Originally Posted by
ssanfu
In the function "Export2Queries()", there is a line "savefile = FilToSave".
If I select "D:\Forum\test.xlsx", the function "FilToSave"returns "D:\Forum\test.xlsx.xlsm" (because of this line: "FilToSave = FilName & ".xlsm").
Back in the function "Export2Queries(), the first DoCmd.TransferSpreadsheet statement tries to execute and an error occurs because there is no such file.
Further testing:
if the extension is ".xlsx", executing the statement "DoCmd.TransferSpreadsheet" will create the Excel file and export the data to the file.
if the extension is ".xlsm", executing the statement "DoCmd.TransferSpreadsheet" will FAIL if the file does not currently exist.
if the extension is ".xlsm", executing the statement "DoCmd.TransferSpreadsheet" will SUCCEED if the file exists BEFORE the function "Export2Queries()" is executed.