Get location or directly open file after printing to PDF using Workbook.PrintOut() method in VBA Excel

Question

I am writing a Macro that exports the whole workbook into a PDF file.

The process of generation works fine, but now I want to make it open the newly generated file after finish. The thing is that since the filename is NOT predefined I cannot simply open it using it's path and name.

I am using the method PrintOut for the generation with previously selected ActivePrinter to be "Microsoft Print to PDF" or if an error occurss, the user is asked to select it manually using Application.Dialogs(xlDialogPrinterSetup).Show.

Here is a snippet of this part:

Call set_printer

ActiveWorkbook.PrintOut Copies:=1, PrintToFile:=True

... where set_printer() is:

Public Sub set_printer()

    On Error GoTo problem_with_pdf_printer

    Application.ActivePrinter = "Microsoft Print to PDF"
    
Done: Exit Sub
    
problem_with_pdf_printer:

    MsgBox "There is a problem with the Microsoft Print to PDF printer." & Chr(13) & Chr(13) & _
        "Select another one manually!", vbInformation, "Warning!"
    
    Application.Dialogs(xlDialogPrinterSetup).Show
    
End Sub

Is there a way to capture the new file location after execution of the method? Another idea that comes to my mind is a custom "select file location"-dialogue could help me predefine the filepath but sadly I have no idea how this could be implemented.

Thank you very much in advance and I will be really happy for any suggestions and ideas from you!

Answer 1

Converting to pdf seems to be the way to go.

Sub test()
    Dim Wb As Workbook
    Dim fn As String
    
    Set Wb = ThisWorkbook
    
    ChDir ThisWorkbook.Path 'C:\yourpath
    
    fn = "test.pdf"
    fn = Application.GetSaveAsFilename(fn, FileFilter:="PDF Files,*.pdf")
        
    Wb.ExportAsFixedFormat xlTypePDF, fn, OpenAfterPublish:=True
End Sub