Need VBA Help with Printer Status

[AngelSpeaks]

Hi Experts,

I’m currently working on an application that shares images taken by my photobooth. By sharing, I mean the user can print, email, or text the image (still working on the texting part). For error handling, and to reduce the wait time on these requests, I’ve done the following:

Email – ping Google before emailing. If Google not available, message telling user that the email will be sent later (all share requests are logged to tblShares with a yes/no field named Sent).

Printing – what I want to do is check to see if the photo printer is ready for printing. This is where I’m having issues. I’ve found VBA code that checks the printer status. The status is “Idle” and the printer is then marked not available. Yet the Microsoft Print to PDF is always ready. I’ve always tried selecting printers that I know aren’t working (driver issues), yet the Print Preview works on these.

Any suggestions?

Here is the code I tried already Both snippets have been tried.

Thanks

Public Function IsPrinterReady(Optional strPrinter As String = "") As Boolean

'Returns True if printer is turned on and ready.
'If no strPrintername provided, active printer is assumed.
'VBA code by Mor Sagmon


    If (strPrinter = "") Then
       strPrinter = Application.Printer
       IsPrinterReady = False
   End If

  
    'If strPrinter has port part - discard
    Dim intOnPosition As Integer

    intOnPosition = InStr(1, strPrinter, " on ")
    If (intOnPosition > 0) Then
        strPrinter = left(strPrinter, intOnPosition - 1)
    Else
        intOnPosition = InStr(1, strPrinter, " " & Chr(225)) 'Replace 225 with ASCII code as per your own Windows language.
        If (intOnPosition > 0) Then
            strPrinter = left(strPrinter, intOnPosition - 1)
        End If
    End If
 

    On Error GoTo FailedPrinter

    IsPrinterReady = Not GetObject("winmgmts:\\.\root\CIMV2").Get("Win32_Printer='" & strPrinter & "'").WorkOffline

    Exit Function

FailedPrinter:

   'Implement error handling here if necessary

End Function

Public Function PrinterOffline(Optional pstrPrinter As String = "Default") As Boolean
    'Printer status
    'Other (1)
    'Unknown (2)
    'Idle (3)
    'Printing (4)
    'Warmup (5)
    'Stopped Printing(6)
    'Offline (7)
    Dim strWhere As String
    Dim objWMI As Object
    Dim objPrinters As Object
    Dim objPrinter As Object
    
    Set objWMI = GetObject("winmgmts:\\.\root\CIMV2")
    If LCase$(pstrPrinter) = "default" Then
        strWhere = "Default = True"
    Else
        strWhere = "Name = '" & pstrPrinter & "'"
    End If
    Set objPrinters = objWMI.ExecQuery("SELECT * FROM Win32_Printer WHERE " & strWhere)
    For Each objPrinter In objPrinters
        PrinterOffline = objPrinter.WorkOffline
         'Write the results to the worksheet.
       ' MsgBox objPrinter.Name & " " & objPrinter.PrinterStatus & " " & objPrinter.Local
        If (objPrinter.Default) Then
            getDefaultPrinter = objPrinter.Name
        End If
        Exit For
    Next
      
    Set objPrinter = Nothing
    Set objPrinters = Nothing
    Set objWMI = Nothing
End Function

 

[June7]

First procedure written for Excel but not working for me in Excel.

Running in Access.
1) in first procedure I get "Object doesn't support this property or method" on Application.Printer. Change to Application.Printer.DeviceName.
2) in second procedure I get "variable not defined" error on getDefaultPrinter. Code needs to strip port ID from printer string (like first procedure does).

Get same result whether printer is plugged in or not. All this seems to do is indicate printer is known to Windows because it has been added.

Disable error handler when debugging.