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

Posted on 2006-05-10
Last Modified: 2012-05-05
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?
Question by:Prioritypub
    LVL 35

    Accepted Solution

    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


    Author Comment

    Thank you for such a quick and accurate response.  It's so simple! That worked fabulously.
    LVL 35

    Expert Comment

    Glad to help! Let me know if you need anything else.

    Author Comment

    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?
    LVL 35

    Expert Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now