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

 

[siroos12]

Hi Everyone,

I have tried this on a mac and it wont work. Can you please help? What should I use insted to select a printer from a list in Excel VBA?

Application.Dialogs(xlDialogPrinterSetup).Show

 

[Rangeraziz]

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

dim myprinter as string
dim printer_name as sttring
1 printer_name ="name goes here"

2    myprinter = Application.ActivePrinter
3    Change_Form.PrintOut Preview:=False, ActivePrinter:=printer_name, PrintToFile:=True, PrToFileName:=PSFileName
4    Application.ActivePrinter = myprinter

Hi Griffin, i am getting error on the line 3 i try braking down to see where the error is but no luck yet. if you could explain the code. thank you

 

[Rangeraziz]

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

HI it look good but some how i am getting error "sub or function not defined, any help highly appreciated thanks

 

[ErikB85]

Hi,

I've been looking for a solution to my problem, this thread seems to almost get it.. but i'm still missing something, couldn't find a solution anywhere. Hope someone knows the solution.

So what I'm trying to do is print 2 separate sheets to 2 separate printers. The one printer is local, and the other is a network printer. I've got this part done and it works on the computer I mostly do these prints from.
But sometimes I have to use this file from another location, the network printer I could find a work around for since only the port could change if I'm correct. But the local printer needs to become something totally different. So to make it easy, I figured, I will set a printer for default use, but when it gives an error (because the port is wrong or printer can't be found) I let the printer select box come up and simply select the printer i want manually. Got all working up to there.

But the annoying part now is, when I use the file now on the computer I do this from most of the time, the printer select box comes up all the time anyway when it doesn't have to. how do i skip this box when the 'default' printers are found?

Below the part of the Code that matters:

Dim Page1 as worksheet
Dim Page2 as worksheet
set Page1 = worksheets("Page1")
set Page2 = worksheets("Page2")


    page1.Cells(2, 5).Value = "links to cell for amount of copies"

    
    On Error Resume Next
    Application.ActivePrinter = "Name of local printer on Ne01:"
    Application.Dialogs(xlDialogPrinterSetup).Show
       Page1.PrintOut Copies:=page1.Range("H2").Value
    
    On Error Resume Next
    Application.ActivePrinter = "Network printer on Ne05:"
    Application.Dialogs(xlDialogPrinterSetup).Show
        Page2.PrintOut

 

[oso_togari]

great! very simple...
but how to check the status of the printer...?
i have a ticket printer that randomly works under a name or another, both are in the list of printers, but only one of them is in ready status at a given time.
i find more easy to question about the status than configure properly the printer and delete it from the system hahaha (i sadly failed trying to doing this)

regards

 

[NdNoviceHlp]

There's some printer stuff here that might be useful. HTH. Dave
http://www.vbaexpress.com/forum/sho...inter-is-not-connected-quot&highlight=printer

 

[Kyle123]

You can return a port for a given printer like this:

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

So to set the printer from the name:

Public Function GetFullPrintrName(strPrinterName As String) As String
    GetFullPrintrName = strPrinterName & " on " & GetPrinterPort(strPrinterName)
End Function

Sub SetPrinter
   Application.ActivePrinter = GetFullPrintrName("P-3521DN")
End Sub

 

[NessPJ]

Hi all,

Sorry for necroing an old thread (but it concerns my question exactly).
This code no longer seems to work with Windows 10 and Office 2016.
Is there a way to fix this?

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

 

[Kyle123]

Yes it does, so please start a new thread as your problem is elsewhere