by Gary Chalupa
gchalupa@inetnebr.com
This article will give you the basics of how to print to a network
printer from Excel. I needed this functionality to print invoices
and quotes from a custom sales database, and found no other way to
accomplish this. Excel provided a great way to format these and it
allowed me the functionality of a Spreadsheet. This will also work
quite nicely when used for reporting purposes, as Excel provides a
great way to generate charts. Microsoft Excel must be installed on
the server for any of this to work. Feel free to contact me with
questions, comments or just to chat.
This article has been
updated to be more adaptable, It now uses an application object
rather than a worksheet object. This will allow multiple copies to
run more smoothly.
If you need to print on a regular basis
this method is inefficient. There is a better way documented here.
The
code is heavily commented so it should be easy to follow. Once you
understand the flow, it is easy to adapt this to other applications
as well. I have an article
on how to use this same concept with a Microsoft Word document as
well. There are some preliminary steps you need to take before it
will work. I detail these steps below with the code following
them.
1. Using Print Manager on the server create a new local
port with the name “C:PCL.out”
Step-by-step:
- Open My Computer/Printers/File Menu/Server Properties
- Go to the Ports tab
- Click “Add Port…”
- Select “Local Port”
- Click “New Port…”
- Enter “C:PCL.out”
- Click “OK”
- Click “Close”
- Click “OK”
2. Install a printer with a driver that
will support ALL of the printers you would like to print to.
I recommend the HP Laserjet III. Install this printer as a local
printer on the newly created port, and set it to be the default
printer.
3. Follow method 2 from Q184291.
This will set our printer as the default printer for the system
account. A summary of this process is listed
here.
Step-by-step:
WARNING: Using Registry Editor
incorrectly can cause, system-wide problems that may require you to
install Windows to correct them. Neither I, nor Microsoft, guarantee
that any problems resulting from the use of Registry Editor can be
solved. Use this tool at your own risk.
- Launch Regedit.exe
- Select this key: HKEY_CURRENT_USERSoftwareMicrosoftWindows
NTCurrent VersionDevices - From the Registry Menu, click Export Registry File.
- Save as Devices.reg
- Select this key: HKEY_CURRENT_USERSoftwareMicrosoftWindows
NTCurrent VersionPrinterPorts - From the Registry Menu, click Export Registry File.
- Save as PrinterPorts.reg
- Select this key: HKEY_CURRENT_USERSoftwareMicrosoftWindows
NTCurrent VersionWindows - From the Registry Menu, click Export Registry File.
- Save as Windows.reg
- From the Start Button, select Run. Type “Notepad Devices.reg”
and hit enter. - Replace the text “HKEY_CURRENT_USER” with
“HKEY_USERS.DEFAULT” - Save the file and exit.
- From the Start Button, select Run. Type “Notepad
PrinterPorts.reg” and hit enter. - Replace the text “HKEY_CURRENT_USER” with
“HKEY_USERS.DEFAULT” - Save the file and exit.
- From the Start Button, select Run. Type “Notepad Windows.reg”
and hit enter. - Replace the text “HKEY_CURRENT_USER” with
“HKEY_USERS.DEFAULT” - Save the file and exit.
- From the Start Button, select Run. Type “c:” and hit enter.
- Double click Devices.reg
- Double click PrinterPorts.reg
- Double click Windows.reg
I used ideas from a
couple of different places to put this together and I need to
mention them.
Excel and
ASP
by: Armand Datema
Using
ASP and WSH to Print on Your Intranet
by: Jeff
Sandquist
Formatted Output
using ASP
by: David R. Cline
Q184291
– PRB: COM Objects Fail to Print When Called From
ASP
Microsoft Corporation
Download the code here: Excelprint.zip
This article will give you the basics of how to print to a network
printer from Excel.
' Copyright 1999 by Gary Chalupa ' gchalupa@inetnebr.com Response.write("" & vbCrLf) Response.write("" & vbCrLf) Response.write("Print Demo " & vbCrLf) Response.write("" & vbCrLf) Response.write("" & vbCrLf) ' Constants ' Domain Name, User Name and Password for a person ' authorized to print to the selected printer. DOMAIN = "DOMAIN" USER = "USER" PASSWORD = "PASSWORD" ' set the printer to print to, cookies work well for this if it needs to be dynamic. PrintVar = "SERVERPRINTER" ' The Filename you gave to the port. PrintFile = "C:PCL.out" Response.Flush Response.Write("Starting Now: " & formatdatetime(now(),3) & "
" & vbCrLf) Response.Flush ' Initialize Print Queue for the printer so you don't overwrite someone's document. if isempty(Application("Ticket")) then Application("Ticket") = 1 Application("Serving") = 1 end if ' Define an Excel Aplication set exDoc = CreateObject("Excel.Application") ' don't show excel to user, obviously as it runs on the server. exDoc.Application.Visible = FALSE ' Set the number of new sheets in a new workbook exDoc.Application.SheetsInNewWorkbook = 1 ' Don't display any notices exDoc.Application.DisplayAlerts = FALSE ' Make a new document exDoc.Application.Workbooks.Add ' put some text in the Excel Document exDoc.ActiveSheet.Cells(1,1).Value = "Hello World." ' set print options margin measurements are in points exDoc.ActiveSheet.PageSetup.LeftMargin = 18 exDoc.ActiveSheet.PageSetup.TopMargin = 36 exDoc.ActiveSheet.PageSetup.RightMargin = 18 exDoc.ActiveSheet.PageSetup.BottomMargin = 36 ' Landscape Page Orientation exDoc.ActiveSheet.PageSetup.Orientation = 2 ' Define File System Object for file manipulation set fsoPrint=CreateObject("Scripting.FileSystemObject") ' Notify User of what we are doing Response.Write("
Waiting for Printer . ") Response.Flush ' Define Windows Scripting Host Object for printer port manipulation set wshobj = CreateObject ("WScript.network") ' Raise an error to enter loop on error resume next Err.Raise 1 ' Set starting LPT port to search port = 2 ' Cycle ports until an open port is found do while not err.number=0 ' don't go above LPT9: if port = 10 then port = 2 ' delay after lpt9: to allow more time for clearing of a port for x=1 to 500000 next end if ' let user know something is happening Response.Write(". ") Response.Flush ' connect a remote printer to a local port on error resume next ' needs a user and a password to work properly wshobj.addprinterconnection "LPT" & port & ":", PrintVar,FALSE,DOMAIN & "" & USER,PASSWORD ' If there is an error add on to the port for the next loop if not err.number=0 then port = port+1 end if loop ' get a place in the Queue MyTicket = Application("Ticket") ' Cycle numbers at 30000 or add on to the ticket number if MyTicket = 30000 then Application("Ticket") = 1 else Application("Ticket") = Application("Ticket") + 1 end if ' wait for my ticket to be served do while not Application("Serving") = MyTicket ' delay slightly for x=1 to 500000 next ' let user know something is happening Response.Write(" . ") Response.Flush loop ' let user know something new is happening Response.Write(vbCrLf & "
" & vbCrLf & "Printing
" & vbCrLf) Response.Flush ' get a temporary filename filename = "c:temp" & fsoPrint.GetTempName ' print the document to the default printer on the server thereby printing to the file ' named Printer File as described in the port setup. exDoc.ActiveWorkbook.PrintOut ,,1,False,,False,False ' set temp variable to 0 to enter loop myTempFileSize = 0 ' loop until file size is stable and greater than 0 to make sure printing is complete do while myTempFileSize fsoPrint.getfile(PrintFile).size or myTempFileSize = 0 ' Get the file size On Error Resume Next myTempFileSize = fsoPrint.getfile(PrintFile).size ' Pause to let the file get larger if it needs to for myForVar = 1 to 50000 next loop ' raise an error to enter the loop on error resume next Err.Raise 1 ' loop until The file is accessable do while not Err.number = 0 On Error Resume Next ' copy the Printer File to a temp ' file to free the print process up for another user. fsoPrint.CopyFile PrintFile,filename loop ' raise an error to enter the loop on error resume next Err.Raise 1 ' loop until the file is free do while not Err.number = 0 On Error Resume Next ' delete the file to allow the next user access to it. fsoPrint.DeleteFile PrintFile, TRUE loop ' increment the queue serving number if MyTicket = 30000 then Application("Serving") = 1 else Application("Serving") = Application("Serving") + 1 end if ' copy the temp file to the proper LPT port for printing fsoPrint.CopyFile filename,"LPT" & port & ":" ' raise an error to enter the loop on error resume next Err.Raise 1 ' loop until the temp file is accessable to be deleted do while not Err.number = 0 On Error Resume Next ' delete the temp file fsoPrint.DeleteFile filename, TRUE loop ' remove the LPT port for others to use. wshobj.removeprinterconnection "LPT" & port & ":" ' Quit Excel exdoc.application.quit ' clean up objects set exDoc = nothing set fsoPrint=nothing set wshobj=nothing ' notify user that we are done Response.Write("Printed to " & PrintVar & "
" & vbCrLf) Response.flush Response.Write("" & vbCrLf) Response.Write("" & vbCrLf) %>