Printing a Workbook

ThisWorkbook.PrintOut(From, To, Copies)

Printing to a File

ActiveSheet.PrintOut PrintToFile:=Time, PrToFileName:="name of file.prn"             'Printing to a file  

Print to a network printer

The example macros below shows how to get the full network printer name (useful when the network printer name can change) and print a worksheet to this printer:

Sub PrintToNetworkPrinterExample() 
Dim strCurrentPrinter As String, strNetworkPrinter As String
    strNetworkPrinter = GetFullNetworkPrinterName("HP LaserJet 8100 Series PCL")
    If Len(strNetworkPrinter) > 0 Then ' found the network printer
        strCurrentPrinter = Application.ActivePrinter
' change to the network printer
        Application.ActivePrinter = strNetworkPrinter
        Worksheets(1).PrintOut ' print something
' change back to the previously active printer
        Application.ActivePrinter = strCurrentPrinter
    End If
End Sub

Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String
' returns the full network printer name
' returns an empty string if the printer is not found
' e.g. GetFullNetworkPrinterName("HP LaserJet 8100 Series PCL")
' might return "HP LaserJet 8100 Series PCL on Ne04:"
Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long
    strCurrentPrinterName = Application.ActivePrinter
    i = 0
    Do While i < 100
        strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":"
        On Error Resume Next ' try to change to the network printer
        Application.ActivePrinter = strTempPrinterName
        On Error GoTo 0
        If Application.ActivePrinter = strTempPrinterName Then
' the network printer was found
            GetFullNetworkPrinterName = strTempPrinterName
            i = 100 ' makes the loop end
        End If
        i = i + 1
' remove the line below if you want the function to change the active printer
    Application.ActivePrinter = strCurrentPrinterName ' change back to the original printer
End Function

Change the default printer

This example macro shows how to print a selected document to another printer then the default printer. This is done by changing the property Application.ActivePrinter:

Sub PrintToAnotherPrinter() 
Dim strCurrentPrinter As String
    strCurrentPrinter = Application.ActivePrinter ' store the current active printer
    On Error Resume Next ' ignore printing errors
    Application.ActivePrinter = "microsoft fax on fax:" ' change to another printer
    ActiveSheet.PrintOut ' print the active sheet
    Application.ActivePrinter = strCurrentPrinter ' change back to the original printer
    On Error Goto 0 ' resume normal error handling
End Sub

Identify Default Printer

Private Declare Function GetProfileStringA Lib "kernel32" (ByVal lpAppName As String, ByVal lpKeyName As String, ByVal lpDefault As String,  ByVal lpReturnedString As String, ByVal nSize As Long) As Long 

Sub DefaultPrinterInfo()
    Dim strLPT As String * 255
    Dim Result As String
    Call GetProfileStringA _
       ("Windows", "Device", "", strLPT, 254)
    Result = Application.Trim(strLPT)
    ResultLength = Len(Result)

    Comma1 = Application.Find(",", Result, 1)
    Comma2 = Application.Find(",", Result, Comma1 + 1)

' Gets printer's name
    Printer = Left(Result, Comma1 - 1)

' Gets driver
    Driver = Mid(Result, Comma1 + 1, Comma2 - Comma1 - 1)

' Gets last part of device line
    Port = Right(Result, ResultLength - Comma2)

' Build message
    Msg = "Printer:" & Chr(9) & Printer & Chr(13)
    Msg = Msg & "Driver:" & Chr(9) & Driver & Chr(13)
    Msg = Msg & "Port:" & Chr(9) & Port

' Display message
    MsgBox Msg, vbInformation, "Default Printer Information"
End Sub

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext