Windows Powershell - How to get available printers and print an Exceldocument

In this chapter I will describe how to print an Excel Woorksheet with Windows Powershell. The first thing we need to know when working with powershell automation of Microsoft Office Products is that we need to be logged on in order to interact with an Excel-, Word-, Powerpoint- or Publisher document. It works perfectly fine to run automated powershell scripts for Microsoft Office such as printing, creating documents or any other task in the background without opening up the GUI. But you need to be logged on. So if you for instance create an automated Excel printingjob as we are about to do in this article, and then run it through the scheduler the user that should be the owner of the printed object must be logged on or your scheduled script will crash. 

In order to print an excel spreadsheet you will need a printer or print it as a Microsoft VPS Document or as a Microsoft PDF Writer document. The first thing we need to do is to retrieve the name of the printer. If you have been working with Powershell in Windows 7 you will know that the Get-Printer command WON'T work isnce it is not a part of the default modules. The Get-Printer command is only available in Windows 10 or Windows Server 2008 R2 or later. However this command won´t get you the correct names of the printers as they are used in Word, Excel, Powerpoint and so on. The Microsoft Office suite has its own printer implementation in it's respective Powershell module. The easiest way to get the name of the printer you want to use is to open up Excel (or any other Office product) from the Powershell command line. Start by creating the Excel object and then set the visibility property to true. 

$xldoc = new-object -comobject Excel.application

$xldoc.Visible = 1

Now you will get a fresh instance of Microsoft Excel. Create a new document and leave it empty, then select Print from the File menu. Now you can select the printer that you want to use in your Powershell script. When you have selected the printer you want to use click on Print, you will now get a notice from Excel saying: "There was nothing to print", but you hve selected the printer you wanr ro use. In order to retrieve the name of the selected printer you simply check the ActivePrinter property of the Excel object you created.

$xldoc.ActivePrinter

Microsoft XPS Document Writer on Ne00:

Now you can copy the name of the printer for further use in your Excel print Powershell script. In order to close Excel you just call the Quit() method of the Excel object.

$xldoc.Quit()

Sometimes the Quit() method of an Microsoft Office object doesn't kill the process properly. If you run your Powershell script multiple times you will end up with a whole bunch of broken processes that eventually will cause your memory to overload and your computer to crash. If you want to be certain that there is no Microsoft Office processe left behind after you have executed your Powershell script you can saerch for the process and then kill it each time you hav run your Powershell script. ("EXC*" #Can be replaced with WINWOR*, POW* and so on for other Microsoft Office Applications)

$xlprocess = Get-Process "EXC*"

$xlprocess.Kill()

