Excel VBA: How to Check if Printer is Ready

Your VBA program needs to send out an original document to the printer. What if the printer is offline, or disconnected? Verify the printer is ready before printing or notify the user of a problem * Here’s the VBA function for that

Why check if the printer is ready?

The first situation in which I encountered the need to verify the printer is ready, was in an application I developed for small business management, in Excel.

In my home country, Israel, tax regulations dictate that the tax invoice confirming the receipt of a payment is printed exactly ONCE. This would be the ORIGINAL legal document confirming to the tax authorities of the transaction. Any subsequent copies of this document (if the original got lost, for example) must have the label “COPY” on it.

This implies that my application needs to maintain the status of the tax invoice. Once it was printed with the “ORIGINAL” label on it – it must be flagged in the system accordingly.

Consider the situation in which my application had sent the document to the printer and flagged it as having being printed, while the printer was offline, or disconnected. This invoice may be flagged as having being printed already, while it hasn’t really.

Other reasons for verifying the printer is ready have to do with the human experience working with the application. It feels more controlled and reassuring to receive a message asking to prepare the printer, instead of being surprised by an error message AFTER you have sent a document to the printer.

You can check if the printer is ready in an early stage in the process and find the way to notify the user in a less disruptive fashion.

Accessing the Windows API from VBA

The Windows API that we can use in order to ask information on devices connected and identified by Windows, is the Windows Management Instrumentation API (WMI).

The WMI service that allows us to ask for information on an accessible machine (computer) is winmgmts, expecting a path to the machine and the namespace (scope of objects) we want access to.

In VBA, we create a handle to the Common Information Model (CIM) classes (the default Namespace in Windows) in the local machine with the following:

GetObject("winmgmts:\\.\root\CIMV2")

The dot, representing the local (current) machine, can be replaced with another host name accessible to your program.

Next, we can call the Get method to gain access to objects in the Win32 CIM environment, such as a connected printer.

More technical information on the WMI is available here.

Is Printer Ready?

In order to ask for information about a connected printer, we need to obtain the printer’s name as listed in the Windows Printers repository.

The below suggested function obtains the default printer known to Excel (Windows default printer), or optionally receives any printer name as an argument, and returns True if that printer is online and ready.

There is a little adjustment we need to do here, as the winmgmts service argument referencing a printer expects only the printer name, while in Windows, printers are identified also by the Port they use.

Run this VBA command and you’ll you will receive a string containing the printer name and the port, separated by the word: “ on “:

Application.ActivePrinter

Will return something like that:

Brother MFC-J470DW Printer on Ne04:

We need to strip off the “ on Ne04:“ part and we can identify its index in the whole string by finding the “ on “ sub-string – a common string manipulation using the Left and InStr VBA functions.

A little hurdle here is that the term “ on “ is returned in the language (local) defined in your Windows system, so it may not be enough to find the “ on ” sub-string for the cut-out.

What I did in the following function to accommodate for that situation, is that I’m looking for the combination of the preceding space before the first character of the word “ on “, and the first character itself – in the target language. You should find out what is the ASCII code of the first letter of the word “on” in your Windows language and replace where appropriate. You can use the VBA Asc() function with that letter to find out. The following function currently works for Hebrew, in which the ASCII code of that first character turns out to be 225.

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.ActivePrinter

    IsPrinterReady = False

    '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

Last Notes on IsPrinterReady

Obviously, this only works on Windows.

You can implement any error handling functionality you need in the FailedPrinter: section. Read my Blog post on Error Handling in VBA here.

Feel free to use this function in your programs, with the proper credit to me. just add a comment: VBA code by Mor Sagmon.

This function is one of 105 VBA functions I developed over the years and use in projects I deliver to my customers. You can download the 105 VBA functions package here: vbafunctions.com.

A step-by-step explanation of every line of code in these 105 VBA functions package is offered on my Udemy course here: 105 Excel VBA Functions Explained.