Get Printer Information Using PowerShell Via VBA

In today’s post, I thought I’d explore Printers via PowerShell a little.

Have you ever needed to determine what printers where installed on a PC?
Have you ever needed to determine the default printer on a PC?
Have you ever needed to stop, or start, the print spooler?

All these things are easy to accomplish with PowerShell.

Note that the functions provided below rely on my PS_GetOutput() function which is part of my post VBA – Run PowerShell Command.

General Printer Information

To start off, I thought it would be nice to be able to simply list the various printers on a PC. In PowerShell we could do something like:

Get-Printer|
Sort-Object Name|
Select-Object Name, Location, DriverName, Type

Thus, we can build a VBA wrapper function and create:

Public Function PS_GetInstalledPrinters()
    Dim sCmd                  As String

    sCmd = "$printers = Get-Printer|" & _
           "Sort-Object Name|" & _
           "Select-Object Name, Location, DriverName, Type" & vbCrLf & _
           "$combined = $printers | ForEach-Object { $_.Name + '--' + $_.Location + '--' + $_.DriverName + '--' + $_.Type }" & vbCrLf & _
           "$result = $combined -join '~~'"
    PS_GetInstalledPrinters = PS_GetOutput(sCmd)
End Function

and we could then use the above function by doing something like:

Function Test_Printers()
    Dim sPrinter             As String
    Dim aPrinters()          As String
    Dim vPrinter             As Variant
    Dim aPrinter()           As String

    sPrinter = PS_GetInstalledPrinters()
    aPrinters = Split(sPrinter, "~~")
    Debug.Print "Printer Name", "Location", "Driver", "Type"
    For Each vPrinter In aPrinters
        aPrinter = Split(vPrinter, "--")
        Debug.Print aPrinter(0), aPrinter(1), aPrinter(2), aPrinter(3)
    Next vPrinter
End Function

wish will output similar to

Printer Name  Location      Driver        Type
Brother HL-2270DW series Printer          http://192.168.0.105:80/WebServices/Device              Brother HL-2270DW series    Local
Fax                         Microsoft Shared Fax Driver Local
Microsoft Print to PDF                    Microsoft Print To PDF      Local
Microsoft XPS Document Writer                           Microsoft XPS Document Writer v4          Local
OneNote for Windows 10                    Microsoft Software Printer Driver         Local

Default Printer Information

Now, let us explore getting the name and other information about the Default Printer.

Get the Default Printer’s Name

What about determining the name of the default printer? Nothing could be easier! For this, the PowerShell command looks like:

Get-CimInstance -Query "SELECT * FROM Win32_Printer WHERE Default=$true"

So, once again, we need only build a wrapper function, something like:

Public Function PS_GetDefaultPrinterName()
    Dim sCmd                  As String

    sCmd = "(Get-CimInstance -Query """"""SELECT * FROM Win32_Printer WHERE Default=$true"""""").Name"
    PS_GetDefaultPrinterName = Replace(PS_GetOutput(sCmd), vbNewLine, "")
End Function

Now, with a single line of code you can retrieve the Default Printer’s name.

? PS_GetDefaultPrinterName

which returns something like

Microsoft Print to PDF

Note, that in this instance, since this is entirely done via a WMI call, we could bypass using PowerShell and use VBA to directly perform the WMI query. This would be faster. For instance, we could do something like:

Public Function WMI_GetDefaultPrinterName() As String
    On Error GoTo Error_Handler
    Dim oWMI                  As Object    'SWbemServicesEx 'WMI object to query about the PC's OS
    Dim sWMIQuery             As String    'WMI Query
    Dim oCols                 As Object    'SWbemServicesObjectSet
    Dim oCol                  As Object    'SWbemObjectEx
 
    Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
    sWMIQuery = "SELECT * FROM Win32_Printer WHERE Default=True"
    Set oCols = oWMI.ExecQuery(sWMIQuery)
    WMI_GetDefaultPrinterName = oCols.ItemIndex(0).Properties_("Name")
 
Error_Handler_Exit:
    On Error Resume Next
    Set oCols = Nothing
    Set oWMI = Nothing
    Exit Function
 
Error_Handler:
    If Err.Number = -2147217385 Then
        'Invalid query - Property doesn't exist
    Else
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: WMI_GetDefaultPrinterName" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
    End If
    Resume Error_Handler_Exit
End Function

and then call it by doing:

? WMI_GetDefaultPrinterName

which returns the same value as with the PowerShell approach:

Microsoft Print to PDF

Get More Information About the Default Printer

To get general information about the default printer, not just its name, we need to combine 2 PowerShell commands and that would look something like:

Get-Printer -Name (Get-CimInstance  -Query "SELECT * FROM Win32_Printer WHERE Default=$true").Name

Notice we use the command to get the default printer’s name and pass it to the Get-Printer CmdLet as the Name of the printer we want information about.

Now, we build our wrapper function, which should look like (pay attention to the quotes!):

Public Function PS_GetDefaultPrinterInfo()
    Dim sCmd                  As String

    sCmd = "Get-Printer -Name (Get-CimInstance -Query """"""SELECT * FROM Win32_Printer WHERE Default=$true"""""").Name|" & _
           "Select-Object Name, Location, DriverName, Type|" & vbCrLf & _
           "ForEach-Object { $_.Name + '--' + $_.Location + '--' + $_.DriverName + '--' + $_.Type }"
    PS_GetDefaultPrinterInfo = PS_GetOutput(sCmd)
End Function

and then we can use it by doing something along the lines of:

Function Test_DefaultPrinter()
    Dim sPrinter              As String
    Dim aPrinter()            As String

    sPrinter = PS_GetDefaultPrinterInfo()
    aPrinter = Split(sPrinter, "--")
    Debug.Print "Printer Name", "Location", "Driver", "Type"
    Debug.Print aPrinter(0), aPrinter(1), aPrinter(2), aPrinter(3)
End Function

which will result in something similar to:

Printer Name                Location      Driver                        Type
Microsoft Print to PDF                    Microsoft Print To PDF        Local

Changing the Default Printer

PowerShell makes it extremely easy to change the default printer and it can be done with the following command:

(Get-WmiObject -class Win32_Printer|Where {$_.Name -eq 'YourPrinterNameGoesHere'}).SetDefaultPrinter()

Thus, we create a simple VBA wrapper for the command, giving us:

Public Function PS_SetDefaultPrinter(ByVal sPrinterName As String)
    Dim sCmd                  As String

    'We could first validate the printer actually exists on this PC!

    sCmd = "(Get-WmiObject -class Win32_Printer|Where {$_.Name -eq '" & sPrinterName & "'}).SetDefaultPrinter()"
    PS_Execute (sCmd)
End Function

Now, we can change the default printer of a PC by simply doing

Call PS_SetDefaultPrinter("Microsoft XPS Document Writer")

Call PS_SetDefaultPrinter("Microsoft Print to PDF")

etc…

DEVelopers HUT