How do I create a macro in Excel that will always print to the default printer?

I have created many macros in Excel that print to a specific printer.  We are undergoing some hardware/printer/network changes. I'd like to change the macros so that they all will print to the default printer - whatever that is set to (since it will change). Help?
PrioritypubAsked:
Who is Participating?
 
mvidasCommented:
Hi Prioritypub,

Your macros are probably using the .PrintOut method, with the printer listed under the "ActivePrinter" argument, something like:
 ActiveSheet.PrintOut Copies:=2, ActivePrinter:="\\printserver\printername"

If you remove whatever you have listed for "ActivePrinter", it will print to the default printer of excel.  So instead of the above line, you'd use:
 ActiveSheet.PrintOut Copies:=2

Matt
0
 
PrioritypubAuthor Commented:
Thank you for such a quick and accurate response.  It's so simple! That worked fabulously.
0
 
mvidasCommented:
Glad to help! Let me know if you need anything else.
0
 
PrioritypubAuthor Commented:
I understand that this command sets the active printer.  Is there a way to select the default printer?  There are other worksheets in the workbook and I'm realizing that users are changing the "active printer" for the other worksheets.  Then when they go to run the macro, it uses the active printer they've last chosen.  Is there any way for it to always use whatever the default printer is?
0
 
mvidasCommented:
I see... It is possible though a little trickier, using windows APIs.


Add the following to the top of the module (above all subs/functions):

Declare Function GetProfileString Lib "kernel32" Alias "GetProfileStringA" (ByVal lpAppName _
 As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString _
 As String, ByVal nSize As Long) As Long


Then add the following function somewhere in the module:

Function GetDefaultPrinter() As String
 Dim stDef As String, dl As Long
 stDef = String$(128, 0)
 dl = GetProfileString("WINDOWS", "DEVICE", "", stDef, 127)
 GetDefaultPrinter = Left$(stDef, InStr(1, stDef, ",") - 1)
End Function


Now, the GetDefaultPrinter function will return the default printer name.  You can use it like:

 Dim DefPrn As String
 DefPrn = GetDefaultPrinter
 ActiveSheet.PrintOut Copies:=2, ActivePrinter:=DefPrn


However, if you don't want to upset the user by changing their excel active printer, you'd have to use something like:

 Dim DefPrn As String, ActPrn As String
 ActPrn = Application.ActivePrinter
 DefPrn = GetDefaultPrinter
 ActiveSheet.PrintOut Copies:=2, ActivePrinter:=DefPrn
 Application.ActivePrinter = ActPrn


Which stores the active printer name, gets the default printer, prints to the default, then changes the active back to where they had it.

Matt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.