Link to home
Start Free TrialLog in
Avatar of chrissmith18
chrissmith18Flag for United States of America

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(xlDialogPrinterSetup).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


Avatar of dlmille
dlmille
Flag of United States of America image

I went at it a bit differently and found some API sources I'm now testing.

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
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")
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of chrissmith18

ASKER

This works perfectly,  Thank you!!