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:
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.
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)
%>
ServerWatch is a top resource on servers. Explore the latest news, reviews and guides for server administrators now.
Property of TechnologyAdvice. © 2025 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.
