Thursday, January 22, 2009

Create a Word Document with VBA and VBScript: How to Automate Microsoft Word using Visual Basic

Create a Word Document with VBA and VBScript: How to Automate Microsoft Word using Visual Basic: "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 (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:
option explicit

Then declare the variables that are going to be used by the script:
dim word 'the Word application
dim doc 'the Word document
dim selection 'text selection
dim fs 'the File system
dim work_dir 'the directory for storing files
dim folder, folders 'used when creating the new file
dim i, j 'indexes for arrays

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):
work_dir = 'C:\vba\word\'

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:
set word = createobject('word.application')
set fs = createobject('Scripting.FileSystemObject')

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:
set doc = word.documents.add
set selection = word.selection
selection.typetext 'An Automated Word Report'

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 not fs.folderexists(work_dir) then

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:
folders = split (work_dir, '\')
for i = 0 to ubound(folders) - 1
folder = ''
for j = 0 to i
folder = folder & folders(j) & '\'
if not fs.folderexists(folder) then
fs.createfolder folder
end if
end if

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:
doc.saveas(work_dir & 'testdoc.doc')

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:
word.visible = True

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):
set fs = nothing
set word = nothing

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."

No comments: