Page 1
Last week I’ve been doing some VBA in Excel while creating a small tool that will automatically create a new word document from a template (.dot file) and at the same time, updating some bookmarks in the template with values added in the Excel sheet.
Updating a Bookmark value
It looks like a very simple task, and with a little googling (or binging) around, you’ll get a lot implemented quickly. Right here you’ll find one of the many examples of how to update a bookmark’s value through VBA. And if you want to know a little bit more about Bookmarks in Word and the use of it, I recommend reading this article.
With the code I found here and there on the net, I was able to write a procedure like this…
Sub InsertBookmarkValue(wrdDoc As Word.Document, bookMarkTitle As String, value As String)
'declare a range
Dim bkRange As Word.range
' set the range equal to the bookmark
Set bkRange = wrdDoc.Bookmarks(bookMarkTitle).range
' pass in the text
bkRange.Text = value
' wrap same bookmark around new text
wrdDoc.Bookmarks.Add bookMarkTitle, bkRange
End Sub
… which can be called like in the code below to updates a specific bookmark with some text.
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")
'create new doc from template
Set wrdDoc = wrdApp.Documents.Add(“MyWordTemplate.dot”)
' insert value in bookmark
InsertBookmarkValue wrdDoc, "BookmarkName", “MyBookmarkValue”
The challenge came when I wanted to add some paragraphs (actually a bulletlist) somewhere IN THE MIDDLE of my document. You’ll find a lot of examples of how to add paragraphs at the end or beginning of a “new” document but nothing that represented anything that I wanted to fulfil.
Insert a paragraph
It took me most of the day to find a solution, but this is what I came up with. Since it was very hard to find examples on the web, I decided to write it up and post it here in hope that it may be useful to some other person in the future.
If you want to understand more how I came up with it, read further and I’ll explain it below ;)
This small procedure simply adds a new paragraph (with text) to the beginning of a (bookmark)range in a Word document.
Sub InsertParagraph(wDoc As Word.Document, wRange As Word.range, Text As String)
Dim para As Word.Paragraph
Set para = wDoc.Paragraphs.Add(wRange)
para.range.InsertBefore Text
End Sub
Following code will add 5 paragraphs at the place of MyBookMark in the template.
Dim wRange As Word.range
Dim i As Integer
' Find bookmark in word template
Set wRange = wrdDoc.Bookmarks("MyBookMark ").range
For i = 1 To 5
InsertParagraph wrdDoc, wRange, “This is the paragraph text “ & i
Next i
' delete placeholder paragraph
wRange.Paragraphs(wRange.Paragraphs.Count).range.Delete
The last line will delete the text that was original present in the word template for the specific bookmark.
The result looks like the images below. The word template (.dot file) contains a bookmark with some default text at the place where you want to add your list.
Note the bookmark holders, indicating that the text is within the range of the bookmark (see below).
After the above script, the created document will look like this:
Note that the bookmark holders are still there (I didn’t deleted the bookmark) and that all paragraphs are WITHIN the bookmark range.
Remark: The paragraphs are always inserted BEFORE the previous paragraph in the bookmark range. I have tried multiple examples, but I didn’t manage adding the paragraphs AFTER the previous, not even with the “InsertAfter” method of the range object. So if you want to have an ordered list, make sure to insert the last one first :D
The Range Object
I’m one of those people that are not happy with just copy/past some code I found somewhere. I usually have to understand the code before I can actually use it with an easy heart. My initial tries with some examples I found all failed because I just had no clue what was going on. So I decided to dig a little deeper and try to actually understand the API I was working with.
I figured out, it all evolves around 1 object, the Range object. This object is present in all Office applications, but represent different things.
This is the general description of the range Interface from MSDN:
“Represents a contiguous area in a document.”
If you scroll down a little you’ll find a little more explication in the “Remarks” section:
“Each Range object is defined by a starting and ending character position. Similar to the way bookmarks are used in a document, Range objects are used to identify specific portions of a document. However, unlike a bookmark, a Range object only exists while the programming code that defined it is running. Range objects are independent of the selection. That is, you can define and manipulate a range without changing the selection. You can also define multiple ranges in a document, while there can be only one selection per pane.
Use the Range method to return a Range object defined by the given starting and ending character positions.
Use the Range property of an individual object to return a Range object defined by the beginning and end of the object. The Range property applies to many objects (for example, Paragraph, Bookmark, and Cell).”
OK, so a range is some abstract thing that can contain anything within a document, including paragraphs, words, letters, images, or the whole document content.
Let’s have now a look at the Paragraphs.Add method of a Word Document (the one’s been using by all examples I encountered and which supposed to be doing what I want, adding a paragraph).
“Returns a Paragraph object that represents a new, blank paragraph added to a document.”
That seems to do what I want… only, where in the document does it add the paragraph?
“If Range isn't specified, the new paragraph is added after the selection or range or at the end of the document, depending on expression.”
So, by default it’s at the end of the document, or after wherever you are positioned at that time in the document.
There it is again, a Range. So what is that Range parameter then in this context?
|
Range
|
Optional
|
Variant
|
The range before which you want the new paragraph to be added. The new paragraph doesn't replace the range.
|
Now we start to understand something. When you specify a range, for example the range of a bookmark in your document, the paragraph will be added AT THE BEGINNING of that range.
Let’s try it with an example. I create a bookmark in my document with some text. It will look like this.
[This is some range text]¶
The range of this bookmark is the text “This is some range text”. The [] are the bookmark handlers. I recommend turning the non-printing characters on so you really see what you’re typing. Lots of people hate those, I cannot type without them :D).
When I do now:
Dim para As Word.Paragraph
Set para = wDoc.Paragraphs.Add(wDoc.Bookmarks(“MyBookMark”).Range)
the bookmark’s range will look like this
[¶This is some range text]¶
You see that a paragraph mark is inserted BEFORE the range text.
When you call now
para.range.InsertBefore “Some Text”
You will insert this text at the beginning of the parameter, making it all look like this:
[Some Text¶This is some range text]¶
Note again that the bookmark handlers are still there, but that now the range of the bookmark covers 2 paragraphs, new one and the original text.
Armed with this knowledge, I’m able now to add paragraphs anywhere in my document by only marking the place with a bookmark in my template.
Conclusion
Always read the library documentation when things are getting unclear (lol). And try things very small, see what the output is and then integrate it in a large application.
I must say, everybody (especially managers) are saying that VBA is very easy to do. Unfortunately, my experience was a little different. I find the Office API (because it’s pretty much the same that you can use in .NET as well) very confusing. It’s not an API that you can pick up and be off with in no time, especially not if you want to know what you’re doing. Maybe those developers should read once the Framework Design Guidelines book ;)
At least I learned something now, that’s what life is all about ;)