Analyze access data in excel - macro?

[L Scal]

Hello,
I need to automate the process of updating and analyzing access data in
excel to produce reports. Is a macro the way to go and does anyone know
where I can find some examples? Or is there a better way?

Thanks

 

[Steve Schapel]

L Scal,

You can certainly use a macro to export data from Access to Excel, and
to import data from Excel into Access. The TransferSpreadsheet macro
action would be applicable. To see how this works, select the Macros
tab in your database window, click New, enter TransferSpreadsheet in the
Action column of the macro design window, and press the F1 key.

As regards "is there a better way", in many cases the better way is to
forget Excel, and update and analyze the data with Access.

 

[L Scal]

Steve,
I like the idea of using access to analyze the data but I'm having a lot of
problems with it. One of which is the error described in KB article
http://support.microsoft.com/default.aspx?scid=kb;en-us;323656&Product=acc2000
Short of using a computer without IE 6.0 and having to buy Access, I tried
the second suggestion which resulted in an Active X runtime error 429. Is
this something I'm going to have to limp through or again, is there a better
way?
LScal

 

[Steve Schapel]

LScal,

Hmmm. I was not aware of the bug you have pointed out. Nor do I know
the reason for the error you are getting when you try to create the
chart manually. Sorry. Maybe your initial idea of exporting the data
to Excel is the best approach.