VBA print button in Excel 2016 RRS feed

  • Question

  • We have a Excel template, that needs to be printed to a specific printer.

    And for that I would like to design a print button, that prints to a specific network printer using that printers IP address.

    I tried to record a macro that prints to that printer which in Office 2016, didn't help at all, because next time I opened that same Excel file it would print to my primary printer again and not the specific one.

    So how do I do this?

    I do know how to design a button and open the VBA script window, but I need help for basically anything else. I have searched online but didn't find a working easy to implement solution.

    I would also like the code to look for the printer and if it isn't installed, then install it. Or at least give a message to the user that the printer isn't installed and call IT.

    • Moved by Bill_Stewart Thursday, March 15, 2018 3:03 PM Move to more appropriate forum
    Thursday, March 15, 2018 8:20 AM

All replies

  • I don't know if the following will work with printers in a network with IP addresses. When you select a printer from the worksheet, how is the printer identified? If it is a name then the following might work.

    The first part is to simply identify the printer name.

    On a worksheet select File -> Print and select the specific printer to which you want to print.

    Print something with the printer (just one page. Under the printer selection in Settings you should be able to set pages 1 to 1). This will set the required printer as the ActivePrinter.

    Now open the VBA editor (Alt and F11)

    Open the Immediate Window (Ctrl and G and the Immediate window appears at the bottom of the VBA editor)

    Enter the following command in the Immediate window (Note it starts with a question mark)

    ? Application.ActivePrinter

    The command should return the Printer Id of the selected printer. (If this does not work then neither will the following work).

    Now create code similar to the following to select the required printer and print to it and then return the user to their usual printer.

    Sub SelectPrinterAndPrint()
        Dim strDefaultPrinterId As String
        Dim strRequiredPrinter As String
       'Following line saves the current Active (or default) printer Id.
        strDefaultPrinterId = Application.ActivePrinter
        'The following Printer Id is the one returned in the
        'Immediate Window and enclosed in double quotes
        'Copy from Immediate Window and add double quotes.
        strRequiredPrinter = "Brother HL-L8260CDW series on Ne02:"
        Application.ActivePrinter = strRequiredPrinter
        'Insert code here to print the required document.
       'Following code to return the user to their default printer Id.
        Application.ActivePrinter = strDefaultPrinterId
    End Sub

    Regards, OssieMac

    Sunday, March 25, 2018 4:17 AM