MS Access 2010 VBA FileDialog

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

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.

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

The name of the file is still blank when the filedialog form pops up.

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

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

pls give more  detailed info..

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

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

<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