Automating Word and Excel with VBA and VBScript
How to Automate Microsoft Office Applications
Dec 10, 2008
Any Windows Programmer wanting to automate one of the Microsoft Office applications (such as Microsoft Word and Microsoft Excel) has a very powerful tool at their finger tips - Visual Basic for Applications (VBA). If that programmer then combines VBA with VBScript then the automation of any office application becomes very easy; for example, a they may wish to:
- populate cells in Microsoft Excel
- create Microsoft Word documents and fill them with text
All this can be achieved by running a single VBScript program.
Accessing a Microsoft Office Application
All Microsoft Office applications are accessed in the same way - by using the CreateObject method - this creates a new instance of the Microsoft application, for example Word:
or Excel:
However, how the applications are used are completely different.
Making the Microsoft Application Visible
It's worth noting that VBA and VBScript will keep the Microsoft application invisible by default - this is useful for hiding complicated tasks from the user, but for the programmer it's more useful to be able to see what's going on while developing the application or testing it (and then to allow the user to see the end result). Fortunately it's just a matter of setting the application's visible property, for example:
or:
Obviously the visibility would be set at the start of the process for development and testing, but at the end for user visibility. However, that is where the similarity ends - the process of updating the cells in Excel is very different to the process of updating a Word document's body.
Automating Excel with VBA and VBScript
The first job for VBScript to do in Excel is to create a new workbook:
And the next job is to access one of the document's worksheets:
Now each cell can be accessed individually:
It's worth noting that the value may contain numbers, text or formulae.
Automating Word with VBA and VBScript
Word works with documents rather than worksheets and so the process starts with creating a new document:
And word works with selections rather than cells:
This code:
- accesses the application's text selection
- underlines the text
- makes the text bigger (since this will be the title for the document)
- writes text to the document using the typetext method
- adds paragraph breaks with the typeparagraph method
And then, before carrying on, the underline can be turned off and the text made smaller:
From there on paragraphs can be added required:
At the end of the process the developer will have a simple vbs application that creates a complete Word document from scratch.
Summary
Any Windows user who has Microsoft Office can easily create a simple VBScript application that will automate either Excel or Word. The first step is to use the CreateObject to create an instance of an application.
Word applications:
- require a new document to be added
- have paragraphs added by using the Selection object
Excel applications:
- require a new workbook to be added
- have worksheets containing cells whose values can be updated
Both Word And excel will be invisible unless the visible property is set to true, and the user will never the the result of the VBScript developer's hard work.
 |
 |
 |
