Report Output - Prompt for a Folder Location (1 Viewer)

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

Office VBA reference topic

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.

 

[Pat Hartman]

I like to add the path location as a control on the form. That way I can call up the file manager dialog. Here are two useful pieces of code. One lets you pick a folder, the other lets you pick a file. Add a button to the form and in the click event of the form use one line of code --

Me.txtDirectory = fChooseDirectory()

Public Function fChooseFile()
 
   ' Requires reference to Microsoft Office 11.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
 
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
   With fDialog
 
      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
            
      ' Set the title of the dialog box.
      .Title = "Please select one file"
 
      'starting location
      .InitialFileName = CurrentProject.path
      
      ' Clear out the current filters, and add our own.
      .Filters.Clear
''''      .Filters.Add "Excel ", "*.XLSX"
      .Filters.Add "Access Databases", "*.ACCDB, *.MDB"
''''      .Filters.Add "Access Projects", "*.ADP"
      .Filters.Add "All Files", "*.*"
 
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
            fChooseFile = varFile
''''            Me.FileList.AddItem varFile
         Next
        
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

End Function




Public Function fChooseDirectory()

    ' requires a reference to the Office xx Object library
    'Declare a variable as a FileDialog object.
    'Dim fd As FileDialog

   '''' Const msoFileDialogFolderPicker = 4 'use for late binding
    
    Dim fd As Object
    
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.

                'Only one item will be returned since the file dialog is a folder picker
                'MsgBox "The path is: " & vrtSelectedItem
                fChooseDirectory = vrtSelectedItem
                Exit Function
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing
    fChooseDirectory = "Error - nothing chosen"
End Function

 

[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

 

[Pat Hartman]

You're welcome. FSO is a very useful library.

 

[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

 

[Pat Hartman]

However, this doesn't work if a user changes the file name (intentionally or not) in the dialog window.

Try using the unaltered code I posted.

 

[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.

 

[Pat Hartman]

The purpose of the code I posted is to select a file (existing) or to select a folder. I always concatenate the file name to the folder name making sure the folder name ends with a slash. Neither piece of code has anything to do with saving/importin a file. Your code need to handle the save over either by deleting the old file first or by trapping the error after. I generally delete the old file first mostly because I concatenate a date to the file name and so if the name is repeated, it is extremely likely that the user exported the file again and wants the newest version. Know your users and how they want to work.

 

[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.

 

[Pat Hartman]

Let us know what happens.

 

[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.

 

[Pat Hartman]

You can't use the file dialog to save the file. It is the Outputto that will save the file. You use the file dialog to pick the folder. The application generates the file name, not the user.

1. User clicks button to export pdf
2. Open file dialog to collect folder name
3. Create full file name
4. OutputTo using full file name.

 

[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.