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

Not to sound like a programming snob, but is there a reason you would do this in Excel and Access versus Visual Studio and MSDE?

If you take that route it opens up a LOT more possibilities and makes debugging and programming a lot simpler.

Visual Studio makes debugging a breeze, and with MSDE you run into a lot fewer problems with database performance and file locking.

If you're set on using Excel, you might still look into VSTO (Visual Studio Tools for Office) for some level of integration with Visual Studio.

Answer 2

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 3

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 4

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 5

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