VBA sub to list printers and their status

[lalbatros]

Hello,

I have Googled for some VBA code to list printers and their status.
I found out that the wshom.ocx can help a little bit, at least to get a printer list.
However, I did not a find a (decent) way to get the status of any printer.
Would you know some way to get this information?

Alternatively, I would be interrested in a simple sub that changes the default printer when a local printer printer is connected.

Thanks

 

[Tom Urtis]

The only status that matters for printers is which one is active, meaning all the others are inactive, unless by "status" you maybe mean if they have a Gucci label or were purchased at Harrod's ?

Stick this beast into a NEW FRESH standard module and run the Printers macro to list the names and status of each printer, test this on a new sheet as it will delete data in columns A and B, worked for me anyway:

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
   Dim tmpFunctionResult As Boolean
   Dim aFileTimeStruc As FILETIME
   Dim AddressofOpenKey As Long, aPrinterName As String
   Dim aPrinterIndex As Integer, aPrinterNameLen As Long
   Const KEY_ENUMERATE_SUB_KEYS = &H8
   Const HKEY_LOCAL_MACHINE = &H80000002
   Set fncEnumInstalledPrintersReg = New Collection
   aPrinterIndex = 0
   tmpFunctionResult = Not CBool _
      ( _
      RegOpenKeyEx _
      ( _
      hKey:=HKEY_LOCAL_MACHINE, _
      lpSubKey:="SYSTEM\CURRENTCONTROLSET\CONTROL\PRINT\PRINTERS", _
      ulOptions:=0, _
      samDesired:=KEY_ENUMERATE_SUB_KEYS, _
      phkResult:=AddressofOpenKey _
      ) _
      )
   If tmpFunctionResult = False Then GoTo ExitFunction
   Do
      aPrinterNameLen = 255
      aPrinterName = String(aPrinterNameLen, CStr(0))
      tmpFunctionResult = Not CBool _
         ( _
         RegEnumKeyEx _
         ( _
         hKey:=AddressofOpenKey, _
         dwIndex:=aPrinterIndex, _
         lpName:=aPrinterName, _
         lpcbName:=aPrinterNameLen, _
         lpReserved:=0, _
         lpClass:=vbNullString, _
         lpcbClass:=0, _
         lpftLastWriteTime:=aFileTimeStruc _
         ) _
         )
      aPrinterIndex = aPrinterIndex + 1
      If tmpFunctionResult = False Then Exit Do
      aPrinterName = Left(aPrinterName, aPrinterNameLen)
      On Error Resume Next
      fncEnumInstalledPrintersReg.Add aPrinterName
      On Error GoTo 0
   Loop
   Call RegCloseKey(AddressofOpenKey)
   '
   Exit Function
ExitFunction:
   If Not AddressofOpenKey = 0 Then _
      Call RegCloseKey(AddressofOpenKey)
   Set fncEnumInstalledPrintersReg = Nothing
End Function
 
Sub Printers()
Application.ScreenUpdating = False
   Dim aPrinter As Variant
   Dim iRow%, lenPrinter%
   Columns(1).Clear: Columns(2).Clear
   With Range("A1:B1")
   .Value = Array("Printer", "Status")
   .Font.Bold = True
   End With
   iRow = 2
   For Each aPrinter In fncEnumInstalledPrintersReg
      Cells(iRow, 1).Value = aPrinter
      lenPrinter = Len(aPrinter)
      If aPrinter <> Left(Application.ActivePrinter, lenPrinter) Then
      Cells(iRow, 2).Value = "Not Active"
      Else
      Cells(iRow, 2).Value = "Active"
      With Range(Cells(iRow, 1), Cells(iRow, 2))
      .Font.Bold = True
      .Interior.ColorIndex = 8
      End With
      
      End If
      iRow = iRow + 1
         Next aPrinter
   
Columns(1).AutoFit: Columns(2).AutoFit
Application.ScreenUpdating = True
End Sub

 

[lalbatros]

Tom,

Thanks for your suggestion.

When I go to the "Printers and Faxes" Settings menu, I get a window where the printers are listed.
One column of this screen is called "status".
I observed only two possible entries: "Ready" or "Offline". Maybe there are more.
This is the information I want to retrive in VBA.

More specifically, I want to check if a given printer is "Offline" or not.
If it is not "Offline", then I want to make it my default printer.

Would you be able to modify your code to get this information?

Thanks

 

[Tom Urtis]

Look at my Printers macro where you see this

Cells(iRow, 2).Value = "Not Active"
      Else
      Cells(iRow, 2).Value = "Active"

and make it be this

Cells(iRow, 2).Value = "Off Line"
      Else
      Cells(iRow, 2).Value = "Ready"

which to me would do what you want, as it is only the nomenclature that I think we are talking about. The bottom line is, you want to know which printer will be the one that prints something off your computer if you were to click the Print icon button at any given time, and teh above code would do that.

 

[lalbatros]

Tom,

Referring to you Printers macro, the statement below does not detect what I want to detect:

aPrinter <> Left(Application.ActivePrinter, lenPrinter)

Actually, I just want to detect if a given printer is physically available for printing.

The statement above detects if a printer is the default printer of Excel.
This is not exactly what I want since a printer can be the default printer even if it is not available (like if unplugged).

The Start menu item called "Printers and Faxes" provides printers information in a list.
The status column indicates for each printer if it is ready or offline.
I would like to get exactly this information in VBA.

Thanks

 

[Tom Urtis]

I've seen code where people say they have accomplished what you are trying to do, but I have not gotten them to work for me.

The problem is not the code or the request, the problem is the computer and the printer drivers.

For the information to be returned that you want, the printer would need to know it. The printer can only know it of the computer tells it. The computer can only tell the printer if the computer has the proper printer drivers supplied by the printer manufacturer, and that are updated, to enable the spooler to write to the printer. The status is only updated when the spooler writes to the printer. So, if there is no writing, there is no readable status. Even the status can be misleading, such as if there are documents stalled in the queue, the status can still be ready, even though it is not the default printer at that time.

I'm not saying your request is not do-able, just that its percentage of accuracy will be compromised, and that's if the computer and printers are totally in sync driver-wise. In any case, I cannot recommend a reliable solution, or even one I've tested successfully.

I'd suggest you keep hunting around the web, I see you've done that already but sometimes new posts pop up. Careful though, what works on your computer with code like this (if indeed you find a solution that does work for you) might not work on other computers if this project is meant for other users on other systems.

Good luck.

 

[jimrward]

found the following which generates a list of printers however there is more output than required so could be trimmed to get a list

http://support.microsoft.com/kb/q166008/

and the second part of Toms macro could be used to get the current status

 

[fazalabbas]

I dont agree with Tom Urtis code, the logic of "Active" is if something is current printer then its "Active". Where as Active printer is one that is actually turned on and ready for printing.

It shows 3 printers on my machine. 2 Active 1 in active. The one that its showing Active is my default printer but turned off sitting in front of me.

Abbas