Home     Products      Support      Corporate     Sign In 
Support Knowledge Base, Article 1325
Product
ExcelWriter
Title
Print an Excel Document When Opened on the Client
Problem
When using ExcelWriter to generate and stream a spreadsheet to a client machine, you may also want to automatically print the spreadsheet to a printer located client-side. There is an easy way to accomplish this using a VBA macro. See the Solution below for details.
Solution

This method to automatically print the spreadsheet when opened on the client requires that you use a pre-existing Excel file, designed directly in Microsoft Excel. This pre-existing Excel file contains VBA code in the Workbook_Open() event that prints the active sheet:


Private Sub Workbook_Open()
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

You can then open the Excel file with the ExcelApplication object or ExcelTemplate object and process it as usual. If you open the file with the ExcelApplication object, you can use the PageSetup object to programmatically set print related properties of the worksheets as shown below.

[C#] [VB.NET]  
ExcelApplication xlApp = new ExcelApplication();
Workbook wb = xlApp.Open(Page.MapPath("AutoPrint.xls"));
Worksheet ws = wb.Worksheets[0];

PageSetup ps = ws.PageSetup; // get the PageSetup object
ps.PrintArea = ws.CreateArea("A1:J10"); // set the area to be printed

// format center header as Arial, Bold and display the file name
ps.CenterHeader = "&\"Arial,Bold\"&F";
ps.RightHeader = "Printed at &T on &D"; // display time and date
ps.CenterFooter = "&P of &N"; // display page numbers (“# of #”)
            
// set the margins
ps.LeftMargin = 0.75;
ps.RightMargin = 0.75;
ps.TopMargin = 1.0;
ps.BottomMargin = 1.0;
ps.HeaderMargin = 0.5;
ps.FooterMargin = 0.5;

ps.PrintComments = false; // don’t print comments
ps.Orientation = PageSetup.PageOrientation.Landscape; // print in landscape
ps.CenterHorizontally = true; // center print area horizontally
ps.CenterVertically = true; // center print area vertically

Attached is an Excel workbook with the above macro defined (AutoPrint.xls).

Related Links
PageSetup object documentation
Printing to a specific client-side printer with ExcelWriter (Classic ASP)
Attachments
Attachments/KB1325_files1.zip
Created : 8/3/2009 4:47:47 PM (last modified : 4/23/2012 4:22:29 PM)
Rate this article!
 
Comments