Report Output - Prompt for a Folder Location

[PSSMargaret]

The below code is on a command button and it works fine saving the report to my Documents folder. Can this be tweaked to open the File Save As window and prompt the user for a folder location?

Dim strReport As String
    Dim strWhere As String
    Dim strFileName As String
    Dim strFolder As String

    strReport = "rptInvoice"
    strWhere = "[ClientID]=" & Me.ClientID
    strFileName = "Invoice " & Me.InvoiceNo & ".pdf"
    
    DoCmd.OpenReport strReport, acViewPreview, , strWhere, acWindowNormal
    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, False
    DoCmd.Close acReport, strReport

 

[The_Doc_Man]

Look at the INPUTBOX function.

InputBox function (Visual Basic for Applications)

VBA language reference

learn.microsoft.com

You would use the returned value from the input box to modify the strFileName parameter in your DoCmd.OutputTo method.

OR use the FileSystemObject to trigger a full-blown file dialog box.

VBA Save File Dialog, FileDialog(msoFileDialogSaveAs) - VBA and VB.Net Tutorials, Education and Programming Services

There may be times when you need to ask the user to select a location to save a file. This can be done using the save file dialog. Keep in mind that the save file dialogs doesn’t actually save anything. It only returns the full

software-solutions-online.com

Again, modify strFileName when done.

 

[PSSMargaret]

The File Dialog along with the existing code did the trick and I learned something new. I also figured out how to include the file name in the File Dialog window. Thanks so much.

Dim strReport As String
    Dim strWhere As String
    Dim strFileName As String
    Dim strFolder As String
    Dim intChoice As Integer
    Dim strPath As String
    
    strReport = "rptInvoice"
    strWhere = "[ClientID]=" & Me.ClientID
    strFileName = "Invoice " & Me.InvoiceNo & ".pdf"
    
    DoCmd.OpenReport strReport, acViewPreview, , strWhere, acWindowNormal
    
    'Insert the file name in the File Dialog window
    Application.FileDialog(msoFileDialogSaveAs).InitialFileName = strFileName
    'make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogSaveAs).Show
    
    'determine what choice the user made
    If intChoice <> 0 Then
        'get the file path selected by the user
        strPath = _
            Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1)
        'displays the result in a message box
    'Call MsgBox(strPath, vbInformation, "Save Path")
    
    Else
        DoCmd.Close acReport, strReport
        Exit Sub
    End If
    
    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, False
    DoCmd.Close acReport, strReport

 

[arnelgp]

FSO is a very useful library.

 

[autumnnew]

The File Dialog along with the existing code did the trick and I learned something new. I also figured out how to include the file name in the File Dialog window. Thanks so much.

Thanks for posting this! I was looking for the same solution, so I was able to get this to work in my database.

However, this doesn't work if a user changes the file name (intentionally or not) in the dialog window. If a user clicks on a file, the InitialFileName changes. How can you set the changed file name to strFileName? I tried strFileName = Application.FileDialog(msoFileDialogSaveAs).InitialFileName before the OutputTo line, but that didn't work.

 

[arnelgp]

you can create a Function that create an instance of the Filedialog:

Public Function FileSaveAs() As FileDialog
Static fd As FileDialog
If fd Is Nothing Then
    Set fd = Application.FileDialog(msoFileDialogSaveAs)
End If
Set FileSaveAs = fd
End Function



Private Sub T()
Dim defFile As String
Dim filename As String
'this is the default filename
defFile = Me!InvoiceNo & ".pdf"
'get default filename from the FileDialog
filename = FileSaveAs.InitialFileName
'check if there is a Default in the Filedialog
If InStrRev(filename, "\") = Len(filename) Then
    'there is no default filename saved in filedialog
    'so add our default
    filename = filename & defFile
    'put the to filedialog as InitialFileName
    FileSaveAs.InitialFileName = filename
End If
DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, filename
End Sub

 

[autumnnew]

Try using the unaltered code I posted.

I did, and it works perfectly as long as I leave the file name intact after the Save As dialog window has opened.
However, it didn't work when I clicked on another pdf to save over. While I'm testing my reports, I often just save over the same pdf, because it's pinned to my taskbar and quickly accessible to open for reviewing. (I don't have the feature of automatically opening PDFs after printing to PDF)

If a user clicks on another file, the file name in the dialog box changes, which means it becomes different from the string name in the procedure. Most users for my db won't need to change the file name, but if they [inadvertently] click on a file, the procedure needs to accomodate for that. I don't see that it does.. am I missing it?

 

[autumnnew]

you can create a Function that create an instance of the Filedialog:

I also tried your solution, but it doesn't prompt me for where to save it. It just automatically exports it to my documents folder. I need it to let the user choose where to save the pdf.

 

[autumnnew]

Your code need to handle the save over either by deleting the old file first or by trapping the error after.

Can you or someone help me with this? I don't know how to do that.

My purpose of using this code is to generate a file name for this report when users export it. Sometimes users can forget which project they're working with and save the report under the wrong project number. Adding this code would prevent user errors. This is the only code I've found so far that I can get to 1) pre-define the file name and 2) prompt users where to export and save a report as a PDF.

Now the issue is when the user has changed the file name in the dialog box (whether accidentally or intentionally) if the user wants to name it differently or save over another file with a different name.

In this case, it's okay if the code overwrites files of the same name, and using a version-appending convention won't be necessary, but I would like the code to also let users change the file name or overwrite a different file if they want. This code doesn't do that.

