Programming Excel with VBA and .NET

Name

[Application.]ActivePrinter [= setting]

Synopsis

Sets or returns the printer that Excel will use. When setting this property, the printer name must include the port number, for example:

Sub SetPrinter(  )
    ActivePrinter = "\\wombat2\Lexmark Z52 Color Jetprinter on Ne04:"
End Sub

The preceding code tells Excel to use a shared printer over the network. The port number used by Excel is Ne nn : for virtual ports but is LPT n : or COM n : for physical ports. The following code gets an array of the available printers in a format that can be used by Excel:

Function GetPrinters() As String(  )
    ' Use a suitably large array (supports up to 100 printers).
    ReDim result(100) As String
    Dim wshNetwork As Object, oPrinters As Object, temp As String
    ' Get the network object
    Set wshNetwork = CreateObject("WScript.Network")
    Set oPrinters = wshNetwork.EnumPrinterConnections
    ' Get the current active printer
    temp = ActivePrinter
    ' Printers collection has two elements for each printer.
    For i = 0 To oPrinters.Count - 1 Step 2
        ' Set the default printer.
        wshNetwork.SetDefaultPrinter oPrinters.Item(i + 1)
        ' Get what Excel sees.
        result(i \ 2) = ActivePrinter
        ' For debug purposes, show printer.
        Debug.Print ActivePrinter
    Next
    ' Trim empty elements off the array.
    ReDim Preserve result(i \ 2)
    ' Change back to original printer
    ActivePrinter = temp
    ' Return the result.
    GetPrinters = result
End Function