chrissmith18
asked on
Microsoft Excel 2007 - Change Default Printer
I have built a macro using Excel 2007 that will print all files located in a specified folder. The macro prints these files in alphabetical order. I need all of these documents to be sent to the color printer which is not the default printer. I came across the following code to change the printer in excel:
Application.Dialogs(xlDial ogPrinterS etup).Show
However this does not achieve what I am looking for because I am not printing exclusively excel workbooks. I am also printing PDF files and Word Documents. I need the macro to change the system default printer so the non-excel files go to the same printer as well. I found the following article which achieves my goal in Visual Basic, but I need to do this with VBA.
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q266/7/67.ASP&NoWebContent=1
Application.Dialogs(xlDial
However this does not achieve what I am looking for because I am not printing exclusively excel workbooks. I am also printing PDF files and Word Documents. I need the macro to change the system default printer so the non-excel files go to the same printer as well. I found the following article which achieves my goal in Visual Basic, but I need to do this with VBA.
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q266/7/67.ASP&NoWebContent=1
This is the code you put in VBA module at the top to set the default printer:
Private Declare Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" _
(ByVal pszPrinter As String) As Long
call it like:
SetDefaultPrinter("full name of the printer")
Private Declare Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" _
(ByVal pszPrinter As String) As Long
call it like:
SetDefaultPrinter("full name of the printer")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This works perfectly, Thank you!!
Most of the code enumerates printers and such, and I have code to set the default system printer...
Let me pull this together in usable form.
Here are my sources for your interest:
http://www.xtremevbtalk.com/showthread.php?t=258994
http://support.microsoft.com/kb/q166008/
I've tested all but setting the default printer.
What I can do is provide some code that lists the printers, allows you to identfy one, and set a defined name to that printer.
Then, another routine that if called, sets the printer based on that range name.
Does this make sense? I'm trying to anticipate the work process and sounds like your macro would prompt for a folder to print from, could then prompt for the printer to use, then does its magic - correct?
Dave