如何使用VBA自动执行多个Office应用程序? [英] How to use VBA to automate several Office applications?

问题描述

尽管我已经在单个应用程序中为MS Access 2007和Excel 2007完成了VBA项目，但我没有同时使多个应用程序自动化.通用项目是打开访问权限，运行一些将数据追加到各个表的更新查询.然后，Excel需要获取数据.Excel中需要进行一些格式更改，例如不会自动更改日期范围的分组.最后，我计划将其构建为可以自动通过电子邮件发送excel文件.

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.

其中的某些部分清楚地说明了如何完成此操作，例如Excel将通过ODBC从Access获取数据.但是主VBA应该住在哪里?我可以在Access中有一个按钮来开始运行Access VBA，但是让Access VBA开始操纵Excel是一种好习惯吗?这会使调试变得困难吗?

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?

推荐答案

要从Access入门，请添加对Excel对象库的引用.然后，使用对象浏览器熟悉Access中Excel对象层次结构的外观.这将有所不同，因为Excel代码中的顶级对象是隐式的(就像在Access中一样)，并且在Access中进行编码时必须显式引用.

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.

《 Access开发人员手册》中有出色的章节介绍了如何通过Access自动执行Office的其余部分.

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.

这篇关于如何使用VBA自动执行多个Office应用程序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！