Save an Access Report to a PDF using the Windows File SaveAs Window (1 Viewer)

kengooch

Member
Local time
Today, 07:41
Joined
Feb 29, 2012
Messages
137
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:

SQL:
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
 

Ranman256

Well-known member
Local time
Today, 10:41
Joined
Apr 9, 2015
Messages
4,339
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

Code:
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:41
Joined
Feb 19, 2002
Messages
42,970
You could create a table where each user can define his preferred save directory. Then depending on the user who is logged in, you would save to the designated folder. Or, have the user use the file dialog or a combination of both. Use the path in the table and use that as the starting point for the save dialog.

Here's two procedures using the File dialog. One opens to a directory and the other to a file
Code:
Public Function fChooseFile()

   ' Requires reference to Microsoft Office 11.0 Object Library.

   Dim fDialog As Office.FileDialog
   Dim varFile As Variant


   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

   With fDialog

      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
           
      ' Set the title of the dialog box.
      .Title = "Please select one file"

      'starting location
      .InitialFileName = CurrentProject.path
     
      ' Clear out the current filters, and add our own.
      .Filters.Clear
''''      .Filters.Add "Excel ", "*.XLSX"
      .Filters.Add "Access Databases", "*.ACCDB, *.MDB"
''''      .Filters.Add "Access Projects", "*.ADP"
      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
            fChooseFile = varFile
''''            Me.FileList.AddItem varFile
         Next
       
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

End Function


''https://msdn.microsoft.com/en-us/library/hww8txat%28v=vs.84%29.aspx?f=255&MSPPError=-2147217396
''https://www.exceltrick.com/formulas_macros/filesystemobject-in-vba/
''https://bettersolutions.com/vba/files-directories/file-system-object.htm
''http://www.xl-central.com/list-files-fso.html
''http://www.thevbprogrammer.com/ch06/06-09-fso.htm


Public Function fChooseDirectory()

    ' requires a reference to the Office xx Object library
    'Declare a variable as a FileDialog object.
    'Dim fd As FileDialog

   '''' Const msoFileDialogFolderPicker = 4 'use for late binding
   
    Dim fd As Object
   
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.

                'Only one item will be returned since the file dialog is a folder picker
                'MsgBox "The path is: " & vrtSelectedItem
                fChooseDirectory = vrtSelectedItem
                Exit Function
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing
    fChooseDirectory = "Error - nothing chosen"
End Function
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:41
Joined
May 7, 2009
Messages
19,169
there is no tPath and tReportNm on your code.
what you have is vPath and vFileNm:
Code:
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

New member
Local time
Today, 09:41
Joined
Dec 18, 2021
Messages
19
there is no tPath and tReportNm on your code.
what you have is vPath and vFileNm:
Code:
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:41
Joined
Feb 19, 2002
Messages
42,970
What folder does "this PC" refer to? You really don't want to save the files to the root folder and probably also not to the MyDocuments folder that is like the well of lost soles folder. You could use the folder the FE is stored in but I would prefer to use a named folder that is a child of the FE folder.

Tell us what you want but be specific. If you control the folder that holds the FE, you can easily create a subfolder for the documents. My suggestion was to let the user define his preferred folder. But you can certainly hardcode the folder names in the app. There is no reason that they cannot be the same for each user. I.e.

C:\myAccessapp\mydocuments
 

Sharkbyte

New member
Local time
Today, 10:41
Joined
Jan 5, 2023
Messages
3
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

Code:
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???
 

Users who are viewing this thread

Top Bottom