I am attempting to pull a large amount of data from a database, filter it, pull it to Excel Workbooks, and format it for distribution.
I am storing the data in a DAO.Recordset as strings. The formatting involves Excel.ListObjects, updating data types, and grouping columns. Because the formatting requires many calls to Excel, it takes a bit of time.
I would like to reduce the time it takes to run the macro by reusing the same workbook for each distribution, SavingAs, deleting/clearing old data and inserting new.
Data is pulled to the first spreadsheet with Range.CopyFromRecordset and then formatted and distributed without issue.
The problem seems to be in deleting/clearing the previous set of data and inserting the new. I've tried quite a few methods involving deleting or clearing contents from the DataBodyRange and resizing/inserting or deleting rows, etc. but none of them completely work. The best I've been able to do so far is getting one row of new data to print.
Is it possible to use CopyFromRecordset with an existing ListObject; if so, how might that be done?
Before copying from a recordset, you should generally lead with .MoveLast and then .MoveFirst, to make sure the entire recordset is loaded, and copying starts at the first row.
.CopyFromRecordset always starts at the current record, which is not necessarily the first.