Exporting one tables to one excel with user defined path.

[millertimecu32]

All,
I am struggling hear novice "coder". I have pulled together some code that helps export many tables into one workbook in excel. see below. I would like to have it set up so that the user can select the path and file name for the export but do not know how to do it. Also for some reason when run the script i get an error in excel that says the file is in a different format than the file extension. Any help would be greatly appreciated.

Private Sub Command9_Click()
strPath = "C:\Documents and Settings\bills\My Documents\container\ken.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dt_task", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dt_subfacility", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dt_location", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dt_location_parameter", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dt_waterlevel", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dt_equipment", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dt_purge", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dt_field_sample", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dt_sample_parameter", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dt_person", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dt_equipment_param", strPath


End Sub

 

[dragofly]

Try not specifying the spreadsheet type and just use the default. Also you may want to experiment with leaving off the extension all together and see if the api is smart enough.

For the user defined path check out the filedialog object. If the hlp file isn't helpful enough try a google and I gaurantee you'll find something useful. That's how I found it.