Using Access in Excel VB Code

[Guest]

Hey Guys.

I was wondering if someone could help me.

Right now, I am working on automating the generation of reports from an
Excel source file. As you could imagine, the Excel file is getting
ridiculuously huge and the cleanup scripts I wrote are taking longer and
longer as more and more records come in.

I have used Access in the past and am porting over the data over there as a
central storage. (This way the information is in a db and it is
easier/quicker to manage.) I already wrote scripts to slice the data as
needed and export it to excel. From here I run the report generation scripts.
So I am going Excel-->Access-->Excel.

My question is: Is there anyway to do all these steps within excel using
VBA? Essentially, how do I open up Access and run the modules I need to using
excel VBA?

For example, I open up a separate sheet with the code, and it will import
the information into Access, run the Access scripts, export to Excel, and run
the Excel scripts. I know how to write code for excel and access separately,
but not together.

I really appreciate any assistance you could provide.

Thanks.

-Michael

 

[Guest]

Since they are both Microsoft products, there is immense amounts of code to
use Office Automation from the Office Suites. I cannot tell you which one is
better to start in, whether Excel or Access. I started in Excel and have
progressively used Access more and more, so I would start in Access, and open
up Excel through Access, run the scripts, run in Access, then report in Excel
since that is the case. Many designers do this since their clients prefer
the look of Excel. I would look into the Knowledge Base to find out more.
VBA is not that different from one to the other, just in the objects you will
be using.