Introduction
Although VBA is a great language for building Office “applications”, when it comes to handling Windows devices, such as a printer, for example, things start to become difficult. The obvious reason is that the VBA was not designed for this kind of purpose. However, what if your VBA “application” needs to know how many printers are installed and available on a particular computer? What if your “application” must set the default Windows printer to “Adobe PDF”, hence, printing in a PDF file, instead of a paper?
To answer the last two questions, somebody might think to search for some old Visual Basic 6.0 code snippets. In other words, he/she might try to find solutions based on some old examples. While this totally OK, the VB 6.0 solutions will probably rely on one or more Windows APIs. So, if you are not very familiar with Windows APIs, you might have trouble adjusting the API functions into your VBA code. The latter is particularly true when the Office version in which your “application” will run, is 64bit. In that case, you need to carefully “alter” the API calls to work in 64bit (e.g. data type conversion: the Long should become LongPtr in 64bit).
But, are there any simpler solutions? Yes, there are! If we combine the VBA with Windows Management Instrumentation (WMI) and Windows Script Host (WSH) objects we can do wonders quite easily! The VBA module that follows demonstrates several techniques that show: how somebody can get the installed printers from a computer, how to check if a printer is the default one, and, finally, how to set a particular printer to be the default one.
Note: the term “application” in the above paragraphs implies a solution to a given problem (e.g. a budget spreadsheet), not an application with the strict definition of the term (e.g. an executable). You can develop “real” applications using VB 6.0, as well as other programming languages (e.g. C#), but not with VBA.
VBA code for printers
The code below is an entire module that contains 3 VBA functions:
- PrinterExists: A function that checks if there is a printer installed with the given name.
- IsDefaultPrinter: A function that checks if the given printer corresponds to the default Windows printer.
- SetDefaultPrinter: A functions that set the given printer to be the default Windows printer.
Next, 2 macros demonstrate how these 3 functions can be used/combined to do something useful:
- GetInstalledPrinters: A macro that loops through all the installed printers of the computer and writes their names in the “Printers” worksheet. Moreover, it checks if each printer is the default one or not.
- SetAsTheDefaultPrinter: A macro that sets the selected range, if it corresponds to an installed printer, to be the default Windows printer. The user must select a range within the given range of (valid) printers, and, then, run the macro.
Option Explicit
'-------------------------------------------------------------------------------------------------------------------------
'This module contains 3 functions that can help you whenever you deal with printers from VBA:
'- PrinterExists: Checks if there is a printer installed with the given name.
'- IsDefaultPrinter: Checks if the given printer corresponds to the default windows printer.
'- SetDefaultPrinter: Makes the given printer to be the default one.
'
'After these functions, there are 2 macros that demonstrate how these functions can be used to do something useful.
'Note that the macros were adjusted to work with the specific workbook that contains the worksheet named "Printers".
'- GetInstalledPrinters: Loops through all the installed printers and writes their names in the "Printers" worksheet.
' Moreover, it checks if each printer is the default one.
'
'- SetAsTheDefaultPrinter: The user selects a range within the given range of printers and then by running the macro
' the selected printer becomes the default one.
'
'Written By: Christos Samaras
'Date: 14/08/2018
'E-mail: [email protected]
'Site: https://www.myengineeringworld.net
'-------------------------------------------------------------------------------------------------------------------------
Function PrinterExists(printerName As String) As Boolean
'Declaring the necessary variables.
Dim computer As String
Dim wmiService As Object
Dim installedPrinters As Variant
Dim printer As Object
On Error Resume Next
'Check if the printer name is empty.
If printerName = vbNullString Then Exit Function
'Set the computer. Dot means the computer running the code.
computer = "."
'Get the WMI object
Set wmiService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & computer & "\root\cimv2")
'Retrieve information about the installed printers (by running a query).
Set installedPrinters = wmiService.ExecQuery("Select * from Win32_Printer")
'If an error occurs in the previous step, the function should exit and return False.
If Err.Number <> 0 Then Exit Function
'Loop through all the installed printers. If the given name matches to any of the installed printers, exit the loop and return True.
For Each printer In installedPrinters
If UCase(printer.Name) = UCase(printerName) Then
PrinterExists = True
Exit Function
End If
Next printer
On Error GoTo 0
End Function
Function IsDefaultPrinter(printerName As String) As Boolean
'Declaring the necessary variables.
Dim computer As String
Dim wmiService As Object
Dim installedPrinters As Variant
Dim printer As Object
On Error Resume Next
'Check if the printer name is empty.
If printerName = vbNullString Then Exit Function
'Set the computer. Dot means the computer running the code.
computer = "."
'Get the WMI object
Set wmiService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & computer & "\root\cimv2")
'Retrieve information about the installed printers (by running a query).
Set installedPrinters = wmiService.ExecQuery("Select * from Win32_Printer")
'If an error occurs in the previous step, the function should exit and return False.
If Err.Number <> 0 Then Exit Function
'Loop through all the installed printers. If the given name matches to any of the installed printers
'and the Default property is set to True, exit the loop and return True.
For Each printer In installedPrinters
If UCase(printer.Name) = UCase(printerName) And printer.Default = True Then
IsDefaultPrinter = True
Exit Function
End If
Next printer
On Error GoTo 0
End Function
Function SetDefaultPrinter(printerName As String) As Boolean
'Declaring the necessary variable.
Dim wshNetwork As Object
On Error Resume Next
'Check if the printer name is empty.
If printerName = vbNullString Then Exit Function
'Test if the printer is already the default one. If yes, return True.
If IsDefaultPrinter(printerName) = True Then
SetDefaultPrinter = True
Exit Function
End If
'The printer is not the default one. Create the WScript.Network object.
Set wshNetwork = CreateObject("WScript.Network")
'If the WScript.Network object was not created, exit.
If wshNetwork Is Nothing Then Exit Function
'Set the given printer to be the default one.
wshNetwork.SetDefaultPrinter printerName
'Release the WScript.Network object.
Set wshNetwork = Nothing
'Check (again) if after the change, the given printer is indeed the default one.
SetDefaultPrinter = IsDefaultPrinter(printerName)
On Error GoTo 0
End Function
Sub GetInstalledPrinters()
'Declaring the necessary variables.
Dim sht As Worksheet
Dim computer As String
Dim wmiService As Object
Dim installedPrinters As Variant
Dim printer As Object
Dim i As Integer
On Error Resume Next
'Set the worksheet in which the information will be written.
Set sht = ThisWorkbook.Worksheets("Printers")
'Check if the sheet exist (there is no error).
If Err.Number <> 0 Then
MsgBox "The sheet does not exists!", vbCritical, "Sheet Name Error"
Exit Sub
End If
'Clear existing data.
Call ClearAll
'Set the computer. Dot means the computer running the code.
computer = "."
'Get the WMI object
Set wmiService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & computer & "\root\cimv2")
'Retrieve information about the installed printers (by running a query).
Set installedPrinters = wmiService.ExecQuery("Select * from Win32_Printer")
'If an error occurs in the previous step, inform the user.
If Err.Number <> 0 Then
MsgBox "Could not retrieve the printer information from WMI object!", vbCritical, "WMI Object Error"
Exit Sub
End If
'Set the starting row.
i = 5
'Loop through all the installed printers and get their name. Check if one of them is the default one.
For Each printer In installedPrinters
'Write the results to the worksheet.
sht.Range("C" & i).Value = printer.Name
sht.Range("D" & i).Value = printer.Default
i = i + 1
Next printer
On Error GoTo 0
End Sub
Sub SetAsTheDefaultPrinter()
'Declaring the necessary variable.
Dim sht As Worksheet
Dim rng As Range
On Error Resume Next
'Set the worksheet in which the information will be written.
Set sht = ThisWorkbook.Worksheets("Printers")
'Check if the sheet exist (there is no error).
If Err.Number <> 0 Then
MsgBox "The sheet does not exists!", vbCritical, "Sheet Name Error"
Exit Sub
End If
'Get the intersected range.
Set rng = Application.Intersect(sht.Range("C5:C24"), Selection.Range("A1"))
'If there is no "common" range, exit.
If rng Is Nothing Then
MsgBox "The selected range is outside the 'C5:C24' range!", vbCritical, "Invalid Common Range Error"
Exit Sub
End If
'If the common range is empty, exit.
If IsEmpty(rng) Then
MsgBox "The range you selected is empty!", vbCritical, "Empty Range Error"
Exit Sub
End If
'Check if the selected printer is already the default printer.
If IsDefaultPrinter(rng.Range("A1")) Then
MsgBox "The selected printer '" & rng.Range("A1") & "' is already the default printer!", vbExclamation, "Default Printer Warning"
Exit Sub
End If
'Finally, set the selected printer as the default one and inform the user.
If SetDefaultPrinter(rng.Range("A1")) = True Then
'Run the GetInstalledPrinters macro to "prove" the change.
Call GetInstalledPrinters
'The process succeded.
MsgBox "The selected printer '" & rng.Range("A1") & "' was set as the default printer!", vbInformation, "Success"
Else
'The process failed.
MsgBox "It was impossible to set the selected printer '" & rng.Range("A1") & "' as the default printer!", vbCritical, "Failure"
End If
End Sub
Sub ClearAll()
'Declaring the necessary variable.
Dim sht As Worksheet
On Error Resume Next
'Set the worksheet in which the information will be written.
Set sht = ThisWorkbook.Worksheets("Printers")
'Check if the sheet exist (there is no error).
If Err.Number <> 0 Then
MsgBox "The sheet does not exists!", vbCritical, "Sheet Name Error"
Exit Sub
End If
'Clear the data.
sht.Range("C5:D24").ClearContents
End Sub
Note that the two macros were adjusted to work with the specific workbook that contains a worksheet named “Printers”. You can find this workbook in the Downloads section that follows.
Downloads
The file can be opened with Excel 2007 or newer. Please enable macros before using it.
Hi, Peter,
I am grateful for your kind comment!
It’s great to hear that this code has been helpful to you.
Best Regards,
Christos
Christos, this is brilliant. I had code that worked for years with W7 but as soon as I had to use W10… nothing but problems. Your code works great. Thanks Heaps!!!
Hi, Flavio,
Thank you very much for your kind words.
It’s nice to see you around.
Best Regards,
Christos
Hi Christos. Thank you so much for your contribution.
I’m loving this website.
You ‘re welcome!
Great post! thanks you.