Exporting Data to Excel - Keep existing calculations intact

[jbrown2339]

I've got an Access database, that processes a large dataset, and from there I've been copying the resulting summaries into an Excel spreadsheet, which then creates some reports. The data is broken out, so I am populating around 10 individual sheets in Excel. Each sheet however has a few columns of calculations already existing which do various things (i.e. concatenates some data, counters, etc), which drive the reports (the reports use a series of vlookups).

I've been manually copying and pasting from the access portion into excel, but this is becoming very tedious when I do multiple markets. I've tried using the transferspreadsheet command, but it wipes out those pre-existing calculations in the sheets, even when I specify to start pasting in column B or C, or wherever. Also, after I use the transferspreadsheet command, and then open Excel, it shows me a warning that, "File Error: Some data may have been lost", even though it only populated 12 rows (and wiped out the existing calcs).

The # of rows is variable in the data I'm exporting. Some markets I do this for may only have 20 or 30 rows, others may have over 5,000 rows. So I can't hardcode the range in.

Any idea how I can automatically transfer the data into Excel from Access, without wiping out any pre-existing information in the sheets I'm exporting to? I could just add some macros to the excel, to re-create those calc fields after Access populates, but is there an easier solution I can do through Access?

Thanks!!

 

[PHV]

Instead of pushing the data why not pulling it ?
In Excel, menu Data -> External data -> ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jbrown2339]

I tend to always find the most complicated ways of doing things, when there is a much simpler approach.

Thanks!