MS Access 2010 VBA FileDialog

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?
deer87Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)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
deer87Author 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.
Rey Obrero (Capricorn1)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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

deer87Author Commented:
The name of the file is still blank when the filedialog form pops up.
Rey Obrero (Capricorn1)Commented:
pass the name of the file on this line

strSaveFileAs = saveFileAs("myFileName.xls")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deer87Author Commented:
As always, Rey solves all my problems!!  He is the best!!!!
deer87Author 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
Rey Obrero (Capricorn1)Commented:
pls give more  detailed info..

if the file name is  myexcel.xls,
- how do you want it to look after adding the FieldName?
deer87Author 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
Rey Obrero (Capricorn1)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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.