Set to default printer

[brianfosterblack]

Excel 2007

I am using this line of code to print to pdf
Application.ActivePrinter = "Nitro PDF Creator 2 on Ne08:"
How do set the worksheet back to the default printer.

I am distributing the workbook so I need it to reset to the default printer for the computer being used - not mine.

 

[daniels012]

Can you use the same formula only for the other Printer?

Mine would be
Application.ActivePrinter = "HP Photosmart C309a series:"


Just add this as a last bit of code.
MIchael

 

[brianfosterblack]

Thanks Daniel but that won't help.

I will not know the default printer on the other computers using the workbook so i need code that can determine the default printer and then set it to that

 

[daniels012]

Ok here:
Sub FindPrinter()
Dim strPName As String
strPName = Application.ActivePrinter
'******Finds the current printer name ********

Application.ActivePrinter = "Nitro PDF Creator 2 on Ne08:"
***** Does your print job *******

Application.ActivePrinter=strPName
**** Then resets the printer to it's default*****
End Sub

 

[brianfosterblack]

Daniel,

Thank you so much. This works like a charm

 

[daniels012]

You are more than welcome!

 

[brianfosterblack]

Daniel,

You very kindly helped me with this code but I have now encountered a problem

Sub PrintPersonalPdf()
Dim strPName As String
strPName = Application.ActivePrinter
Application.ActivePrinter = "Nitro PDF Creator 2 on Ne08:"
Application.Run "PrintPersonalFax"
Application.ActivePrinter = strPName
End Sub

As soon as I remove my laptop from my network the code does not work. When I checked the Nitro printer it now shows as "Nitro PDF Creator 2 on Ne04:" Has this got something to do with the port. If so I am going to have the same problem with anyone using the workbook. Is there a way in the code to check if Nitro professional is in fact loaded on the computer using my workbook and if so, changing the code to include the correct settings in order to print to Nitro Professional?

 

[daniels012]

You can have a button on the page. When pressed it would tell you what is the current printer.
Not sure what the issue is? What line does it give you an error?

Something like:
Dim strPName As String
MsgBox "My Current Printer is " & strPName

 

[brianfosterblack]

Daniel,

I have been holding my breath and hoping you woould reply to this dated string.

The system works perfectly to pick up the user's current default printer and after resetting the printer to print to the Nitro pdf printer and doing the print and then resetting back to the default printer. This is what I need.

the problem now appears that to reset to the nitor pdf printer the code needs to determine what port this printer is on. Whether that is the reason for the Ne08 and Ne04 i am not sure.

What i need my code to automatically do is identify the correct setting for the pdf printer on that computer and change the pdf print instruction accordingly. It is no good giving the user a message as they will not know what to do with it.

 

[Leith Ross]

Hello brianfosterblack,

This UDF will return the printer name with the port number. Place this code in a standard VBA module.

Function GetPrinterWithPort(ByVal PrinterName As String) As String

    Dim Port As Variant
    Dim WSH As Object
    
        Set WSH = CreateObject("WScript.Shell")
            On Error Resume Next
            Port = WSH.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts\" & PrinterName)
            If Err = 0 Then
                Port = Split(Port, ",")(1)
                GetPrinterWithPort = PrinterName & " on " & Port
            End If
            On Error GoTo 0
    
End Function

Example of Using the Macro

Sub PrintPersonalPdf()
    Dim strPName As String
        strPName = Application.ActivePrinter
        Application.ActivePrinter = GetPrinterWithPort("Nitro PDF Creator 2")
        Application.Run "PrintPersonalFax"
        Application.ActivePrinter = strPName
End Sub