Until recently, I was not even aware of various quotation marks such as double angle quote. In today’s article, we will discuss the replace function and problems that can be caused by a simple attempt to replace double angle quote with standard Word settings.
When I was having fun with .Find Word function I also decided to practice .Replace function. As always, I searched for some information and examples in Google. After some time I came across with very interesting topic on the Stack Overflow. It was about double angle quote mis-replacement.
I could not believe that Word is replacing itself double angle quote Chr(171) for double quote Chr(34) until I tried it out in my Word.
It turns out, that if I try to replace/paste Chr(171), my Word doesn’t want to do that. Instead it is pasting Chr(34), just like in the example described on Stack. I got into this.
Solution of this case
After some time I found the solution. In my regional settings or just in default Word settings there was Options.AutoFormatAsYouTypeReplaceQuotes turned on. So if you want to turn it off permanently, just put this in the beginning of your code.
Options.AutoFormatAsYouTypeReplaceQuotes = False
But if You want to just turn it off for some code lines and then bring it back, You need to get the status of this option into variable to know what to return in the end (for example replacing double angle quote code just like I mentioned above).
userSmartQuotes = Options.AutoFormatAsYouTypeReplaceQuotes Options.AutoFormatAsYouTypeReplaceQuotes = False With ctn.Find .ClearFormatting .Text = Chr(171) & Chr(32) .Replacement.Text = Chr(171) .Wrap = wdFindContinue .Forward = True .Execute Replace:=wdReplaceAll, Forward:=True End With Options.AutoFormatAsYouTypeReplaceQuotes = userSmartQuotes
And just like that, the problem is gone!
Notice that comparing to the .Find function I just added Replace:=wdReplaceAll to replace all the occurrences of the chosen phrase .Text = Chr(171) & Chr(32)
As always, we want to do the whole thing from the Excel console, because we are Simple Excel VBA!
Some notes about code:
– Remember about Word reference in the library if You want to use that kind of variables as I used. In other case create and set Word objects.
Microsoft Word 16.0 Object Library reference
– Word.Range is not the same as Range variable type, if You are writing code from Excel VBA editor! That difference can cause error!
Dim wdDoc As Document Dim ctn As Word.Range Set wdDoc = wd.Documents.Open(fpath) Set ctn = wdDoc.Content
– Options.AutoFormatAsYouTypeReplaceQuotes returns Boolean value, so remember about declaring variable as Boolean.
Dim userSmartQuotes As Boolean userSmartQuotes = Options.AutoFormatAsYouTypeReplaceQuotes
Keep that in mind and start coding!
Option Explicit Sub ReplaceDoubleAngleQuotes() 'Microsoft Word 16.0 Object Library reference Dim wd As Word.Application Dim fpath As String Set wd = New Word.Application wd.Visible = True fpath = "Your Word file path" Dim userSmartQuotes As Boolean Dim wdDoc As Document Dim ctn As Word.Range Set wdDoc = wd.Documents.Open(fpath) Set ctn = wdDoc.Content ctn.SetRange Start:=ctn.Start, End:=wdDoc.Range.End userSmartQuotes = Options.AutoFormatAsYouTypeReplaceQuotes Options.AutoFormatAsYouTypeReplaceQuotes = False With ctn.Find .ClearFormatting .Text = Chr(171) & Chr(32) .Replacement.Text = ChrW(171) .Wrap = wdFindContinue .Forward = True .Execute Replace:=wdReplaceAll, Forward:=True End With Options.AutoFormatAsYouTypeReplaceQuotes = userSmartQuotes wdDoc.Save wdDoc.Close 0 wd.Quit End Sub
Now whole thing looks really easy, but believe me – it was not easy at all. It was confusing. Thankfully You don’t have to go through this alone. Now You should know how to use replace function and replace double angle quote successfully.