Need to change export VBA to let user select folder location

G

gojets1721

Registered User.
Local time
Today, 06:29
Joined
Jun 11, 2019
Messages
430
I've got the following code that exports a report to a pre-designated folder. It works great so I want to use it for a different project. However, the only change is that the user needs to be able to select their own folder to save. It can't be pre-designated. Since you all helped me with this code, I thought it was easiest to just come back for this small change.

Code: 
Private Sub cmd_exportPDF_Click()
    Dim reportName As String
    Dim criteria As String
    Dim strfolder As String
    Dim strfilename As String
 
    reportName = "CompletedForm"
    criteria = "[ComplaintNumber]= " & [Forms]![frmDetails]![ComplaintNumber]
    strfolder = "F:\documents\"
    strfilename = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.DateOpened, "m.d.yyyy") & ".pdf"
 
    DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, strfolder & strfilename
    DoCmd.Close acReport, reportName, acSaveNo
End Sub
 
Gotcha. What are your thoughts on this code?

Code: 
Private Sub cmd_exportPDF_Click()
    Dim reportName As String
    Dim criteria As String
    Dim strfilename As String
    Dim fd As FileDialog

    reportName = "CompletedForm"
    criteria = "[ComplaintNumber]= " & [Forms]![frmDetails]![ComplaintNumber]
    strfilename = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.DateOpened, "m.d.yyyy") & ".pdf"

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .Filters.Clear
        .Filters.Add "PDFs", "*.pdf"
        .InitialFileName = "C:\Users\Public"
    End With

    fd.Show 'Show file select dialog
    
    fileName = fd.SelectedItems(1)
    
    MsgBox fileName

    DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, strfolder & strfilename
    DoCmd.Close acReport, reportName, acSaveNo
End Sub
 
You haven't defined strFolder in the new code
 
templeowls said:
Gotcha. What are your thoughts on this code?

Code: 
Private Sub cmd_exportPDF_Click()
    Dim reportName As String
    Dim criteria As String
    Dim strfilename As String
    Dim fd As FileDialog

    reportName = "CompletedForm"
    criteria = "[ComplaintNumber]= " & [Forms]![frmDetails]![ComplaintNumber]
    strfilename = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.DateOpened, "m.d.yyyy") & ".pdf"

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .Filters.Clear
        .Filters.Add "PDFs", "*.pdf"
        .InitialFileName = "C:\Users\Public"
    End With

    fd.Show 'Show file select dialog
   
    fileName = fd.SelectedItems(1)
   
    MsgBox fileName

    DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, strfolder & strfilename
    DoCmd.Close acReport, reportName, acSaveNo
End Sub
Click to expand...
Did it work? If it did, then you're good.
 
the newly picked file is saved in filename variable, while you use strfilename on OutputTo routine.
 
So I used the following code:
Code: 
Private Sub btnExportEvent_Click()
    Dim reportName As String
    Dim criteria As String
    Dim fd As FileDialog
 
    reportName = "CompletedForm"
    criteria = "[ComplaintNumber]= " & [Forms]![frmDetails]![ComplaintNumber]

 
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .Filters.Clear
        .Filters.Add "PDFs", "*.pdf"
        .InitialFileName = "C:\Users\Public"
    End With

    fd.Show

    FileName = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.DateOpened, "m.d.yyyy") & ".pdf"

    MsgBox FileName
 
    DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF & FileName
    DoCmd.Close acReport, reportName, acSaveNo
End Sub

I'm getting a compile error on the "Dim fd As FileDialog" line when debugging which says "user-defined type not defined". Full disclosure...I'm very new to VBA so apologies if I'm being stupid
 
Last edited:
Dim fd As Office.FileDialog

you must add Reference to Microsoft Office XX.X Object library.
 
It still won't work once you have added the missing reference.
Previously I mentioned you hadn't defined strFolder so the code won't know which folder to put your PDF file in
You have now removed it from the output to line. Reinstate it and make sure its value its set to the selected folder
 
what is the use of your Filedialog?
you already has a custom filename.
maybe you want to specify which folder to save the pdf?
use msoFileDialogFolderPicker constant.

