Printing: VBA code to select printer

[scotawful]

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

 

[DRJ]

Try this:

    Application.Dialogs(xlDialogPrinterSetup).Show

 

[scotawful]

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

Thanks dude! That was my missing link.

 

[DRJ]

Your Welcome

Take Care

 

[Irish_Griffin]

Just for knowledge sharing...

Use this to determine if a print exists:

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

 

[Irish_Griffin]

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

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

 

[mdfreeman]

Try this:

    Application.Dialogs(xlDialogPrinterSetup).Show

Just what I was looking for - thanks!

 

[puppy26]

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!

 

[puppy26]

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!

 

[vicsar]

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

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.