I am trying to have a PowerShell script print an Excel 2010 file automatically while choosing a specific printer and number of copies.

Here is what I currently have and it does open my Excel file and print it but it is only printing one copy and to the Windows default printer.

#$xlLandscape = 2
$xl = New-Object -comobject excel.application
$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\Users\swalker\Desktop\WIPBinLocations.xlsx")
#
$ws = $wb.Worksheets.Item(1)
#$ws.PageSetup.Orientation = $xlPortrait
$ws.Copies = 2
#$ws.ActivePrinter = 'Manufacturing Rcv HP LaserJet 4250 PCL6 on FILE'
$ws.ActivePrinter = "HP Officejet Pro L7500 Series"
$wb.printout()
$wb.Close
# Quit Excel.
$xl.quit()
spps -n excel

It is not using my Copies and ActivePrinter settings.  If you could point me in the right direction to fix my problem it would be much appreciated.

Thanks,
Stacy

If you do

$ws | Get-Member

This will print out all properties and methods of the $ws object, and I neither see Copies nor a ActivePrinter property, which is why it is defaulting to the default printer settings.

The ActivePrinter property is part of the $xl object, but also found that trying to set it to the printer name results in error, at least on my end it did.

So first set the printer you want it to print to as default then run the following

$xl = New-Object -comobject excel.application

$xl.ActivePrinter

This will print out the name of the active printer, copy and paste it then set it by doing

$xl.ActivePrinter = "Printer Name from above output"

This will get you to print to the proper printer, I am still looking into the number of copies.

Copies you can try something along the lines of this:

$ws.printout(1,1,2)

vaadadmin2010's  solution works, so a finalized script would be

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false

$wb = $xl.Workbooks.Open('F:\Test.xlsx')
$ws = $wb.WorkSheets.Item(1)

$xl.ActivePrinter = "Printer to print to"

$ws.PrintOut(1,1,2)
$xl.quit()

I have $xl.Visible set to false, since I am only printing it there is no reason to open it.

Thank you clayman2 it is working perfectly.  That did the trick and yes my printer ended up being HP Officejet Pro L7500 Series on Ne03: so that definitely would have caused a problem. 

Thanks again,

Stacy

Your welcome, glad I could be of assistance. Yeah the printer name did the same to me, so not sure what the on Ne03: is, but it works.

My bad I was premature in thinking it was working correctly.  I think I still had the printer set as my default printer so I thought it was taking my ActivePrinter setting from my PowerShell script, but it is not.  The $xl.ActivePrinter = "Manufacturing Rcv HP LaserJet 4250 PCL6 on Ne12:" does not appear to be changing the printer to which Excel will print.  It is still printing to my windows default printer. Ugh.  Any ideas?

Thanks,

Stacy

• Edited by tnswalker 15 hours 38 minutes ago Added error

My bad I was premature in thinking it was working correctly.  I think I still had the printer set as my default printer so I thought it was taking my ActivePrinter setting from my PowerShell script, but it is not.  The $xl.ActivePrinter = "Manufacturing Rcv HP LaserJet 4250 PCL6 on Ne12:" does not appear to be changing the printer to which Excel will print.  It is still printing to my windows default printer. Ugh.  Any ideas?

Thanks,

Stacy

• Edited by tnswalker Tuesday, August 11, 2015 3:32 PM Added error

You are setting ActivePrinter to Manufacturing Rcv HP LaserJet 4250 PCL6 on Ne12:

In the screen shot you have after the error message, a totally different printer, where is this coming from?

The printer you want to print to, you set as default, then you did $xl.ActivePrinter, to print out the name that it sees?