Exporting Query Using VBA (1 Viewer)

Hello,

I am trying to export a query into excel using vba, the problem I am having is it is not consistently working. Sometimes the data will be over written, other times the previous data will be present once ran. Not sure what is causing this? At first I thought it might be a timing issue (query was running faster than it could process, so I wrote in "beeps" but this did not seem to help. I also wrote logic to have the query open before exporting but this didn't work either. Furthermore I tried having it export to xls instead of xlsx and this didn't help the consistency issue either. The end goal is to have one spreadsheet that has specific tabs that get overwritten each day with the new data. Below are both of the formulas I have tried:

DoCmd.TransferSpreadsheet acExport, "10", "DAILY__REPORT", "X:\Database_Processing\Processing\Excel_Spreadsheets\NEW_Sheet.xls", True
DoCmd.TransferSpreadsheet acExport, "10", "DAILY_REPORT", "X:\Database_Processing\Processing\Excel_Spreadsheets\NEW_Sheet.xls", True,"DAILY_Report"

It might help if you deleted the old file before exporting the new file. The easiest way would probably be with the kill function. I don't remember how this function reacts if the file is open so you will need to look into that.

mikeh1992 said:

..At first I thought it might be a timing issue (query was running faster than it could process, so I wrote in "beeps" but this did not seem to help...

Click to expand...

Only to clarify: Beeps, pause, loops etc. doesn't help in situation described above, because it gives not the handling back to the operating system, DoEvents does, the same does a breakpoint in the code, but that isn't useful here.