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

kengooch

Member
Local time
Today, 01:47
Joined
Feb 29, 2012
Messages
36
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, 04:47
Joined
Apr 9, 2015
Messages
3,915
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, 04:47
Joined
Feb 19, 2002
Messages
32,787
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, 16:47
Joined
May 7, 2009
Messages
13,693
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
 

Users who are viewing this thread

Top Bottom