Exporting To Excell to run analysis macro interactively

[Guinea]

Hi there,

We have an Excel macro that analyzes excel data which is currently less than 50,000 rows. Are needs are increasing and need to analyze databases of 100,000 rows or more so we need to go to access. How can we use Access to store the data and export chunks say 25,000 to Excel and have the Excel run the macro's that exist, then export the next 25,000...

Would it be easier to change the macro to work in Access directly?

Thanks!
Marc

Guinea pigs make the world a nicer place!

Piggydad30@hotmail.com

 

[GingerR]

Assuming the functionality of the Excel macro can be duplicated in Access, my vote would be to put it in Access. Why flop around between two apps?

What's the Excel macro do?

 

[MichaelRed]

Actually, most of the Excel functions (including the "Analysis" functios can be done directly from Ms. A., simply by including the Excel Lib in Ms. A. (Tools --> References from ANY module). That being said, converting Excel Macros which operate on Cells to VB/A procedures is a less than trivial task, and (at least fo me) often becomes a re-design and re-code to take advantage of the "set" orientated focus of Relational db.

Still, even with the issues of conversion of the excel 'stuff' (Macro or Code?) the operations in Ms. A. should provide a performance improvement to the overall process.

Additional benefits are also POTENTIALLY available, but will require some investment in time and learning. Some of this is going to be automatic and somewhat painful, as Ms. A. WILL force data typing, even when directly adding records to a table and - it will do this on the conversion (i.e. IMPORTING) fro Excel to Access, so you will find some fields of some which are simply not imported at all. Thankfully, Ms. A. will at least let you know that the 'something' weny awry, but you still need to find out what and decide on the 'remedial effort' on an individual record basis. Other benefits can accrue in simplifying the data entry process through the use of Forms and placing limits on the data which can (or MUST) be entered and the use of simple validation procedures and lookups for fields which are restricted to know and limited values.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places