Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Referencing default printer in macros - Excel 2003

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    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
    Attached Files Attached Files

  4. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2015-02-13)

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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}!\\.\root\default:StdRegProv")
       strRegVal = "Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts\"
       objReg.getstringvalue HKEY_CURRENT_USER, strRegVal, strPrinterName, strValue
       GetPrinterPort = Split(strValue, ",")(1)
    End Function
    Regards,
    Rory

    Microsoft MVP - Excel

  6. The Following User Says Thank You to rory For This Useful Post:

    zeddy (2015-02-16)

  7. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Rory

    many thanks for that.

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

    zeddy
    Attached Files Attached Files

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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:

    1. The argument (ActivePrinter) to the dialog.show command is not necessary.
    2. 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
    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: VBA - Excel - Select Printer Using Common Dialog Box.xlsm
    Last edited by RetiredGeek; 2015-02-16 at 07:09.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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).
    Regards,
    Rory

    Microsoft MVP - Excel

  10. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2015-02-16)

  11. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks everyone. I'll work on these suggestions and see if I can make some headway.

    BH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •