[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

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?
0
Prioritypub
Asked:
Prioritypub
  • 3
  • 2
1 Solution
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now