Now that we have the printer name we can create the Powershell script for printing an Excel spreadsheet.

  • First we start by creating the ExcelObject by calling the same method as we did earlier. The only difference is that we don´t set the Visibility propert so the Excel object remains in the background  without shoeing the GUI. 
    $xldoc = new-object -comobject Excel.application

     

  • Now we want to open the Excel Workbook that we want to print. In order to do that we call the Workbooks.Open() method of the Excel Object.
    $excel_document_name = "C:\PSscripts\resources\2019_11_Example_Sheet.xlsx"
    
    $workbook = $xldoc.Workbooks.Open($excel_document_name)
  • When the Excel object is created and the Excel Workbook is loaded we are ready to set the printer we want to use by setting the ActivePrinter property of the Excel Object.
    $xldoc.ActivePrinter = "Microsoft XPS Document Writer on Ne00:"

     

  • Now we are ready to print the actual sheet. It is not possible to call a Print() method on the Excel Object or the Excel Work Book itself. In order to print a shet we need to set what sheet to print from the Excel woorkbook and then call the Print()method of the ExcelSheetObject. The Worksheet Object is a child object of the WorksheetObject and can be retrieved by calling the Worksheet Item method with a number. The n:th worksheet is retrieved.  It is also possible to get the sheet by name by using the Worksheet Item()method and call it with a string.
    $worksheet = $workbook.worksheets.item(1) $worksheet = $workbook.worksheets.item("Sheet1")

     

  • Finally we can print the actual worksheet by calling the WorkSheetObject method PrintOut(). But before we print the Excel sheet we can manipulate the printer properties just as if we would use the GUI to print. A common issue when printing Excelsheets is that it doesn´t always fit the paper size. By setting the PageSetup.Zoom() property to a number between 1 and 100 we can adjust the scaling of the current sheet from 1% to 100% of its actual size.
    $worksheet.PageSetup.Zoom() = 97
    
    $worksheet.PrintOut()

     

  • There are also several other printing properties you can reach through the WorkSheet.PageSetup class

    $workbook.Worksheets.Item("Sheet1").PageSetup
    
    Application: 					Microsoft.Office.Interop.Excel.ApplicationClass
    Creator: 						1480803660
    Parent: 						System.__ComObject
    BlackAndWhite:					False
    BottomMargin:					54
    CenterFooter:
    CenterHeader
    CenterHorizontally:				False
    CenterVertically :				False
    ChartSize: 						-2146826246
    Draft : False
    FirstPageNumber: 				-4105
    FitToPagesTall:					1
    FitToPagesWide: 				1
    FooterMargin: 					21,6
    HeaderMargin : 					21,6
    LeftFooter:
    LeftHeader:
    LeftMargin:						50,4
    Order: 							1
    Orientation: 					1
    PaperSize:		 				9
    PrintArea:	
    PrintGridlines: 				False
    PrintHeadings:					False
    PrintNotes: 					False
    PrintTitleColumns:
    PrintTitleRows:
    RightFooter:
    RightHeader:
    RightMargin:					50,4
    TopMargin:						54
    Zoom:							100
    PrintComments:					-4142
    PrintErrors:					0
    CenterHeaderPicture: 			System.__ComObject
    CenterFooterPicture:	 		System.__ComObject
    LeftHeaderPicture: 				System.__ComObject
    LeftFooterPicture: 				System.__ComObject
    RightHeaderPicture: 			System.__ComObject
    RightFooterPicture: 			System.__ComObject
    OddAndEvenPagesHeaderFooter:	False
    DifferentFirstPageHeaderFooter:	False
    ScaleWithDocHeaderFooter:		True
    AlignMarginsHeaderFooter:		True
    Pages:							System.__ComObject
    EvenPage:						System.__ComObject
    FirstPage:						System.__ComObject

Now we have everything we need to put all together in a nice little Powershell script which we can call manually or from the TaskScheduler. To be on the safe side I have thrown in some error handling, which always is nice when running unattended jobs and scripts. Also I have added a StartSleep before printing the document. Since we are calling the Microsoft.Office.Interop.Excel.ApplicationClass which is an assembly stored in the assembly cache, it takes some time for everything to load properly. If we start printing before the WorkBookObject is loaded properly we will get an error. Another way to do it is to do a for-loop and wait for the WoorkBookObject to be defined, but then we face the posibility to get stuck in the for-loop for all eternity. Basically it is safer to delay the execution slightly because then we are certain that execution of the Powershellscript will continue.

#Powershell script for printing excelsheets - Licensed through GPL v3

#Declare variables    
    $excel_document_name = "C:\PSscripts\resources\2019_11_Example_Sheet.xlsx"
    $sh_no = 1

#Create a new excel object and open the workbook
    $xldoc = new-object -comobject Excel.application
    try {
            $workbook = $xldoc.Workbooks.Open($excel_document_name)

            #Select Sheet in workbook
            $worksheet = $workbook.worksheets.item($sh_no)
            
            #Sleep for a number of seconds so everything gets a chance to get loaded
            Start-Sleep -s 15

            #Set printer properties (downscale the document to fit A4 Page % Zoom 
            $worksheet.PageSetup.Zoom() = 97
           
            #Print worksheet
            echo "Printing excel sheet $sheet_number on printer $($xldoc.ActivePrinter)"
            $worksheet.PrintOut()
        }
    catch {
            #Call the errorhandling function and do some error handling
            echo "Failed to open the workbook: $($_.Exception.Message) $($_.Exception.ItemName)"
    } 
   
    finally {
          #Wait for printout to complete
          Start-Sleep -s 10

          #Close Workbook and quit exceldoc
          $workbook.Close()
          $xldoc.Quit()
     
          #Check for remaining excel process and kill it if found.
          $xlprocess = Get-Process "EXC*"

          if ($xlprocess){
                $xlprocess.Kill()
          }
        write-host "Printout Script Finished @ $($todays_date)"
    }

 

English