Home     Products      Support      Corporate     Sign In 
Support Knowledge Base, Article 603
Product
ExcelWriter
Title
Printing to a specific client-side printer with ExcelWriter
Problem
When using ExcelWriter on the server to generate and stream a spreadsheet to a client machine, you may also want to automatically print the spreadsheet to a specific printer located client-side.

Currently, this functionality is not part of the server-side scripting object model for ExcelWriter. However, there is an easy way to accomplish this using a VBA macro. See the Solution below for details.
Solution
This method to print the spreadsheet to a specific client-side printer requires that you use a pre-existing Excel file, designed directly in Microsoft Excel. This pre-existing Excel file can, then, contain VBA code that sets PageSetup properties in the Workbook_Activate() event as follows:


Private Sub Workbook_Activate()

    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$J$10"
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&""Arial,Bold""&F"
        .RightHeader = "Printed at &T on &D"
        .LeftFooter = ""
        .CenterFooter = "&P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    
End Sub


Then, in the Workbook_Open() event it prints the active sheet as follows:


Private Sub Workbook_Open()

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
End Sub



Attached is an ASP sample that demonstrates this workaround. The sample contains the following files:
  • AutoPrint.asp -- the script that creates and streams the Excel file with ExcelWriter
  • AutoPrint.xls -- pre-existing spreadsheet that contains the VBA code to pre-set the PageSetup properties and auto print the spreadsheet when it downloads and opens client-side
Related Links
OfficeWriter Home Page
Purchase OfficeWriter
OfficeWriter Enterprise Edition
OfficeWriter Edition Differences
Latest OfficeWriter News

Attachments
Attachments/KB603_files1.zip
Created : 5/2/2003 12:00:00 AM (last modified : 5/5/2003 12:38:22 PM)
Rate this article!
Comments