VBA to APPEND rows to Existing Excel Worksheet

Trying to clean up an Access DB that someone designed to talk back and forth a lot with Excel. At one point, data from several Access tables gets transferred to columns A thru L of an existing spreadsheet; original designer used a whole bunch of convoluted, arrays-within-arrays, loops-within-loops VBA to do that. I have a simple Access query set up that assembles all the appropriate bits and pieces from multiple tables, ready to move over to Excel where some fairly complex calculations can be done more efficiently (plus the client likes to look at that Excel workbook sometimes in addition to what he gets from the Access database).

I've used TransferSpreadsheet at various times, sometimes to create an entire new worksheet on the receiving end, sometimes (if I recall correctly) to overwrite the previous transfer with new data. Situation here is, I need to APPEND rows, essentially adding new date-related data, to what's already there. I can code for that, or actually just plagiarize what the original owner did, but wondering if there's a simple way, with arguments to the TransferSpreadsheet procedure or something else, to tack on another xxx rows without mucking up what's already there. (What's already there, BTW, includes formulae on some rows, so the transfer would not pick those up, they'd have to be "brought forward" somehow.

Ideas welcome, otherwise I'll start coding.

This sounds like something I've done. First of all have you considered "pulling" the data from access using MS-Query instead of "pushing" it from Access?

Secondly I suggest that you use Excel Tables to store the combined data and another table to store the queried in data. Excel tables know how big they are.

Please Login or Register  to view this content.

See this wiki for working with Excel Tables http://www.utteraccess.com/wiki/inde...ables_in_Excel.

Note: MS-Query will bring in current data into an Excel table. You can set it up to do it upon opening the file and it can read parameters (like yesterday's date, for example) from calculated cells in the spreadsheet. Let me know if you are interested in the MS-Query approach and need help.