It's only 27,777 records. The TransferSpreadsheet works but it keeps
overwriting the tabs. What I intend to do is export the spreadsheet with the
previous date as the tab name. So for yesterday records the tab should have
100306 as tab name, 100406 for tomorrow. What I'm trying to accomplish is
import a data file into access each day, work on the data, then the following
day append the current data and export previous day data. Here is the code
Dim varOldName As Variant
Dim varNewName As Variant
Dim varFilename As Variant
Dim varDir As Variant
varDir = Format(DATE, "yyyy")
varFilename = "i:\PRICING\DATABASE\ACCESS 2003\REPORT HISTORY\" & varDir
& "\EXCEPTION" & Format(DATE, "mmyyyy") & ".XLS"
varOldName = Format(DATE - 2, "mmddyy")
varNewName = Format(DATE - 1, "mmddyy")
'DoCmd.OutputTo acOutputQuery, varOldName, acFormatXLS, varFilename -
THis gives me the too many error
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, varOldName,
varFilename, True - This overwrites the current tab
DoCmd.Rename varNewName, acQuery, varOldName - this rename the old query
to todays date and used for the tab name when exporting.