INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Specify where to save PDF file

Specify where to save PDF file

(OP)
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?

CODE

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 

RE: Specify where to save PDF file

Might be clunky, something like this...

CODE

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 

RE: Specify where to save PDF file

(OP)
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

RE: Specify where to save PDF file

Is yourhttp://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.

RE: Specify where to save PDF file

(OP)
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.

RE: Specify where to save PDF file

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.

CODE -->

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

CODE -->

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
'http://answers.microsoft.com/en-us/office/forum/office_2003-customize/vba-example-select-a-directory/f1c57e80-8185-48de-8c03-8bc52770a44e
'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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close