Check for a printer in Excel VBA Macro

I’ve been doing upgrades to Microsoft Office 2003 Excel workbooks – specifically to VBA macros in the workbooks.

The workbooks are used as PeopleSoft nVision layouts.  PS nVision utilizes Excel to provide reports – however nVision is run on a server.

During the upgrade I kept running into one error in particular – due to the server I was working on not having a printer driver installed.  A number of the macros did print set up as part of the report formatting – without a printer driver the code would bomb.  So I came up with the following function – it determines if a printer is set up on the machine, and returns a Boolean value:

Public Function IsPrinterInstalled() As Boolean
On Error GoTo IsPrinterInstalled_ERR
Dim objWMIService, colInstalledPrinters As Object
Dim strComputer As String
Dim i As Integer

strComputer = “.”
Set objWMIService = GetObject( _
“winmgmts:” & “{impersonationLevel=impersonate}!\\” _
& strComputer & “\root\cimv2”)
Set colInstalledPrinters = objWMIService.ExecQuery _
(“Select * from Win32_Printer”)

i = colInstalledPrinters.Count

Set objWMIService = Nothing
Set colInstalledPrinters = Nothing

If i > 0 Then
 IsPrinterInstalled = True
  IsPrinterInstalled = False
End If

Exit Function

 If Not objWMIService Is Nothing Then
   Set objWMIService = Nothing
End If

If Not colInstalledPrinters Is Nothing Then
 Set colInstalledPrinters = Nothing
End If

IsPrinterInstalled = False

End Function


About Lee Greffin
Just another programmer...

One Response to Check for a printer in Excel VBA Macro

  1. tychabrahe says:

    This worked great! My only complaint is that when you posted the code here it replaced ordinary quotes with smartquotes, which don’t work in Excel.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: