I would normally do a lot more searching on this matter before posting, but the search functionality seems to still be having problems.
Here's my issue: I need to export the results of a query for which I am generating the SQL in VBA. I would ideally like to export this to an existing Excel file. I am using Access 97 and have discovered that I cannot force it to export to a particular range within an Excel file and that I must put the contents of my query into a table before I can use the TransferSpreadsheet method. This works fine the first time I do it, however, if I re-run, my application gets a fatal error and immediately shuts down. I need to be able to export to the same file over and over. I get the error even after I rename the worksheet tab it exported to after the initial export. It just won't export to the same file more than once.
I have also tried (unsuccessfully) exporting my query to a delimited text file. The problem here is that Access seems to require that my SQL statement already be in a query. The problem with this is that I need to generate the SQL and run it during code execution.
Does anyone have any suggestions on how I can export a query (generated on the fly) to the same file over and over (either overwriting existing contents, or deleting contents first, then adding). I would prefer to go straight to Excel, but can go to a text file if necessary.
Thanks in advance!
Here's my issue: I need to export the results of a query for which I am generating the SQL in VBA. I would ideally like to export this to an existing Excel file. I am using Access 97 and have discovered that I cannot force it to export to a particular range within an Excel file and that I must put the contents of my query into a table before I can use the TransferSpreadsheet method. This works fine the first time I do it, however, if I re-run, my application gets a fatal error and immediately shuts down. I need to be able to export to the same file over and over. I get the error even after I rename the worksheet tab it exported to after the initial export. It just won't export to the same file more than once.
I have also tried (unsuccessfully) exporting my query to a delimited text file. The problem here is that Access seems to require that my SQL statement already be in a query. The problem with this is that I need to generate the SQL and run it during code execution.
Does anyone have any suggestions on how I can export a query (generated on the fly) to the same file over and over (either overwriting existing contents, or deleting contents first, then adding). I would prefer to go straight to Excel, but can go to a text file if necessary.
Thanks in advance!
