Need to change export VBA to let user select folder location (1 Viewer)

templeowls

Registered User.
Local time
Today, 14:04
Joined
Jun 11, 2019
Messages
156
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
 

templeowls

Registered User.
Local time
Today, 14:04
Joined
Jun 11, 2019
Messages
156
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
 

isladogs

CID VIP
Local time
Today, 22:04
Joined
Jan 14, 2017
Messages
16,085
You haven't defined strFolder in the new code
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
18,792
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
Did it work? If it did, then you're good.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
16,129
the newly picked file is saved in filename variable, while you use strfilename on OutputTo routine.
 

templeowls

Registered User.
Local time
Today, 14:04
Joined
Jun 11, 2019
Messages
156
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:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
16,129
Dim fd As Office.FileDialog

you must add Reference to Microsoft Office XX.X Object library.
 

isladogs

CID VIP
Local time
Today, 22:04
Joined
Jan 14, 2017
Messages
16,085
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
16,129
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
 

templeowls

Registered User.
Local time
Today, 14:04
Joined
Jun 11, 2019
Messages
156
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
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
16,129
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
 

templeowls

Registered User.
Local time
Today, 14:04
Joined
Jun 11, 2019
Messages
156
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
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
18,792
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
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...
 

templeowls

Registered User.
Local time
Today, 14:04
Joined
Jun 11, 2019
Messages
156
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...
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: 305

theDBguy

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
18,792
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?
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?
 

templeowls

Registered User.
Local time
Today, 14:04
Joined
Jun 11, 2019
Messages
156
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
18,792
Any recommendations on how to do that?
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?
 

templeowls

Registered User.
Local time
Today, 14:04
Joined
Jun 11, 2019
Messages
156
How to do what, exactly?
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?
Precisely
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
18,792
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
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.
 

Users who are viewing this thread

Top Bottom