My question: Is there another way to ensure the re-run will delete all rows beyond the header row (in Excel) and then add the new records? If not, how could I ensure that the "Format Painter" is automatically re-applied to the Excel once I ran the Access VBA routine?
1) You might be able to use a template workbook. These allow you to enter data, but FORCE you to do a "Save As" to change the workbook name. You can never overwrite the template.
2) You could use Automation to format the workbook from Access.
3) Have you tried refreshing the workbook (pressing the F5 key)?
It looks like you didn't post the complete code.
However, I have a few suggestions. Try these in a COPY of your dB first!!!
Code:
CurrentDb.Execute MySql_DropRecords
I would use
Code:
CurrentDb.Execute MySql_DropRecords, dbFailOnError
For the queries. Am I right that they are Append queries?
If Yes, then consider using "CurrentDb.Execute" instead of "DoCmd.OpenQuery".
If/when you use "CurrentDb.Execute", you don't need the set warnings commands. When you use "CurrentDb.Execute", be sure to also use ", dbFailOnError" . ("CurrentDb.Execute" is also faster)
So your code would look like
Code:
'Drop Data Cleanup Table
MySql_DropRecords = "DELETE * FROM 002_tblRawData_DataCleanup;" '<<-- note changes
CurrentDb.Execute MySql_DropRecords, dbFailOnError
'Append records to Data Cleanup Table
CurrentDb.Execute "qryDataCleanup_01_IncidentTime_B", dbFailOnError
CurrentDb.Execute "qryDataCleanup_02_PLADName_B", dbFailOnError
CurrentDb.Execute "qryDataCleanup_03_Echelon_B", dbFailOnError
CurrentDb.Execute "qryDataCleanup_04_MajorCommandEch2_B", dbFailOnError
CurrentDb.Execute "qryDataCleanup_05_TYCOMEch3_B", dbFailOnError
CurrentDb.Execute "qryDataCleanup_06_CONUS_B", dbFailOnError
CurrentDb.Execute "qryDataCleanup_07_City_B", dbFailOnError
CurrentDb.Execute "qryDataCleanup_08_State_B", dbFailOnError
CurrentDb.Execute "qryDataCleanup_09_Country_B", dbFailOnError
CurrentDb.Execute "qryDataCleanup_10_LocationDesc_B", dbFailOnError
'Enable warnings
' DoCmd.SetWarnings (True) '<<-- also comment out/delete the previous DoCmd.SetWarnings (False)
Two more things:
It looks like you are using A2010. In the "TransferSpreadsheet" command, you have the SpreadsheetType as "acSpreadsheetTypeExcel12".
"acSpreadsheetTypeExcel12" is the A2007 format. For A2010, you should be using "acSpreadsheetTypeExcel12Xml". This is the format for A2010 - A2016.
Finally, you are Exporting so you shouldn't have anything for the "Range" parameter.
From HELP:
A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
Having said that, it seem that sometimes the export works and sometimes not when the RANGE parameter is used when exporting. BE AWARE.