Excel VBA Macro printpreview default directory and generated file name

[truerock]

I'm using Microsoft Excel for Microsoft 365 MSO (16.0.13929.20222) 64-bit
On Windows 10 Pro 20H2 build 19042.928
On a desktop PC using an Intel CPU on an Intel system board

The workflow requires the user to visually check an Excel print preview before creating a "Microsoft Print to PDF" PDF file and
to visually double check the storage directory and PDF file name before saving the PDF file.

When the Excel Macro below is run, a print preview is displayed... providing the user an opportunity to visually check the print out before it actually generates a PDF printout to be stored in the appropriate storage directory with the correct file name.
If "Print" is clicked on, the "Save Print Output As" dialog box is displayed.
Unfortunately, this displays the directory of where the last (previous) Excel "Microsoft Print to PDF" printer file was saved - not, where the file should actually be saved.

The correct directory name to save the PDF print file in is something like E:\LOC567 in this example and the correct PDF file name is something like FN763.PDF.

Sub Macro_y()
Excel.Application.ActivePrinter = "Microsoft Print to PDF on Ne00:"
Excel.Application.ActiveSheet.Range(Cells(1, 1), Cells(25, H)).Select
Excel.Application.Selection.PrintPreview EnableChanges:=True
End Sub

How can the "Save Print Output As" dialog box be set to be at the correct storage directory (e.g. E:\LOC567) and also with the file name box filled in with the correct PDF file name (e,g, FN763.PDF)?

 

[johnnyL]

@truerock. this should assist you:

Sub truerock1()
'
'   This currently will save the worksheet named "Sheet1" as a PDF file with a file name (user changeable) to a constant directory
'
    Dim PDFfileName As String
'
    DefaultSaveFileName = "FN763"   ' <--- Set the Default PDF file name here
    PDFfileName = InputBox("Please enter a name for the PDF file to be saved", Default:=DefaultSaveFileName)
    If PDFfileName = "" Then Exit Sub
'
    Application.ScreenUpdating = False
'
    FilePath = "E:\LOC567"    ' <--- Set the location that you always want to save the PDF file to on this line, This location must already exist ;)
    ChDir FilePath
'
'   Set the \/ \/ \/ Sheet that you want to save as a PDF file below
    Sheets("Sheet1").ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFfileName & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
'
    MsgBox PDFfileName & ".pdf has been saved to " & FilePath
'
    Application.ScreenUpdating = True
End Sub

 

[truerock]

@truerock. this should assist you:

Sub truerock1()
'
'   This currently will save the worksheet named "Sheet1" as a PDF file with a file name (user changeable) to a constant directory
'
    Dim PDFfileName As String
'
    DefaultSaveFileName = "FN763"   ' <--- Set the Default PDF file name here
    PDFfileName = InputBox("Please enter a name for the PDF file to be saved", Default:=DefaultSaveFileName)
    If PDFfileName = "" Then Exit Sub
'
    Application.ScreenUpdating = False
'
    FilePath = "E:\LOC567"    ' <--- Set the location that you always want to save the PDF file to on this line, This location must already exist ;)
    ChDir FilePath
'
'   Set the \/ \/ \/ Sheet that you want to save as a PDF file below
    Sheets("Sheet1").ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFfileName & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
'
    MsgBox PDFfileName & ".pdf has been saved to " & FilePath
'
    Application.ScreenUpdating = True
End Sub

johnnyL,

Thank you for your suggestion. Our workflow requires a "print preview" before the saved PDF file is generated.
Our preferred solution is to use
Excel.Application.Selection.PrintPreview
and then have the user click on the "Print" button of the "print preview" dialog box.
Our Excel macro is able to generate the path of the directory to be used and the name of the PDF file. No user input is required for that information.

The "Microsoft Print to PDF" driver is able to save the last directory used to save a PDF file for each individual application that uses it. We do not know how to change that saved information and we would like yo now how to do that.

Thank you

 

[johnnyL]

Sorry @truerock, the code I provided allows your suggested name ("FN763") of the file to be default/ user changeable, in addition you requested an alternate location to automatically save the pdf file to ("E:\LOC567"). I think the code I provided does that.