copy

Sunday, 15 July 2018



Last updated: 15/07/2018, 2 min read (without the code)

Get & Set The Default Windows Printer With VBA


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 purposes. However, what if your VBA “application” needs to know how many printers are installed and available in the 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 example. 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 using Windows APIs in your VBA code, you might have troubles adjusting the API calls. 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 so as 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 actually 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 with other programming languages, but not with VBA.



VBA code



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, there are 2 macros that 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:        xristos.samaras@gmail.com
    '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



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info