FileName = Me.PatientLastName & ", " & Me.PatientFirstName & " " & Format(Me.PRDateOpened, "m.d.yyyy") & ".pdf"
If Len(fd.SelectedItems(1)) > 0 Then
FileName =Replace$(fd.SelectedItem(1) & "\", "\\", "\") & Filename
End If
 
arnelgp said:
what is the use of your Filedialog?
you already has a custom filename.
maybe you want to specify which folder to save the pdf?
use msoFileDialogFolderPicker constant.

FileName = Me.PatientLastName & ", " & Me.PatientFirstName & " " & Format(Me.PRDateOpened, "m.d.yyyy") & ".pdf"
If Len(fd.SelectedItems(1)) > 0 Then
FileName =Replace$(fd.SelectedItem(1) & "\", "\\", "\") & Filename
End If
Click to expand...
Yes, essentially I want it to have a custom filename but allow the user to decide where they want to save it. So like this?

Code: 
Private Sub btnExportEvent_Click()
    Dim reportName As String
    Dim criteria As String
    Dim fd As FileDialog
 
    reportName = "CompletedForm"
    criteria = "[ComplaintNumber]= " & [Forms]![frmDetails]![ComplaintNumber]

    FileName = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.DateOpened, "m.d.yyyy") & ".pdf"
    If Len(fd.SelectedItems(1)) > 0 Then
    FileName =Replace$(fd.SelectedItem(1) & "\", "\\", "\") & Filename
 
    DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF & FileName
    DoCmd.Close acReport, reportName, acSaveNo
End Sub
 
Code: 
Private Sub btnExportEvent_Click()
    Dim reportName As String
    Dim criteria As String
    Dim fd As FileDialog
    Dim filename As String
   
    reportName = "CompletedForm"
    criteria = "[ComplaintNumber]= " & [Forms]![frmDetails]![ComplaintNumber]


    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    With fd
        .InitialFileName = "C:\Users\Public"
        .Show
    End With
    Debug.Print fd.SelectedItems(1)
   
    fileName = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.DateOpened, "m.d.yyyy") & ".pdf"
   
    If Len(fd.SelectedItems(1)) <> 0 Then
        filename = Replace$(fd.SelectedItems(1) & "\", "\\", "\") & filename
    End If
    MsgBox filename

    DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, filename
    DoCmd.Close acReport, reportName, acSaveNo
End Sub
 
arnelgp said:
Code: 
Private Sub btnExportEvent_Click()
    Dim reportName As String
    Dim criteria As String
    Dim fd As FileDialog
    Dim filename As String
 
    reportName = "CompletedForm"
    criteria = "[ComplaintNumber]= " & [Forms]![frmDetails]![ComplaintNumber]


    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    With fd
        .InitialFileName = "C:\Users\Public"
        .Show
    End With
    Debug.Print fd.SelectedItems(1)
 
    fileName = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.DateOpened, "m.d.yyyy") & ".pdf"
 
    If Len(fd.SelectedItems(1)) <> 0 Then
        filename = Replace$(fd.SelectedItems(1) & "\", "\\", "\") & filename
    End If
    MsgBox filename

    DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, filename
    DoCmd.Close acReport, reportName, acSaveNo
End Sub
Click to expand...
I'm still getting a compile error on the "Dim fd As FileDialog" line when debugging. It says "user-defined type not defined"

I changed it to "Dim fd As Office.FileDialog" based on your previous suggestion but still same error
 
templeowls said:
I'm still getting a compile error on the "Dim fd As FileDialog" line when debugging. It says "user-defined type not defined"

I changed it to "Dim fd As Office.FileDialog" based on your previous suggestion but still same error
Click to expand...
Hi. Find the following two lines in your code and replace them accordingly.
Code: 
Dim fd As Object

Set fd = Application.FileDialog(4)
Hope that helps...
 
theDBguy said:
Hi. Find the following two lines in your code and replace them accordingly.
Code: 
Dim fd As Object

Set fd = Application.FileDialog(4)
Hope that helps...
Click to expand...
Hi DB, so that did the trick but it led me into another problem....

When the dialog box pops up, the file name is blank (no pre-designated name) and the dialog box is asking me to open a folder. See attached. I added a slash after "public" in the code but that didn't fix it.

Any idea?
 

Attachments

  • Capture.PNG
    Capture.PNG
    8.7 KB · Views: 465
templeowls said:
Hi DB, so that did the trick but it led me into another problem....

When the dialog box pops up, the file name is blank (no pre-designated name) and the dialog box is asking me to open a folder. See attached. I added a slash after "public" in the code but that didn't fix it.

Any idea?
Click to expand...
Yes, that was the point @arnelgp mentioned earlier. He said, why expect to show a filename when you want it to be a specific one anyway. Instead, he suggested to just let the user select the "folder" name instead.

Does that make sense?
 
Ohhh my fault. I missed that.

Essentially these reports are saved in all sorts of file locations. The managers pull them and then save somewhere on their own drive. But the name of the file should be uniform and pre-designated for ease of use. Any recommendations on how to do that?
 
templeowls said:
Any recommendations on how to do that?
Click to expand...
How to do what, exactly? Are you saying you want to give them a predesignated or "default" filename but allow them to change it at the time they're saving/exporting it?
 
How to do what, exactly?
Click to expand...
Suggestions on what to change in the code so that a dialog box appears and has a pre-designated file name and then all the user has to do is select the location of where to save the PDF.

Are you saying you want to give them a predesignated or "default" filename but allow them to change it at the time they're saving/exporting it?
Click to expand...
Precisely
 
templeowls said:
Suggestions on what to change in the code so that a dialog box appears and has a pre-designated file name and then all the user has to do is select the location of where to save the PDF.


Precisely
Click to expand...
Okay, go back to using:
Code: 
    Set fd = Application.FileDialog(2)
    fileName = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.DateOpened, "m.d.yyyy") & ".pdf"
    With fd
        .InitialFileName = "C:\Users\Public\" & fileName
        .Show
    End With
Hope that helps... Feel free to adjust as necessary.
 
You must log in or register to reply here.

Similar threads

A
Report output to filepath variable
Replies
4
Views
595
Antony Cole
A
L
Solved Saving a report to a specified folder
Replies
3
Views
586
Gasman
Gasman
C
New error when saving PDF
Replies
6
Views
853
cricketbird
C
MajP
Load Local PDF into Webbrowser
Replies
7
Views
660
Gasman
Gasman
P
Report Output - Prompt for a Folder Location
2
Replies
22
Views
2,206
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom