WalterAM.com - Automatic Writing Part 1: Creating a Word document from another VBA application
Microsoft VBA - A short user-defined function to create a Microsoft Word document from another Microsoft VBA-enabled application. Part 1 of a series.
published 2012-01-23
As I mentioned in the first
article for 2012, this year I am going to write about smaller
functions and subroutines that we can incorporate in to larger projects.
In this series, I will discuss code that writes from a VBA-enabled
application to a Word document. I use code like this in a variety of
ways including creating documents from data in:
An Access database (I have never liked the way Access converts reports
into Word documents).
An Excel spreadsheet.
An Outlook calendar.
We will start with a user-defined
function that opens the Microsoft Word application and creates a
blank document that can then be used by other functions and
subroutines.
Function udfCreateWordDoc(Optional strTemplate As _
String = "Normal", Optional bolVisible As _
Boolean = True) As Object
Dim objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Documents.Add Template:=strTemplate, _
NewTemplate:=False, DocumentType:=0
objWord.Visible = bolVisible
Set udfCreateWordDoc = objWord
End FunctionThe signature line of the function (the first line) gives us the name of the function,
udfCreateWordDoc and that the function
will return an Object. It also tells us the function can take a string
and/or a Boolean
value as optional input. By the way, the underscore (_) is a line
continuation character in VBA.New Word documents are based on templates. If the function is passed the path to a template and its filename, the application will use that template. If the code is not passed any text for
strTemplate,
the signature line supplies the string "Normal," and the Normal
template is used. We will see different ways to call the function below.
The next line (
Dim objWord As Object) simply declares
a variable to hold the object we are creating before we return it to
the code that calls our function.The
Set objWord line actually starts Microsoft Word and
makes our variable a reference to the Word session we created.
objWord.Documents.AddobjWord.Visible sets the visibility of the instance of
Word we are referencing. If a value is not supplied, the Microsoft Word
document we created will be displayed and the user of the script will
see the file being built. I find this very helpful when I am writing
the script. You can pass the False value in to the
function and Word will not be displayed; the Word document will not be
visible to the user.
Set udfCreateWordDoc = objWordTalking of which, now I will discuss the various ways this function can be called:
Set objMyWordDoc = udfCreateWordDoc()
Set objMyWordDoc = _
udfCreateWordDoc("C:\myTemplates\learningDoc.dot")
Set objMyWordDoc = _
udfCreateWordDoc("C:\myTemplates\learningDoc.dot", _
False)
Set objMyWordDoc = udfCreateWordDoc(, False)A user-defined function usually returns a value. In this case, it returns an object that refers to an instance of Microsoft Word. Since we want to be able to work with the object, we set a variable equal to our function’s return value (hence
Set objMyWordDoc =).The first example uses the defaults built in to our function. Thus, the Word document will be created using the Normal template and the application will be visible.
The second example uses a template on the hard drive. You would supply the path and file name of the template you are using. This example uses the default for
bolVisible and displays the application.The third example sets the value of the template and sets
bolVisible
to False. In this case, the user of the script will not
see the activity in the document.The fourth example will create a document using the Normal template and the application will be hidden.
Next time, I will discuss writing text to the document created by this function.
If you would like to comment on this article, please email me
(send to: comment at walteram dot com, subject: Comment on Automatic Writing Part 1: Creating a Word document from another VBA application)
Comments will be reviewed and posted if not inappropriate.
Your email username (the part before the @ sign) will be used as the commenter name unless you specify otherwise.