I'm trying to automate Word MailMerge from Access VBA. It's mostly working, but having a strange quirk.
My paragraph text is stored in Access tables in Long Text fields, NOT designated as rich text. I've found that if the text (in Access) contains "smart" quotes and apostrophes, it messes up my code execution and causes errors such as making it look like there are too many fields in the merge data source. Therefore, I make sure all quotes and apostrophes are "plain" ( ' or " ).
Instead of long text fields being in my merge data file, I'm doing a "hybrid" merge where I first populate long text into bookmarks, then do the merge. I only do this for document content which is the same for every document.
Just before the actual merge, I run code to replace the "plain" apostrophes and quotes with "smart" versions, using this code, which relies on Regex:
Code:
' first replace open-apostrophes and open-quotes
With oRegex
.Pattern = " '| """
.Global = True
.IgnoreCase = True
Set oMatches = .Execute(WordDoc.Content)
End With
If oMatches.Count > 0 Then
For Each oMatch In oMatches
matchText = oMatch.Value
With WordDoc.Content.Find
.Text = oMatch.Value
.Replacement.Text = Switch(oMatch = " '", " " & Chr$(145), oMatch = " """, " " & Chr$(147))
.Execute Replace:=wdReplaceAll, Forward:=True, Wrap:=wdFindContinue
End With
Next
End If ' open-apostrophes or open-quotes found
' now replace single apostrophes and quotes (handles close-quotes, close-apostrophes and individual apostrophes
With oRegex
.Pattern = "'|"""
.Global = True
.IgnoreCase = True
Set oMatches = .Execute(WordDoc.Content)
End With
If oMatches.Count > 0 Then
For Each oMatch In oMatches
matchText = oMatch.Value
With WordDoc.Content.Find
.Text = oMatch.Value
.Replacement.Text = Switch(oMatch = "'", Chr$(146), oMatch = """", Chr$(148))
.Execute Replace:=wdReplaceAll, Forward:=True, Wrap:=wdFindContinue
End With
Next
End If ' single apostrophes or quotes found
Notice I'm using
WordDoc.Content, which I assume means ALL content.
When I finish this code execution, I notice that everything works fine except for text in my document footer. This does not get replaced by smart quotes.
I even tried single-stepping through the code and tried replacing WordDoc.Content with WordDoc.StoryRanges(9), which happens to be the footer in my document. This returned a Regex match, but the code would not cause the quotes to be replaced with smart quotes.
So the questions are:
Why doesn't WordDoc.Content include the footer text?
Why doesn't the code work when I use the proper StoryRange.Text? I even tried manually activating the footer while running my code, but no luck.
Thanks in advance for any insight here...