The users will be exporting a work order, so I modified the code to set the file name as the project number and work order number, with the words 'Work Order' at the end. It'll be okay if they are overwriting pre-existing PDFs. The users are my former co-workers and I made this database for my former job, and this is one of the upgraded features that I'm finally adding that I never got to finish.

 

[arnelgp]

you need to call FileSaveAs again:

Private Sub T()
Dim defFile As String
Dim filename As String
'this is the default filename
defFile = "1234" & ".pdf"
'get default filename from the FileDialog
filename = FileSaveAs.InitialFileName
'check if there is a Default in the Filedialog
If InStrRev(filename, "\") = Len(filename) Then
    'there is no default filename saved in filedialog
    'so add our default
    filename = filename & defFile
    'put the to filedialog as InitialFileName
    FileSaveAs.InitialFileName = filename
End If
If FileSaveAs.Show Then
    DoCmd.OutputTo acOutputReport, "yourReport", acFormatPDF, filename
End If
End Sub

Public Function FileSaveAs() As FileDialog
Static fd As FileDialog
If fd Is Nothing Then
    Set fd = Application.FileDialog(msoFileDialogSaveAs)
End If
Set FileSaveAs = fd
End Function

 

[autumnnew]

you need to call FileSaveAs again:

Thanks! That also works now, just as well as Pat Harman's solution, but I'm still not able to save with a different file name other than the one initially generated by the code.

 

[autumnnew]

Try using the unaltered code I posted.

I always seem to be derping on this forum.

I just came back here to review the post, and I just realized I used @PSSMargaret's code, not yours. *facepalm* I forgot there was another solution offered once I saw Pssmargaret's code and tried it. This whole time I was thinking it was your code that I used.

I'll try yours now.

 

[autumnnew]

Let us know what happens.

I removed the me.txtdirectory, since I don't need to define the directory or path where the file is being saved... the user will browse to the location. (respective project folder)

So I just put the fChooseFile function in the onclick event for the button to run the SaveAs dialog, and pass the generated filename to the function. I changed the type of dialog box from FilePicker to SaveAs, since I don't need to choose files.. I'm just outputting 1 report to a pdf file (saving).

Then I noticed I'd have to add the OutPut to command in order to export the pdf, and I stopped there because then this code ends up doing the same thing that arnelgp's and pssmargaret's code does, up until the user makes a different decision.

User clicks a button to pdf a report.
The dialog box opens with a suggested filename already generated.
User can save with that file name (that's the intent of the code)

oops, User intentionally or accidentally changes the file name. Now the code won't work.

If the filename in the dialog box changes for whatever reason, how can I capture the changed filename? This is the code I need but I don't know how to write. I've never written code that utilizes file dialog windows.

 

[autumnnew]

Okay, so you can give the user the control of where the pdf gets saved, without letting them think they're naming the file. I'm okay with that. I didn't know that was a possibility.

So I added the OutputTo statement and I'm using just your fChooseDirectory function to choose where the pdf gets saved, but it's not saving where I choose. It always gets saved where the database is saved, which is my desktop. Is it because I don't have the me.txtdirectory control? I never understood what the purpose of txtdirectory was for.

 

[autumnnew]

EUREKA, I finally got it using PSSMargaret's code!!! I decided to take a break from this issue and revisit this later, to work on other parts of the db. Every new feature I add takes a ton of time and research, questioning the forums, trial and error, testing, etc etc. and it's exhausting.

Firstly, I think you might have my post mixed up with another. I'm not working with any invoices in my db; I previously stated that I'm exporting Work Orders and that I wanted to export the PDF file with the right Work Order number in the file name, for the sake of preventing potential mistakes made by complacent users. However, I also wanted to give the users the freedom to choose to name the file something else in the unlikely event that they need to. The report is a simple one and the export procedure is just what you would expect in any other program: click a button to export a report, and the file name is already generated in the filename box. MOST users would accept the file name and choose where to save. But some users might prefer to save with another name and that's okay too, I just didn't know how to provide that ability.

Below is the code I went with. Using PSSMargaret's code, I defined a new variable Dim fso As New FileSystemObject and added the line strFileName = fso.GetFileName(Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1)) to get the file name from path set by the user. I also had to turn on a new reference in VBA editor (Microsoft Scripting Runtime) in order for this to work.

Here is the link where I found this method: https://stackoverflow.com/questions/25006283/get-only-the-file-name-from-ms-access-file-browse

Private Sub btnPDFWorkOrder_Click()
    Dim strReport As String
    Dim strWhere As String
    Dim strFileName As String
    Dim intChoice As Integer
    Dim fso As New FileSystemObject

    strReport = "rptWorkOrder"
    strWhere = filterByTmpWorkOrder    'this is a global variable defined when the db opens.
    strFileName = Me.[Project Number] & "-" & Me.[Work Order Number] & " Work Order" & ".pdf"

    DoCmd.OpenReport strReport, acViewPreview, , strWhere, acHidden

   'Insert the file name in the File Dialog window
    Application.FileDialog(msoFileDialogSaveAs).InitialFileName = strFileName

   'make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogSaveAs).Show

   'determine what choice the user made
    If intChoice <> 0 Then
        strFileName = fso.GetFileName(Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1))
    Else
        DoCmd.Close acReport, strReport
        Exit Sub
    End If

    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, False
    DoCmd.Close acReport, strReport
End Sub