Quote Originally Posted by ssanfu View Post
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.
So if I'm understanding you correctly (and my own tests have shown the same i believe). You can only do a transferspreadsheet into an existing .xlsm file, not a new one.

Changing this bit "FilToSave = FilName & ".xlsm", won't change that as far as i can see?

Now that kinda gives me two questions:
Firstly is why can't it create an .xlsm file when it can create an .xlsx (if I change FilName & ".xlsm" to "xlsx").
Secondly if I do a transferspreadsheet into an existing .xlsm file, will it overwrite the existing sheets if they have the same names? (I hope that they will).

Thanks for all your support and time Steve.