Save an Access Report to a PDF using the Windows File SaveAs Window

[kengooch]

I would like to save an Access report to disk, but each user may save their files in a different folder, so though I create a default name for the file, I would like to open the windows standard SaveAs file dialogue window, paste the created file name as the default and then wait for the user to accept or modify the file or navigate to a different folder.

I've tried various options, here is my last attempt:

Private Sub bSavePDF_Click()
'This Code Saves the file to a PDF
'Set the Default Path and Name
    vPath = "L:\KDG-MPA\CAP\"
    vFileNm = Format(CDate(Date), "yyyy-mm-dd") & tPSKey & " - " & " Proficeincy Survey Review.pdf"
'Open the Windows File SaveAs Dialogue
    Application.Dialogs(xlDialogSaveAs).Show vPath & vFileNm
' Reassing vPath and vFileNm to user selection
'Save the Report     
    DoCmd.OutputTo acOutputReport, "rPSRSurvey", acFormatPDF, tPath & tReportNm, True
End Sub

Thanks in advance!
Ken

 

[theDBguy]

Have you tried using the FileDialog object?

FileDialog object (Office)

Office VBA reference

docs.microsoft.com

 

[Ranman256]

usage:
Dim sFile As String

sFile = UserSaveFileAs("c:\") 'change the default folder

DoCmd.OutputTo acOutputReport, "rPSRSurvey", acFormatPDF, sFile, True


put this SAVE AS in a module

Public Function UserSaveFileAs(Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialogMsg As String, sDecr  As String, sExt As String
'CONST msoFileDialogFilePicker = 3
'CONST msoFileDialogSaveAs = 2
'CONST msoFileDialogViewList = 1

'getFilterTxt pvFilter, sDecr, sExt, sDialog
If IsMissing(pvPath) Then pvPath = getMyDocs()

  'Application.FileDialog (msoFileDialogSaveAs)
With Application.FileDialog(2)   'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
    .AllowMultiSelect = True
    .Title = sDialogMsg   ' "Locate a file to Import"
    .ButtonName = "Save As"
    .InitialFileName = pvPath
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail

        If .show = 0 Then
           'There is a problem
           Exit Function
        End If

    'Save the first file selected
    UserSaveFileAs = Trim(.SelectedItems(1))
End With
End Function

 

[arnelgp]

there is no tPath and tReportNm on your code.
what you have is vPath and vFileNm:

Private Sub bSavePDF_Click()
'This Code Saves the file to a PDF
'Set the Default Path and Name
Dim vPath As string, vFileNm As String
    vPath = "L:\KDG-MPA\CAP\"
    vFileNm = Format(CDate(Date), "yyyy-mm-dd") & tPSKey & " - " & " Proficeincy Survey Review.pdf"
'Open the Windows File SaveAs Dialogue
'Application.Dialogs(xlDialogSaveAs).Show vPath & vFileNm
' Reassing vPath and vFileNm to user selection
'Save the Report
    DoCmd.OutputTo acOutputReport, "rPSRSurvey", acFormatPDF, vPath & vFileNm, True
End Sub

 

[Neo003]

there is no tPath and tReportNm on your code.
what you have is vPath and vFileNm:

Private Sub bSavePDF_Click()
'This Code Saves the file to a PDF
'Set the Default Path and Name
Dim vPath As string, vFileNm As String
    vPath = "L:\KDG-MPA\CAP\"
    vFileNm = Format(CDate(Date), "yyyy-mm-dd") & tPSKey & " - " & " Proficeincy Survey Review.pdf"
'Open the Windows File SaveAs Dialogue
'Application.Dialogs(xlDialogSaveAs).Show vPath & vFileNm
' Reassing vPath and vFileNm to user selection
'Save the Report
    DoCmd.OutputTo acOutputReport, "rPSRSurvey", acFormatPDF, vPath & vFileNm, True
End Sub

Is there a way to open to "This PC" rather than some particular location.

 

[Sharkbyte]

usage:
Dim sFile As String

sFile = UserSaveFileAs("c:\") 'change the default folder

DoCmd.OutputTo acOutputReport, "rPSRSurvey", acFormatPDF, sFile, True


put this SAVE AS in a module

Public Function UserSaveFileAs(Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialogMsg As String, sDecr  As String, sExt As String
'CONST msoFileDialogFilePicker = 3
'CONST msoFileDialogSaveAs = 2
'CONST msoFileDialogViewList = 1

'getFilterTxt pvFilter, sDecr, sExt, sDialog
If IsMissing(pvPath) Then pvPath = getMyDocs()

  'Application.FileDialog (msoFileDialogSaveAs)
With Application.FileDialog(2)   'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
    .AllowMultiSelect = True
    .Title = sDialogMsg   ' "Locate a file to Import"
    .ButtonName = "Save As"
    .InitialFileName = pvPath
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail

        If .show = 0 Then
           'There is a problem
           Exit Function
        End If

    'Save the first file selected
    UserSaveFileAs = Trim(.SelectedItems(1))
End With
End Function

I'm updating a legacy system (~2013 moving to 365), which used the old Save As dialog, which Access no longer supports. I've tried using the code, above, but am running into a couple of issues (see below). Also, if there's a simpler way to just present a file name, and let the user select the location, I'm all for it. Thanks!

Access tells me the 'getMyDocs()' was undefined but commenting it out seems to work.

But I get "Invalid procedure call or argument" for this line... .InitialView = msoFileDialogViewList 'msoFileDialogViewThumbnail

And I'm at a loss. Any ideas???