Export Report as PDF, Set Name bu Allow User to Choose Location

[Damo1412]

Hi,

I am running an Access 2013 database where the user can export certain reports as a PDF. I would like to set the name of the PDF to a "user friendly" name such as "Additional Works Summary" instead of the report name of "RClientAdditionalWorksSummaryV2" but still allow the user to chose where the report is to be saved.

I currently use the command:

DoCmd.OutputTo acOutputReport, "RClientAdditionalWorksSummaryV2", acFormatPDF,

This allows the user to select the save location but defaults the filename to "RClientAdditionalWorksSummaryV2".


I can amend the command to:

DoCmd.OutputTo acOutputReport, "Blank OurAllInAuditCurrentJob", acFormatPDF, "c:\Location\Additional Works Summary.pdf"

This will change the filename but it also sets the save location


If I change it to:

DoCmd.OutputTo acOutputReport, "Blank OurAllInAuditCurrentJob",  acFormatPDF, "Additional Works Summary.pdf"

this will change the filename but automatically saves the file to the default directory (usually My Documents)


Any help would be greatly appreciated

 

[Ranman256]

Public Sub ExportFile()
dim vFile

vFile = UserSaveFile("c:\library\")
if vFile <>"" then DoCmd.OutputTo acOutputReport, "Blank OurAllInAuditCurrentJob", acFormatPDF, vFile
End Sub



Public Function UserSaveFile(Optional pvPath)
Dim strTable As String
Dim strfilepath As String
Dim sDialog As String, sDecr  As String, sExt As String

If IsMissing(pvPath) Then pvPath = "c:\"

  'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
With Application.FileDialog(msoFileDialogSaveAs)
    .AllowMultiSelect = False
    .Title = "Save As"
    .ButtonName = "Save"
    '.Filters.Clear
    '.Filters.Add sDecr, sExt
    .InitialFileName = pvPath
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail

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

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

 

[Damo1412]

Thanks for the help. You have laid that out so that it is very clear and easy to follow