TransferSpreadsheet command in a macro

[mathboy23]

I have a simple two step macro that is designed to transfer the information (qryOutput) to an excel spreadsheet (and then tell the user it is done). The first time it was run, it worked like a charm. The second time that I run it, Access gives me an error message that the Table qryOutput already exists. If I delete the Sheet qryOutput in excel and re-run the macro, it works again. I have done a number of TransferSpreadsheet commands and never had this happen before. Any suggestions?

 

[BukHix]

Insert a line in your macro and set the Warnings to false then right after the transfer make sure you turn them back on.

Unfortunately this graphic doesn't show the command in th bottom there but if it did it would say Set Warnings > No and then Set Warnings > Yes.

HTH

 

[mathboy23]

I added a line for SetWarnings=No as step 1 and SetWarnings=Yes as the final step, but I still get the error saying, Table qryOutput already exists. If I delete the Sheet in the Excel file, it will transfer the records as I wish. I don't want to force the user to delete that sheet everyday, but would rather just paste each day's records over the previous day's in the Excel sheet.

 

[BukHix]

Ok maybe I am confused then. Is deleting the spreadsheet an option? If so you could use the shell command to delete the spredsheet before the thansfer is done.