How to use VBA to automate several Office applications?

Question

Although I've done VBA projects within a single application for both MS Access 2007 and Excel 2007, I haven't automated multiple applications at the same time. The generalized project is to open access, run some update queries that appends data to various tables. Then Excel needs to get the data. Some formating changes are needed in Excel, such as grouping that doesn't automatically change the date range. Finally, I plan to build it out such that the excel file will be emailed automatically.

Some parts of this are clear how to accomplish it, such as Excel will be getting the data by ODBC from Access. But where should the master VBA live? I could have a button in Access that would start running Access VBA, but is it a good practice to have the Access VBA start manipulating Excel? Does that make it difficult to debug?

Answer 1

You may put the code in Access, or Excel. In my experience, that is easier to work with than splitting the code between the two (which also works).

If you find that the automation code runs too slowly, you can split the code, so that Access is run by functions in Access, and Excel from functions in Excel, and the master code just runs routines in both. In this case, you can put the master code where-ever you get the nicest user interface for starting things. I've used C,Access,Word and Excel, and where you put the start button doesn't matter a whole lot if all the code is somewhere else.

If your users or maintainers are more familiar with one application, you may wish to put your main or master code there, but more often I find it is better to put the main or master code with the guts of the application, so that I can have no code at all in the other partner.

From the brief description, it sounds like more work will be done in Excel, so I would put all the code there.

Pulling the data out of Access doesn't even need an Access Application object - a DAO or ADO or ODBC object will work (or even DDE), which will be much faster, much more robust, much easier, much better than having both applications open and automating one from the other.

If you start from Access, it sounds like this project will require an Excel automation object. That works well now, but it is still much slower and more fragile doing it with just Excel and a DAO object.

Answer 2

Do all the work in Access VBA. See the following URLs for some sample code

Modules: Sample Excel Automation

Modules: Transferring Records to Excel with Automation

Also note that if you are dealing with multiple versions of Excel late binding becomes a necessity. Late binding means you can safely remove the reference and only have an error when the app executes lines of code in question. Rather than erroring out while starting up the app and not allowing the users in the app at all. Or when hitting a mid, left or trim function call.

This also is very useful when you don't know version of the external application will reside on the target system. Or if your organization is in the middle of moving from one version to another.

For more information including additional text and some detailed links see the Late Binding in Microsoft Access page.

As far as emailing goes there are a number of options at the Microsoft Access Email FAQ

Answer 3

I have recently done something quite similar to this, and have found that I can output HTML with built-in CSS for formatting that loads quite nicely into Excel. I used Access to allow users to build their required output, only opening Excel to display the results. You may find that HTML output makes for nicer emails.

Answer 4

To get started from Access, add a reference to the Excel object library. Then use the object browser to familiarize yourself with how the Excel object hierarchy looks from within Access. It is going to be somewhat difference, because the top-level object in Excel code is implicit (as it is in Access), and has to be explicitly referenced when coding in Access.

The Access Developers Handbook has excellent chapters on automating the rest of Office from Access.

Last of all, it's best once you've coded using the reference to the other app's automation library to help you program, you want to switch to late binding so you can remove the reference. This means not using any of the external library's specific data types (you mostly use plain object variables) and using none of the constants defined in the external library. My production code with late binding usually includes the early binding version commented out, alongside the late binding version.

Answer 5

I haven't done VBA for ages although Access was my entry-point into the world of programming. Moving from Access 97 to Visual Basic was easy and I remember that I wrote a lot of stuff using Access more like a VB-form for doing many kinds of tasks (not necessarily database-stuff).

This is the reason why I think you should stick to Access and from there, with help of VBA, do your stuff in Excel etc..

Good luck

Answer 6

I think the keywords you are looking for is "microsoft office automation".

Make an application in you favorite programming language that supports COM interfacing and then use automation to do the manipulations in the different office applications.

Look here c# How to access an excel cell? and here How to read data of an excel file using c# ? and Google.