Results 1 to 10 of 10

Printing: VBA code to select printer

This is a discussion on Printing: VBA code to select printer within the Excel Questions forums, part of the Question Forums category; Dear Excel Guru's, I've got a delimma here. I've got a customized print macro that uses a userform to display ...

  1. #1
    Board Regular
    Join Date
    Mar 2003
    Location
    SoCal
    Posts
    148

    Default Printing: VBA code to select printer

    Dear Excel Guru's,

    I've got a delimma here. I've got a customized print macro that uses a userform to display all pages in a workbook that 1: have information on them and 2: are not hidden. The user simply checks off the sheets they want to print via checkboxes in the list, and then clicks "print".

    What I'd like to do is also give the user the option to check off which printer they'd like the job to be sent to, as we've got about 5 different printers here each with their own area of specialty.

    This may sound redundant with the already built-in print menu for excel, but the utility of this menu here has become pretty important.

    I'm just looking for a snippet of code to get me off the ground on this one: something that detects the installed printing devices on the PC, if possible????

  2. #2
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Try this:
    Code:
        Application.Dialogs(xlDialogPrinterSetup).Show

  3. #3
    Board Regular
    Join Date
    Mar 2003
    Location
    SoCal
    Posts
    148

    Default Re: Printing: VBA code to select printer

    Wahoo! That's exactly what I'm looking for.

    Thanks dude! That was my missing link.

  4. #4
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Your Welcome

    Take Care

  5. #5
    Board Regular Irish_Griffin's Avatar
    Join Date
    Jan 2009
    Location
    Detroit Michigan
    Posts
    126

    Default Re: Printing: VBA code to select printer

    Just for knowledge sharing...

    Use this to determine if a print exists:

    Code:
    Private Declare Function CreateIC Lib "gdi32" Alias "CreateICA" (ByVal lpDriverName As String, ByVal lpDeviceName As String, ByVal lpOutput As String, ByVal lpInitData As Long) As Long
    
    
    
    Public Function GetPDC(ByVal strPrinter As String) As Long
        GetPDC = CreateIC("WINSPOOL", strPrinter, vbNullString, 0&)
    End Function
    
    
    Sub Look_For_Printer()
    
       printer_name = "Name found in the print setup drop down"
       If GetPDC( printer_name ) = 0 Then
          msgbox("This printer is not on this computer")
       end if
    
    End Sub
    Mechanical Engineer in the Auto Industry Coding = Fish Out of Water

    Great VBA Help Links
    -> VBA Syntax List
    ->
    Online Tutorials
    __________________ "What-ever thy hand findith to do, do it with thy might..." Ecc. 9:10 ~ In H and T

  6. #6
    Board Regular Irish_Griffin's Avatar
    Join Date
    Jan 2009
    Location
    Detroit Michigan
    Posts
    126

    Default Re: Printing: VBA code to select printer

    Here's how to print to a given printer without changing the computer settings....

    Code:
    dim myprinter as string
    dim printer_name as sttring
    printer_name ="name goes here"
    
        myprinter = Application.ActivePrinter
        Change_Form.PrintOut Preview:=False, ActivePrinter:=printer_name, PrintToFile:=True, PrToFileName:=PSFileName
        Application.ActivePrinter = myprinter
    Mechanical Engineer in the Auto Industry Coding = Fish Out of Water

    Great VBA Help Links
    -> VBA Syntax List
    ->
    Online Tutorials
    __________________ "What-ever thy hand findith to do, do it with thy might..." Ecc. 9:10 ~ In H and T

  7. #7
    New Member
    Join Date
    Jan 2011
    Posts
    2

    Default Re: Printing: VBA code to select printer

    Quote Originally Posted by DRJ View Post
    Try this:
    Code:
        Application.Dialogs(xlDialogPrinterSetup).Show
    Just what I was looking for - thanks!

  8. #8
    New Member
    Join Date
    Feb 2012
    Posts
    42

    Default Re: Printing: VBA code to select printer

    Hi Excel Guru's,
    Can some one help me with a complete code as how to select a printer(between 3 printers) using Macros. Hope I'm clear with my Question.

    Many thanks!

  9. #9
    New Member
    Join Date
    Feb 2012
    Posts
    42

    Default Re: Printing: VBA code to select printer

    Hi Irish,
    I tried using your code, as you have mentioned, I have changed printer_name with my printer name.,I'm getting an error as Invalid outside Procedure.


    Can some one help me with a complete code as how to select a printer(between 3 printers) using Macros. Hope I'm clear with my Question.

    Many thanks!

  10. #10
    New Member vicsar's Avatar
    Join Date
    May 2013
    Location
    Heredia, Costa Rica
    Posts
    8

    Lightbulb Re: Printing: VBA code to select printer

    Here is my suggestion, tested in Excel 2013, should be backwards compatible.

    Code:
    Sub iActiveSheet_Print()
    ' First choose a printer
        Application.Dialogs(xlDialogPrinterSetup).Show
    
    
    ' Make final arrangements and setup
        ActiveSheet.PrintPreview
    
    
    ' Print like there is no tomorrow
        ActiveSheet.PrintOut
    End Sub
    Of course the above may be adapted to your needs, be creative.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com