Customizing the TransferSpreadsheet Code

[andrewghunt]

Hi, I have the code TransferSpreadsheet running in one of my forms to export to Excel, is it possible to specify the file name and directory you wish to export it to each time, instead of specifying the directory and file name in the Macro?
Thanks

 

[ByteMyzer]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyTableName", "C:\MyFolder\MyExcelFile.xls"

 

[andrewghunt]

Sorry, what I meant was I have that kind of thing now, but I want the user to be able to customize the destination and file name each time they export, without changing the code. Can that be done?

 

[gemma-the-husky]

substitute the bold bits above with variables

then use a file picker etc, or a drop down to pick the variables

 

[ByteMyzer]

[COLOR="Navy"]Dim[/COLOR] objFileDialog [COLOR="navy"]As[/COLOR] FileDialog

[COLOR="navy"]Set[/COLOR] objFileDialog = Application.FileDialog(msoFileDialogSaveAs)

objFileDialog.Show

[COLOR="DarkGreen"]' NOTE: Substitute "MyTableName" with actual table name[/COLOR]
[COLOR="Navy"]If[/COLOR] objFileDialog.SelectedItems.Count = 1 [COLOR="navy"]Then[/COLOR]
    DoCmd.TransferSpreadsheet acExport, _
        acSpreadsheetTypeExcel9, _
        "MyTableName", objFileDialog.SelectedItems(1)
[COLOR="navy"]End If[/COLOR]