EXCEL VBA - Identify Printer location XXX On Ne01 - Ne16

Hi Guys,

In the below code, I am checking to see if a specific Printer is installed, if not, install it, then use it to create a ps file which is then converted to pdf (This eliminates the issue with PDF fonts not installed errors)

But for this to work I need to know where the printer is located after it's installed (Or a reboot) it may be Ne01 or it may be Ne04, it may be different on every PC and every reboot. How can I find this out each time I run the code to drop it into a variable I can use for setting the printer as active?

Dim sPSFileName As String 'Name of PS to be created
    Dim sPDFFileName As String 'Name of PDF to be created
    Dim sJobOptions As String
    Dim sCurrentPrinter As String 'Same current printer choice to resume at end
    Dim sPDFVersionAndPort As String 'Version of Adobe
    Dim appDist As cACroDist
    Dim MyFileName As String

    

    Dim aPrinter As Variant
    Dim stPrinter As String
     
     'The name of the printer You want to check up.
    stPrinter = "HP LaserJet 2300L PS"
     
    For Each aPrinter In fncEnumInstalledPrintersReg
        If stPrinter = aPrinter Then GoTo PrintLoc
    Next aPrinter

Shell "rundll32 printui.dll,PrintUIEntry /in /b ""HP LaserJet 2300L PS"" /n ""\\CONORDT\HP LaserJet 2300L PS"""

    
PrintLoc:
 
    MyFileName = Trans
     
    Set appDist = New cACroDist
    sCurrentPrinter = Application.ActivePrinter 'Save the currently active printer
    sPDFVersionAndPort = "\\CONORDT\HP LaserJet 2300L PS on Ne04:" 'sCurrentPrinter
    sPSFileName = Dir & "\" & MyFileName & ".ps"  'Name of PS file
    sPDFFileName = Dir & "\" & MyFileName & ".pdf" 'Name of PDF
    ActiveSheet.PrintOut ActivePrinter:=sPDFVersionAndPort, _
    PrintToFile:=True, PrToFileName:=sPSFileName 'Prints to PS
    
     
    Call appDist.odist.FileToPDF(sPSFileName, sPDFFileName, sJobOptions)
     'Creates PDF
     
    Kill sPSFileName 'Removes PS
    Kill Dir & "\" & MyFileName & ".log" 'Removes Log
    
     
    Application.ActivePrinter = sCurrentPrinter 'Change back to the original printer

Open in new window

LVL 2
Conor_NewmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
I'm not quite sure what's hanging you up.  Perhaps you need to see if the stPrinter name is in the printer enumeration.  e.g.,  


    stPrinter = "HP LaserJet 2300L PS"
     
    For Each aPrinter In fncEnumInstalledPrintersReg
       If InStr(aPrinter,stPrinter)<> 0 Then GoTo PrintLoc
    Next aPrinter

If you hadn't provided code and I was pulling together help from scratch, I would provide the following (and am, anyway ;)--------------------------------------------

