Specify where to save PDF file

[NeilT123]

I am using Access 2007 and the following code to generate and save a PDF. The PDF is automatically saved in the Sales folder but is there an easy way to open the "file save as"/ Explorer window (sorry not sure what it is called) prepopulated with the me.SaveAs as the file name so that the user can navigate to a different folder if required?

Private Sub btnSavePDF_Click()
On Error GoTo ErrorCode

Dim MyReportName As String
MyReportName = Me.txtReport.Value

DoCmd.OutputTo acOutputReport, MyReportName, "PDF Format (*.pdf)", "" _
& "x:\Sales\" & Me.SaveAs & ".pdf", True

Exit Sub

ErrorCode:
    If Err = 2501 Then Exit Sub                 'ignore error if user cancels print
    Beep
    MsgBox Err.Description

End Sub

 

[sxschech]

Might be clunky, something like this...

Private Sub btnSavePDF_Click()
    Dim FNameExists As Boolean
    Dim yesno

    On Error GoTo ErrorCode

    Dim MyReportName As String
    
    MyReportName = Me.txtReport.Value

    FNameExists = False
FNAME:
    Do While FNameExists = False
        stFileName = InputBox("Enter Name for this Summary Report." & vbCrLf & vbCrLf & _
                              "On the next screen, choose the folder location " & _
                              "for where you want to save the report file.", "Save Report", MyReportName)
        stFileName = Replace(stFileName, "-", "_")
        If stFileName = "" Then
            MsgBox "No name was chosen, or action was cancelled by user.", vbOKOnly, "Missing File Name"
            FNameExists = True
        Else
            stExportPath = selectFolder()
            stExportFileName = stExportPath & "\" & stFileName & ".pdf"
SaveOrReplace:
            If Dir(stExportFileName) = "" Then
                DoCmd.OutputTo acOutputReport, MyReportName, "PDFFormat(*.pdf)", stExportFileName, ShowPdf, "", 0, acExportQualityPrint
                        FNameExists = True
                    Else
                       yesno = MsgBox("File " & stExportFileName & " already exists.  " & vbCrLf & vbCrLf & "Would you like to REPLACE this file?", vbYesNo + vbQuestion, "File Exists")
                        If yesno = vbYes Then
                            DeleteFile (stExportFileName)
                            GoTo SaveOrReplace
                        Else
                            GoTo FNAME
                        End If
                    End If
                    MsgBox "File " & stExportFileName & " is now created", vbOKOnly, "Saved Report"
                End If
            Loop
    End Select

Exit Sub

ErrorCode:
    If Err = 2501 Then Exit Sub                 'ignore error if user cancels print
    Beep
    MsgBox Err.Description

End Sub

 

[NeilT123]

Thank you for the reply. I don't really understand what is going on but I have been able to do what I wanted based on using this

http://www.mvps.org/access/api/api0001.htm

 

[sxschech]

Is your

http://www.tek-tips.com/viewthread.cfm?qid=1768657

response indicating that you didn't understand what the code does or that you were unable to get it to work?

The code I provided tried to fit what you posted into existing code and may need to be edited for your needs. It will display the default name you assign to the report, allows user to choose a different filename, then presents the folder dialog in order to choose what folder to save the file to. It also checks to see if the file already exists and if so, allows user to replace the existing file or to choose a new name or could keep same name and choose a different folder. You can simplify the code if you don't need to provide the user the option of keeping the existing report.

 

[NeilT123]

sxschech, I was able to do what I wanted using the Call the standard Windows File Open/Save dialog box but I didn't really understand how the code was working. I tried your code as I wanted to see how it works. First error message was stFileName didn't exist. I presumed stFileName has to be declared as a string which I did. Then I got SelectFolder sub or function not defined and I ran out of time so haven't taken it any further. I may have another go tomorrow night.

 

[sxschech]

Sorry about that, looks like I didn't highlight that bit when I did the copy paste that off. If you try it and I still didn't copy all you need, let me know and I'll see what else I might have missed.

    Dim stFileName As String
    Dim stExportPath As String
    Dim FNameExists As Boolean
    Dim yesno

Also forgot to give you this. Put it in a standard module (not the form):

Function selectFolder()
'--------------------------------------------------
' File Browse Code and pick a folder
'--------------------------------------------------
'NOTE: To use this code, you must reference
'The Microsoft Office 14.0 (or current version)
'Object Library by clicking menu Tools>References
'Check the box for:
'Microsoft Office 14.0 Object Library in Access 2010
'Microsoft Office 15.0 Object Library in Access 2013
'Click OK
'[URL unfurl="true"]http://answers.microsoft.com/en-us/office/forum/office_2003-customize/vba-example-select-a-directory/f1c57e80-8185-48de-8c03-8bc52770a44e[/URL]
'modified to style of Function selectFile
'--------------------------------------------------
    Dim fd As FileDialog, FolderName As String
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    fd.AllowMultiSelect = False
    fd.Title = "Choose a Folder"
    If fd.Show = True Then
        FolderName = fd.SelectedItems(1)
    End If
    
    'clear file dialog
    Set fd = Nothing
    'Return Folder name and path
    selectFolder = FolderName
End Function