Printing with Excel from ASP

By ServerWatch Staff (Send Email)
Posted Jun 14, 1999


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_USER\Software\Microsoft\Windows NT\Current Version\Devices
  • From the Registry Menu, click Export Registry File.
  • Save as Devices.reg
  • Select this key: HKEY_CURRENT_USER\Software\Microsoft\Windows NT\Current Version\PrinterPorts
  • From the Registry Menu, click Export Registry File.
  • Save as PrinterPorts.reg
  • Select this key: HKEY_CURRENT_USER\Software\Microsoft\Windows NT\Current Version\Windows
  • 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.

<%@ Language=VBScript %>
<%
Response.ExpiresAbsolute=#Jan 1,1980 00:00:01#
Response.Buffer=TRUE

' Copyright 1999 by Gary Chalupa
' gchalupa@inetnebr.com

Response.write("<HTML>" & vbCrLf)
Response.write("<HEAD>" & vbCrLf)
Response.write("<title>Print Demo</title>" & vbCrLf)
Response.write("</HEAD>" & vbCrLf)
Response.write("<BODY>" & 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 = "\\SERVER\PRINTER"
' The Filename you gave to the port.
PrintFile = "C:\PCL.out"
Response.Flush
Response.Write("Starting Now: " & formatdatetime(now(),3) & "<BR>" & 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("<BR>Waiting for Printer <B>. ")
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 & "</B><BR>" & vbCrLf & "Printing<BR>" & 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 & "<BR><BR>" & vbCrLf)
Response.flush
Response.Write("</BODY>" & vbCrLf)
Response.Write("</HTML>" & vbCrLf)
%>

Page 1 of 1


Comment and Contribute

Your name/nickname

Your email

(Maximum characters: 1200). You have characters left.


 

 


Thanks for your registration, follow us on our social networks to keep up-to-date