MS Access 2010 VBA FileDialog

deer87
deer87 used Ask the Experts™
on
Need Access VBA code for FileDialog to auto assign name of file (I provide name in code).  The filedialog form pops up like it should, but need it to populate the file name field with the name I provide in the code.  How can I make that happen?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
something like this,

Function fSelectFileCSV(myFile)
Dim fd As Object
Set fd = Application.FileDialog(1)
With fd
    .Filters.Clear
    .Filters.Add "Text File", "*.csv; *.txt"

    .AllowMultiSelect = False
   
    'initial path
   .InitialFileName = CurrentProject.Path & "\" & myFile ' change this to your desired default folder
   
    .Title = "Select File"
       
    If .Show = True Then
        fSelectFileCSV = .SelectedItems(1)
       
    End If
End With
End Function

Author

Commented:
Rey, what I am doing is exporting a table to Excel for client.  this is what I am using now:

Public Function saveFileAs(Optional iFilename As String = "") As String
    Dim fd As FileDialog
      Set fd = Application.FileDialog(msoFileDialogSaveAs)
    'Set initial filename
    fd.InitialFileName = iFilename
    If fd.Show = True Then
        If fd.SelectedItems(1) <> vbNullString Then
            saveFileAs = fd.SelectedItems(1)
        End If
    Else
        'Stop Code Execution for Null File String
        End
    End If
    'Cleanup
    Set fd = Nothing
   
End Function

Private Sub cmdExportSelectedHealthSalesBonustoExcel_Click()

    Dim myQueryName As String
    Dim myExportFileName As String
   
        myQueryName = "qdr_RDC_Health_Selected_p"
        myExportFileName = "2015 Health Sales Bonus"
       
    DoCmd.TransferSpreadsheet acExport, 8, myQueryName, saveFileAs, True, "2015 Health Sales Bonus"

End Sub

Will I just replace the savefileas Function with the fSelectFileCSV function you show above.  Currently my spreadsheet is picking up the "2015 Health Sales Bonus" as the name of the spreadsheet but I also want to autofill the file name with myExportFileName in the filedialog form.
Top Expert 2016

Commented:
try

Private Sub cmdExportSelectedHealthSalesBonustoExcel_Click()

    Dim myQueryName As String
    Dim myExportFileName As String
     Dim strSaveFileAs As String
         strSaveFileAs = saveFileAs()
        myQueryName = "qdr_RDC_Health_Selected_p"
        myExportFileName = "2015 Health Sales Bonus"
       
    DoCmd.TransferSpreadsheet acExport, 8, myQueryName, strSaveFileAs, True, "2015 Health Sales Bonus"

End Sub
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
The name of the file is still blank when the filedialog form pops up.
Top Expert 2016
Commented:
pass the name of the file on this line

strSaveFileAs = saveFileAs("myFileName.xls")

Author

Commented:
As always, Rey solves all my problems!!  He is the best!!!!

Author

Commented:
Rey, I need to add a field name  in front of the file name can you tell me how I would do that?  
qdr_RDC_Health_Selected_p.Producer_Name

Private Sub cmdExportSelectedHealthSalesBonustoExcel_Click()

     Dim myQueryName As String
     Dim myExportFileName As String
      Dim strSaveFileAs As String
          strSaveFileAs = saveFileAs()
         myQueryName = "qdr_RDC_Health_Selected_p"
         myExportFileName = "2015 Health Sales Bonus"
       
     DoCmd.TransferSpreadsheet acExport, 8, myQueryName, strSaveFileAs, True, "2015 Health Sales Bonus"

 End Sub
Top Expert 2016

Commented:
pls give more  detailed info..

if the file name is  myexcel.xls,
- how do you want it to look after adding the FieldName?

Author

Commented:
I need the Producer Number and Producer Name fields from qdr_RDC_Health_Selected_p.
These two fields are on a form which the user selects from a drop down list to get his report.  It initially shows as a pdf file.  Because we have a lot of code in the report header the user cannot download into Excel as normally so I added a button where they can download to an excel file using the report query.  Below is how I would like it to show in the Filedialog filename box

111555 John Smith - 2015 Retention Bonus Report.xlsx
Top Expert 2016

Commented:
<These two fields are on a form which the user selects from a drop down >
what are the names of the drop down boxes?
change the names of the combo boxes in the codes below with the actual names of the combo box


Private Sub cmdExportSelectedHealthSalesBonustoExcel_Click()

     Dim myQueryName As String
     Dim myExportFileName As String
     Dim strSaveFileAs As String
     
         myQueryName = "qdr_RDC_Health_Selected_p"
         myExportFileName = Me.ComboProducerNumber & " " & Me.comboProducerName & " - " & "2015 Health Sales Bonus.xls"
         strSaveFileAs = saveFileAs(myExportFileName)  
     DoCmd.TransferSpreadsheet acExport, 8, myQueryName, strSaveFileAs, True, "2015 Health Sales Bonus"

 End Sub

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial