Changing Printer Ports Macro

[burke2950]

Hey everyone, I know this has been asked and answered before but I can't seem to follow and understand the code to fit it for my needs. I'm pretty new in the macro game and I'm usually quick to figure out how things work but this one is stumping me a bit.

I've made a macro that updates the time on my sheet, changes my printer to MS Print to PDF, prints my Excel sheet as a PDF with an autogenerated name based on a cell, then gives a confirmation box. The reason I need to change the printer to MS Print to PDF instead of just saving as a PDF is that my normal printer is a receipt printer and I need a snapshot of the entire sheet instead of just the portion I'm normally printing. The macro works perfectly except for when the printer ports change and I have to go in and edit the new port into the macro. I have a very similar macro to actually print the receipt with the same issue when the ports change.

Sub ExportToPDF()
'
' ExportToPDF Macro
'

'
Calculate
Const MSPDF As String = "Microsoft Print to PDF on NE04:"
Application.ActivePrinter = MSPDF
    ChDir "G:\My Drive\Advance Export\"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "G:\My Drive\Advance Export\" & Sheets("Advance Sheet").Range("F38").Value, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:= _
        False
        MsgBox "Advance Export complete."

End Sub

Any and all help would be greatly appreciated.

 

[burke2950]

After taking a new look at it and trying some more, I think I have it working. I have a few more computers to test on and the other macro to tweak, but I wanted to post an update incase anyone comes across the same issue. The code was taken from another post and tweaked (source: Dynamically change network printer port).

Public Function GetPrinterPort2(strPrinterName As String) As String
    Dim objReg As Object, strRegVal As String, strValue As String
    Const HKEY_CURRENT_USER = &H80000001
    Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    strRegVal = "Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts\"
    objReg.GetStringValue HKEY_CURRENT_USER, strRegVal, strPrinterName, strValue
    GetPrinterPort2 = Mid$(strValue, 10, 5)
End Function
Sub ExportToPDF()
'
' ExportToPDF Macro
'
'
Calculate

Dim strPrinter As String
strPrinter = "Microsoft Print to PDF"
Application.ScreenUpdating = False

Application.ActivePrinter = strPrinter & " on " & GetPrinterPort2(strPrinter)
Application.ScreenUpdating = True
    ChDir "G:\My Drive\Advance Export\"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "G:\My Drive\Advance Export\" & Sheets("Advance Sheet").Range("F38").Value, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:= _
        False
        MsgBox "Advance Export complete."

End Sub