Referencing default printer in macros – Excel 2003

Topic

New Reply

I have a number of macros that automatically send data to one of a number of printers. After doing the print job the macro then re-establishes the default system printer as the printer to send data to should the PRINT icon get used. Without resetting to the default printer within the macro data will get sent to the last printer used should the PRINT icon get pressed. I can also get some screwy on screen page print sections showing up if a label printer was last used.

This is written into the macro as follows:

‘ THE FOLLOWING LINE RETURNS THE BORTHER MFC-9460 AS THE ACTIVE PRINTER.

Application.ActivePrinter = “Brother MFC-9460CDN Printer on Ne08:”

The aggravation with this system is that every time a printer gets added or removed on the system I have to go in and edit all the macros to update the printer name and/or Ne0# location.

What I’m wondering is if there is a command I can use that will re-establish the DEFAULT system printer as the ACTIVE PRINTER without specifically naming it. This would hopefully work somewhat like printing to the default system printer:

‘ THE FOLLOWING STATEMENT PRINTS 1 COPY OF THE QUOTE TO THE DEFAULT SYSTEM PRINTER

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Thanks as always for you help and guidance.
BH

Reply | Quote

Replies

BH,

Maybe you can modify this code to meet your needs. The trick would be to save the Active Printer at the beginning of your code and then reset it to that upon exiting. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi

Apart from RG’s suggestion, maybe you could have the User pick their required printer using something this:

Code:

Sub selectPrinter()

Application.Dialogs(xlDialogPrinterSetup).Show (ActivePrinter)
zPrinter = ActivePrinter    'e.g. "hp LaserJet 1320 PCL 5 on Ne04:"

zPosition = Application.Find(" on ", zPrinter)
zPrinterName = Left(zPrinter, zPosition - 1)    '<< removes the " on Ne04:"
[chosenPrinter] = zPrinterName                  '<< printer name, without port
'[chosenPrinter] = ActivePrinter                '<< printer with port address

End Sub

This uses a named cell [chosenPrinter]
see attached Excel2003 file for example use.
(The example file is used to print pdf files)

zeddy

Reply | Quote

I use this function to return the correct port for a given printer name:

Code:

Public Function GetPrinterPort(strPrinterName As String) As String
   Dim objReg As Object, strRegVal As String, strValue As String
   Const HKEY_CURRENT_USER = &H80000001
   Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\.rootdefault:StdRegProv")
   strRegVal = "SoftwareMicrosoftWindows NTCurrentVersionPrinterPorts"
   objReg.getstringvalue HKEY_CURRENT_USER, strRegVal, strPrinterName, strValue
   GetPrinterPort = Split(strValue, ",")(1)
End Function

Reply | Quote

Hi Rory

many thanks for that.

If anyone needs to see this in action, I have added both to the attached file.

zeddy

Reply | Quote

Zeddy,

Loved your code! But of course being a code hound myself I just had to play with it.

Here’s a split version that covers a couple of usage conditions. I converted the base code to a Function that accepts a parameter telling it whether or not to return the name with the port. The function can be called from other VBA code. I then created a Sub that calls the function and takes the return value and stuffs it into your named cell (functions can’t be called directly from command buttons). So now the printer selection is accessible from a button on the worksheet or VBA code as the user desires.

I discovered a couple of interesting things while doing this:

[*]The argument (ActivePrinter) to the dialog.show command is not necessary.
[*]Although you can call the Function as a UDF from a cell it merely returns the currently active printer name it does NOT show the dialog box!

HTH :cheers:

Code:

Option Explicit

'                         +-------------------------+             +----------+
'-------------------------|      PickPrinter()      |-------------| 02/16/15 |
'                         +-------------------------+             +----------+
'Calls:  zSelectPrinter
'        Argument:  False = Printer Name w/o Port
'                   True  = Printer Name with Port
'RangeNames: chosenPrinter - single named cell where selected printer value
'                            is returned.

Sub PickPrinter()

   [chosenPrinter] = zSelectPrinter(False)
   
End Sub      'PickPrinter


'                         +-------------------------+             +----------+
'-------------------------|    zSelectPrinter()     |-------------| 02/16/15 |
'                         +-------------------------+             +----------+
' Uses the CommonDialog Box to select the desired printer.
' Note: If called as a UDF from a cell, e.g. =zSelectPrinter the dialog box
'       will NOT be displayed but the currently active printer will be
'       returned in the cell!

'Argument:  False = Printer Name w/o Port
'           True  = Printer Name with Port

'*************************************************************
'*** Based on code by Zeddy @ WSL {www.windowssecrets.com} ***
'*************************************************************


Function zSelectPrinter(bWithPort As Boolean) As String

   Dim zPrinter     As String
   Dim zPrinterName As String
   Dim iPosition    As Integer

   Application.Dialogs(xlDialogPrinterSetup).Show
   zPrinter = ActivePrinter    'e.g. "hp LaserJet 1320 PCL 5 on Ne04:"
   iPosition = Application.Find(" on ", zPrinter)
   zPrinterName = Left(zPrinter, iPosition - 1)    '<< removes the " on Ne04:"
   
   '*** Return Name to calling Code ***
   zSelectPrinter = IIf(bWithPort, ActivePrinter, zPrinterName)
   
End Function 'zSelectPrinter

Test File: 39445-VBA-Excel-Select-Printer-Using-Common-Dialog-Box

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

FYI, buttons can call functions – you just have to type the name in. So you could assign:
‘zSelectPrinter False’
as the “macro”. Note you need the single quotes as you are passing an argument (though you could just make False the default).

Reply | Quote

Thanks everyone. I’ll work on these suggestions and see if I can make some headway.

BH

Reply | Quote