Export to Excel & write back to Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I have a query on Access 2002 that pulls data from 2 tables. I would like to
export the query to an Excel file that's already been designed. Initially I
have about 30K rows of data to export, and after that about 2k rows of data
every week. Users will manipulate and analyze data on Excel, and would like
to write new results back into access (may be in a new field).
1. what's the best way to trasnfer data between Access and Excel (macro,
VBA, etc).
2. since it's a lot of data, how do I maintain the Excel file without having
to delete the old data. (my idea is to import data into a new sheet, and from
that sheet I can feed my predesigned sheets with formulas) but since the
users would like to have a full year worth of data available at all times
before we can archive it, how can I go about executing that without having to
face Excel limitations?
3.What's the best way to write the additional data, that produced into
Excel, back to Access tables?

Please provide as much details as you can.
Thanks.
 
What you are wanting to do would be difficult to achive using the
TransferSpreadsheet method. You could just dump your Access data into a
spreadsheet, but then it would take manual manipulation or VBA macros on the
Excel side.
Another alternative would be to use Automation. It takes a bit of coding,
but you have much better control. One method of the Range object in Excel is
CopyFromRecordset. I find this very useful in this situation. So the basic
concept is:

Create an instance of Excel
Open the Workbook
Determine where you want to copy the data to
Use the CopyFromRecordset to copy the Access data into Excel.

Now, as to pulling the data back into Access from Excel after it has been
manipulated. Being an old time IT person, this, to me, is scarier than
finding a snake in the bed. Spreadsheet data cannot be trusted. That being
said, you should use caution when doing so. If I am forced (usually at
gunpoint) to pull in Excel data, my practice is to link to the worksheet as a
table and scub the data carefully to ensure it has no errors.
 
It is actually done in VBA from Access. I would be more than happy to help;
however, there is so much to it that it is not possible to put it all in a
post. I would suggest you find a book on Automation. You have to learn how
Access deals with an Excel Application object and you have to learn the Excel
object model to be able to do this.

For a quick start, go into VBA Help and look for CreateObject and GetObject.
It will not be nearly enough, but it will give you a start on how to create
an Excel object in your Access Application.
 
Back
Top