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"
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"