MS Office Automation

Like most businesses the software you are likely to be using will include the Microsoft Office suite of programs plus other software such as accounting etc. You will probably use Microsoft Word and Excel for letters, invoices, statements and many other documents. It is very likely you use Microsoft Outlook for email and appointments and you may even use Microsoft Access databases to store basic data such as customer and supplier details.

But the software you have installed is capable of doing a lot more than you think. All the Microsoft Office programs come with their own powerful programming language called VBA (see below) which means you can get them to do exactly what you them to do. For example, you will probably find that you spend a lot of time copying and pasting data from one document to another. Well a small utility can written to do this for you by opening a document, getting the information you need and automatically inserting the data into another document. You can even get a program to automatically send out emails. There is a lot that your existing software can do for you so, if you think you have areas of your office that could be automated, then we can help.

Visual Basic for Applications

As its name suggests, VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library. However, VBA code normally can only run within a host application, rather than as a standalone program. VBA can, however, control one application from another using OLE Automation. For example, VBA can automatically create a Microsoft Word report from Microsoft Excel data that Excel collects automatically from polled sensors. VBA can use, but not create, ActiveX/COM DLLs, and later versions add support for class modules.

VBA is built into most Microsoft Office applications, including Office for Mac OS X (except version 2008), and other Microsoft applications, including Microsoft MapPoint and Microsoft Visio. VBA is also implemented, at least partially, in applications published by companies other than Microsoft, including ArcGIS, AutoCAD, CorelDraw, LibreOffice, Reflection, SolidWorks, and WordPerfect.

MS Office Automation

As an example, VBA code written in Microsoft Access can establish references to the Excel, Word and Outlook libraries; this allows creating an application that — for instance — runs a query in Access, exports the results to Excel and analyses them, and then formats the output as tables in a Word document or sends them as an Outlook email.

VBA programs can be attached to a menu button, a macro, a keyboard shortcut, or an OLE/COM event, such as the opening of a document in the application. The language provides a user interface in the form of UserForms, which can host ActiveX controls for added functionality.