This tip has a subroutine that enumerates printers, and in that enumeration you could check the printer name (or part of the name) to see if its one that you want to use.  There's a get default printer routine (to hold the current default) and there's a set default printer routine.  So, you can hold the current default, find a printer (and if not found, then make a connection), set it to default, do your printing, then revert to the original default after your operation.  (or, in your case since you're printing from Excel rather than Adobe, you just set the active printer).  From this you should be able to develop what you need.

>>need to know where a printer is after its installed
The enumerate printers routine should help you to locate it (if installed, or after installed) based on its name part (e.g., whatever identifies it short of NEO1 or NEO4)...

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27529301.html

-------------------------------------------------
It appears you have similar logic and function to what I've described, and perhaps the only help you need is the advice to look at a part of the name to know you've got it (e.g., if InStr(aPrinter,stPrinter) <> 0).  In fact, you may be using the same API to enumerate your printers as that's not shown in your snippet.  Otherwise, the code in the tip provides what you can use for this purpose.

Let me know if you need further assistance.

Take a look at the routine:  Sub EnumeratePrinters4() as there are several parameters that are fetched from connected printers, the key one of which is the printer name (and should exclude "on NE01" and just be the printer name - though I can't test this right now as I'm not in an office environment).


Dave
Conor_NewmanAuthor Commented:
Here's the function I'm using to get the Printer name (From my previous question on here)
Option Explicit
 
Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type
 
Declare Function RegOpenKeyEx _
Lib "advapi32.dll" _
Alias "RegOpenKeyExA" _
( _
ByVal hKey As Long, _
ByVal lpSubKey As String, _
ByVal ulOptions As Long, _
ByVal samDesired As Long, _
phkResult As Long _
) _
As Long
 
Declare Function RegEnumKeyEx _
Lib "advapi32.dll" _
Alias "RegEnumKeyExA" _
( _
ByVal hKey As Long, _
ByVal dwIndex As Long, _
ByVal lpName As String, _
lpcbName As Long, ByVal _
lpReserved As Long, _
ByVal lpClass As String, _
lpcbClass As Long, _
lpftLastWriteTime As FILETIME _
) _
As Long
 
Declare Function RegCloseKey _
Lib "advapi32.dll" _
( _
ByVal hKey As Long _
) _
As Long
 
 
Public Function fncEnumInstalledPrintersReg() As Collection
     'returns a collection of the currently installed printers as
     'it appears in the Windows Registry
     '
     'variable declarations
    Dim tmpFunctionResult As Boolean
    Dim aFileTimeStruc As FILETIME
    Dim AddressofOpenKey As Long, aPrinterName As String
    Dim aPrinterIndex As Integer, aPrinterNameLen As Long
     '
     'required API constants
    Const KEY_ENUMERATE_SUB_KEYS = &H8
    Const HKEY_LOCAL_MACHINE = &H80000002
     '
     'initialise the result of the function to a New (empty) Collection object
    Set fncEnumInstalledPrintersReg = New Collection
     'initialise other variables
    aPrinterIndex = 0
     '
     'open the Windows Registry key that contains the subkeys with the list
     'of currently installed printers
     'This key should be located at: "SYSTEM\CURRENTCONTROLSET\CONTROL\PRINT\PRINTERS"
    tmpFunctionResult = Not CBool _
    ( _
    RegOpenKeyEx _
    ( _
    hKey:=HKEY_LOCAL_MACHINE, _
    lpSubKey:="SYSTEM\CURRENTCONTROLSET\CONTROL\PRINT\PRINTERS", _
    ulOptions:=0, _
    samDesired:=KEY_ENUMERATE_SUB_KEYS, _
    phkResult:=AddressofOpenKey _
    ) _
    )
     '
     'if we could not open/find the "SYSTEM\CURRENTCONTROLSET\CONTROL\PRINT\PRINTERS"
     'Registry key, exit the function; return an empty collection
    If tmpFunctionResult = False Then GoTo ExitFunction
     
     'Loop through all the sub-keys in the Registry key we just opened, and read the
     'entries for the installed printers
    Do
         '
         'pre-initialise a string to hold the first printer name
        aPrinterNameLen = 255 'the length of the string should be
         'large enough to hold any printer name
        aPrinterName = String(aPrinterNameLen, CStr(0))
         '
         'read the value of the next registry sub-key
        tmpFunctionResult = Not CBool _
        ( _
        RegEnumKeyEx _
        ( _
        hKey:=AddressofOpenKey, _
        dwIndex:=aPrinterIndex, _
        lpName:=aPrinterName, _
        lpcbName:=aPrinterNameLen, _
        lpReserved:=0, _
        lpClass:=vbNullString, _
        lpcbClass:=0, _
        lpftLastWriteTime:=aFileTimeStruc _
        ) _
        )
         'prepare to read the next sub-key
        aPrinterIndex = aPrinterIndex + 1
         '
         'for as long as there is a sub-key to read the RegEnumKeyEx function
         'will return tmpFunctionResult=True. If the result is False, there are
         'no more printers and therefore we can exit the loop
        If tmpFunctionResult = False Then Exit Do
         '
         'trim all trailing Null-Strings from the name of the printer
         '(the aPrinterNameLen variable has been updated to indicate the
         ' actual length of the printer name)
        aPrinterName = Left(aPrinterName, aPrinterNameLen)
         
         'add the name of the printer that we have just read in the
         'fncEnumInstalledPrintersReg collection
        On Error Resume Next
        fncEnumInstalledPrintersReg.Add aPrinterName
        On Error GoTo 0
    Loop
     '
     'close the Windows Registry key that we have opened for reading the collection
     'installed printers
    Call RegCloseKey(AddressofOpenKey)
     '
    Exit Function
ExitFunction:
    If Not AddressofOpenKey = 0 Then Call RegCloseKey(AddressofOpenKey)
    Set fncEnumInstalledPrintersReg = Nothing
End Function

Open in new window


It only returns the bare name without the location, so "HP LaserJet 2300L PS" instead of "HP LaserJet 2300L PS on NeXX:" which is what I need to define that printer to be used to create my .ps file later on. SO I have no problem finding just the name, but I need the "on NeXX" part, thats the bit I'm stuck on ;-)
dlmilleCommented:
Ok - take a look at the EnumPrinter() function and see the different parameters that are there - the "fullname" you're looking for may be available.

The Server is one of the parameters (sName).  Here's where I got the original EnumPrinter code:  http://support.microsoft.com/kb/q166008/

Dave
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

Rory ArchibaldCommented:
If it helps, here is a sub I use to list printers and ports
Sub PrintersAndPorts()

'This works with Windows 2000 and up

   Dim Arr               As Variant
   Dim Device            As Variant
   Dim Devices           As Variant
   Dim msg               As String
   Dim RegObj            As Object
   Dim RegValue          As String
   Const HKEY_CURRENT_USER = &H80000001

   Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
   RegObj.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr

   For Each Device In Devices
      RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
      msg = msg & Device & " on " & Split(RegValue, ",")(1) & vbCrLf
   Next

   MsgBox msg, vbInformation, "Printers and Ports"

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Conor_NewmanAuthor Commented:
Ok... so.. in the code I'm using
 
    ( _
    RegOpenKeyEx _
    ( _
    hKey:=HKEY_LOCAL_MACHINE, _
    lpSubKey:="SYSTEM\CURRENTCONTROLSET\CONTROL\PRINT\PRINTERS", _
    ulOptions:=0, _
    samDesired:=KEY_ENUMERATE_SUB_KEYS, _
    phkResult:=AddressofOpenKey _
    ) _
    )

Open in new window


It only pulls the Printer names as that's all that's available from that registry entry.. Also it only pulls Printers on the current machine, not network printers.

But, at HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts
the full information including the order (ports) is stored.. can I change, in my code, the registry key to the current users printerports one and extract from there instead?

I'm not sure myself as I don't understand how the code is extracting the info needed from the registry keys,
Conor_NewmanAuthor Commented:
Rorya, just saw your post now, 1 tic, that looks like what I'm looking for.. just going to plug it in and check..
Conor_NewmanAuthor Commented:
Ok, so I've updated the code, Rorya, that is , I think, giving me what I need, but the code won't let me set that as the active printer;

Dim sPSFileName As String 'Name of PS to be created
    Dim sPDFFileName As String 'Name of PDF to be created
    Dim sJobOptions As String
    Dim sCurrentPrinter As String 'Same current printer choice to resume at end
    Dim sPDFVersionAndPort As String 'Version of Adobe
    Dim appDist As cACroDist
    Dim MyFileName As String

    

    Dim aPrinter As Variant
    Dim stPrinter As String
     
     'The name of the printer You want to check up.
    stPrinter = "HP LaserJet 2300L PS"
     
    For Each aPrinter In fncEnumInstalledPrintersReg
        If InStr(aPrinter, stPrinter) Then GoTo PrintLoc
    Next aPrinter

Shell "rundll32 printui.dll,PrintUIEntry /in /b ""HP LaserJet 2300L PS"" /r ""LPT1"" /n ""\\CONORDT\HP LaserJet 2300L PS"""
    
PrintLoc:
 
' Set Full Printer name and Path

   Dim Arr               As Variant
   Dim Device            As Variant
   Dim Devices           As Variant
   Dim sFullPName        As String
   Dim RegObj            As Object
   Dim RegValue          As String
   Const HKEY_CURRENT_USER = &H80000001

   Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
   RegObj.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr

   For Each Device In Devices
      RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
      sFullPName = Device & " on " & Split(RegValue, ",")(1) & vbCrLf
      If InStr(sFullPName, "HP LaserJet 2300L PS") Then GoTo Printloc1
   Next

' end
 
Printloc1:
  
 
    MyFileName = Trans
     
    Set appDist = New cACroDist
    sCurrentPrinter = Application.ActivePrinter 'Save the currently active printer
    sPDFVersionAndPort = sFullPName 'sCurrentPrinter ' Can't get location right Ne01: etc etc
    sPSFileName = Dir & "\" & MyFileName & ".ps"  'Name of PS file
    sPDFFileName = Dir & "\" & MyFileName & ".pdf" 'Name of PDF
    Application.ActivePrinter = sFullPName
    ActiveSheet.PrintOut ActivePrinter:=sPDFVersionAndPort, _
    PrintToFile:=True, PrToFileName:=sPSFileName 'Prints to PS

Open in new window


It faults out at:     Application.ActivePrinter = sFullPName  (line 56)

With a 1004: Method 'ActivePrinter' of object '_Application' Failed

Any ideas?
Rory ArchibaldCommented:
What is the value of sFullPName at the time?
Conor_NewmanAuthor Commented:
"HP Laserjet 2300L PS on LPT1:"
Rory ArchibaldCommented:
If you set the printer manually, what does application.activeprinter return?
Conor_NewmanAuthor Commented:
The exact same..  makes no sense..
Rory ArchibaldCommented:
Ah - I should have scrolled across. This line
 sFullPName = Device & " on " & Split(RegValue, ",")(1) & vbCrLf

Open in new window

should just be
 sFullPName = Device & " on " & Split(RegValue, ",")(1)

Open in new window

Conor_NewmanAuthor Commented:
If Application.ActivePrinter = sFullPName Then
    MsgBox "TRue!"
    Else
    MsgBox "False"
    End If

This is returning False..  but hovering over, they are identical (See attached jpg)
compare.jpg
Conor_NewmanAuthor Commented:
Aha!

Doh, should've seen that myself, works now :) Thanks a million!!
Conor_NewmanAuthor Commented:
Thanks guys, both of you. This one has been on mind a long time., nice to have it working as I wanted at last ;-)<br /><br />Great answers!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.