Setting up a LOCAL printer

[rjtaylor]

My Excel VBA program needs a specific Print to Microsoft PDF printer set up
I need to walk my users through this process.
Going to Printers and Scanners and selecting Add Printer
Then select the printer wasn't listed and select Add Local Printer or Network Printer with Manual Settings
Then select Create a new Port and select Local Port from drop down.
Then type the name of this port (Which will be a directory and file on the C drive
Then add a name and eventually change the driver to Microsoft Print to PDF.

Is there a way to complete these tasks through Code?

 

[John_w]

This macro runs two PowerShell commands, Add-PrinterPort to add a new printer port with the fixed PDF file name "C:\Temp\PrintToPDF.pdf" and Add-Printer to add a new printer named "Print To PDF" which prints to the fixed PDF file using the Microsoft Print To PDF driver.

Public Sub Add_Print_To_PDF_Printer()

    Dim WShell As Object
    Dim WSExec As Object
    Dim portName As String
    Dim printerName As String
    Dim driverName As String
    Dim PScommand As String
    Dim PSerror As Variant
    
    portName = "C:\Temp\PrintToPDF.pdf"
    printerName = "Print To PDF"
    driverName = "Microsoft Print To PDF"

    Set WShell = CreateObject("WScript.Shell")
    
    'First, add the new printer port as the fixed PDF file name
    
    PScommand = "Add-PrinterPort -Name '" & portName & "'"
    Set WSExec = WShell.Exec("powershell.exe -ExecutionPolicy Bypass -command " & PScommand)
    PSerror = WSExec.StdErr.ReadAll
    If PSerror <> "" Then
        If MsgBox(PScommand & vbCrLf & vbCrLf & PSerror, vbOKCancel, "PowerShell Add-PrinterPort error") = vbCancel Then
            Exit Sub
        End If
    End If

    'Second, add a new printer which prints to the fixed PDF file using the Microsoft Print To PDF driver
    
    PScommand = "Add-Printer -Name '" & printerName & "' -DriverName '" & driverName & "' -PortName '" & portName & "'"
    Set WSExec = WShell.Exec("powershell.exe -ExecutionPolicy Bypass -command " & PScommand)
    PSerror = WSExec.StdErr.ReadAll
    If PSerror <> "" Then
        If MsgBox(PScommand & vbCrLf & vbCrLf & PSerror, vbOKCancel, "PowerShell Add-Printer error") = vbCancel Then
            Exit Sub
        End If
    End If

End Sub

 

[rjtaylor]

Awesome code worked like a charm. How would I check to make sure it has not already been run. In other words, only run code if Printer and Port are not already set up.

 

[John_w]

This macro creates a PowerShell script which does everything and calls it. It captures the script's StdOut and StdErr so that you can display any errors.

Public Sub PS_Add_Printer()

    Dim PSscript As String
    Dim PSscriptFile As String
    Dim WShell As Object 'WshShell
    Dim WSExec As Object 'WshExec
    Dim portName As String
    Dim printerName As String
    Dim driverName As String
    Dim PScommand As String
    Dim PSerror As Variant, PSoutput As Variant, i As Long
        
    portName = "C:\Temp\PrintToPDF.pdf"
    printerName = "Print To PDF"
    driverName = "Microsoft Print To PDF"
    
    PSscript = "[cmdletbinding()]" & vbCrLf & _
               "param([Parameter(Mandatory)] [string]$PrinterName, [Parameter(Mandatory)] [string]$PortName, [Parameter(Mandatory)] [string]$DriverName)" & vbCrLf & _
               "    Write-Host ""-PrinterName '$PrinterName' -PortName '$PortName' -DriverName '$DriverName'""" & vbCrLf & _
               "    try {" & vbCrLf & _
               "        Write-Host ""Get printer port '$PortName'""" & vbCrLf & _
               "        $null = Get-PrinterPort -Name $PortName" & vbCrLf & _
               "    }" & vbCrLf & _
               "    catch {" & vbCrLf & _
               "        Write-Host ""Port does not exist: add printer port '$PortName'""" & vbCrLf & _
               "        Add-PrinterPort -Name $PortName" & vbCrLf & _
               "    }" & vbCrLf & _
               "    try {" & vbCrLf & _
               "        Write-Host ""Add printer '$PrinterName' with driver '$DriverName'""" & vbCrLf & _
               "        $null = Get-PrinterDriver -Name $DriverName" & vbCrLf & _
               "        Add-Printer -Name $PrinterName -PortName $PortName -DriverName $DriverName" & vbCrLf & _
               "        Write-Host ""Printer added successfully""" & vbCrLf & _
               "    }" & vbCrLf & _
               "    catch {" & vbCrLf & _
               "        Write-Host ""Failed to add printer, error was:""" & vbCrLf & _
               "        $PSCmdlet.WriteError($_)" & vbCrLf & _
               "    }"
    
    PSscriptFile = Environ("TEMP") & "\Add_Printer.ps1"
    Open PSscriptFile For Output As #1
    Print #1, PSscript
    Close #1
    
    PScommand = "powershell -ExecutionPolicy Bypass -File " & Q(PSscriptFile) & " -PrinterName " & Q(printerName) & " -PortName " & Q(portName) & " -DriverName " & Q(driverName) & " -ErrorAction Stop"
    
    Set WShell = CreateObject("WScript.Shell")
    Set WSExec = WShell.Exec(PScommand)
        
    PSoutput = Split(WSExec.StdOut.ReadAll, vbCrLf)
    Debug.Print "StdOut:"
    For i = 0 To UBound(PSoutput)
        Debug.Print PSoutput(i)
    Next
        
    PSerror = Split(WSExec.StdErr.ReadAll, vbCrLf)
    Debug.Print "StdErr:"
    For i = 0 To UBound(PSerror)
        Debug.Print PSerror(i)
    Next

    Kill PSscriptFile
    
End Sub


Private Function Q(text As String) As String
    Q = Chr(34) & text & Chr(34)
End Function