Create a Word Document with VBA and VBScript
How to Automate Microsoft Word using Visual Basic
Dec 11, 2008
Microsoft Word has been around (in one form or another) since 1981 and it (with Microsoft Excel) forms the core of Microsoft Office. There are alternatives, such as OpenOffice.org (which has the advantage of being a cross-platform application), but there is no real competition - and one of the key uses of Microsoft Word is the writing of reports.
How may reports are written in Microsoft Word every year? Millions? And how many man hours are used in the production of those reports? Billions? One simple way of reducing some of that time and effort is to start automating reports - and a programmer can easily do this by using VBA (Visual Basic for Applications) with VBScript.
The starting point for this automation is a file saved with a .vbs extension, some variable declarations and a little code.
Declaring Variables for the Script
As always enforce variable declaration:
Then declare the variables that are going to be used by the script:
The variables in VBScript are not defined with a specific data type - by default they are all of the data type variant, but once the variables have been declared they can be populated (if the values are known at this point):
Once any required variables have been loaded with data the programmer can turn to any objects that they may need access to.
Creating Objects
The programmer gains access to any OLE (Object Linking and Embedding) compliant applications though the createobject method. In this case access a Word application and the file system are required:
The next stage is to start making use of these objects in the vbs script.
Creating a New Word Document
Once the Word application object has been initiated then a new document can be created and written to - and the process is very simple:
- use the add method to create a new document
- access the document selection (the text)
- use the typetext method to write to the document
- use the typeparagraph method to add a paragraph break
And so the code will be something like:
A programmer can create a complete report just by adding more paragraphs -perhaps from data stored in a database. However, where ever the information comes from, the next stage is to save the document to a folder somewhere on the network.
Creating a New Directory
VBScript has no in-built method for creating a folder (or even checking that a folder exists); however, this is where the programmer can make use of the FileSystemObject (FSO) initiated at the start of the script. First the FSO is used to check if the directory exists:
If the folder does not exist then it must be created - however this cannot be done in a single stroke; for example, in the case of C:\vba\word\
- C: must be present before the vba folder can exist
- the vba folder must exist before the word folder can be created
So the directory tree must be created folder by folder - progressing from the top layer to the bottom creating folders as they are required:
Once the folders are in place the document may be saved.
Saving a Word Document
Saving a Word document is very easy - the programmer only needs the saveas method:
And with that the new Word document has been created - it's now just a matter of a little housekeeping.
Ending the Script
If all of the code is saved to a file (for example create_report.vbs) then a user can run it by double-clicking on the file icon in Windows Explorer; however, they will see not output - and that's because VBA keeps the file invisible by default. The developer, therefore, has two choices at this point - they can:
- make the document visible to the user
- close the Word application
To make the file visible the programmer would need to add the code:
Or to close Microsoft Word:
And the programmer will also want to fully unload the FSO and the Word application (to ensure no memory leakage occurs):
At this point, regardless of the script ending, the user will have brand new Word document in the specified folder - without having to type in a single word themselves.
 |
 |
 |
