Using Excel VBA saveas function to save as PDF with user interaction

Question

Firstly - sorry if the title is somewhat confusing. I was not really sure how to title this problem.

I have this really simple vba macro which opens the saveas dialog and prefills filename based on a cell value:

Sub SaveAsFunction()

If Sheet1.Range("B17").Value = vbNullString Then
    MsgBox "Cell B17 must not be empty"
    End
End If

Dim fileName As String
fileName = Sheet1.Range("B17").Value

Application.Dialogs(xlDialogSaveAs).Show fileName

End Sub

I've been asked if it's possible to change the "save as type:" field to PDF as the dialog opens but NOT save automatically. The enduser needs to navigate to the correct folder first. I've looked around but haven't found any threads explaining this specific issue.

Thank you.

Answer 1

Try this:

Sub pdf()
Application.Dialogs(xlDialogSaveAs).Show "*.*", 57
End Sub

Answer 2

You cannot accomplish this with the Application.Dialogs method, however, you can use the Application.GetSaveAsFilename method with a file filter:

Function SaveAsPDF() As String
    SaveAsPDF = Application.GetSaveAsFilename(, "PDF Files (*.PDF), *.PDF")
    If (SaveAsPDF = "False") Then SaveAsPDF = "" 'user cancelled.
End Function

This method doesn't actually save anything, so you would have to do something like this:

Dim saveFilePath As String
saveFilePath = SaveAsPDF()

'if user didn't cancel...
If (Len(saveFilePath) > 0) Then
    'use whatever third-party technology you are planning to use to save to PDF.
End If