Replace double angle quote problem

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.

Replace double angle quote problem

Introduction

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!

Notes:
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)

Whole code

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

Summary

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.

Tomasz Płociński

Author: Tomasz Płociński

Excel VBA enthusiast who is also open for other languages. Mainly working in VBA, some SQL, hungry for more.

Leave a Reply

Your email address will not be published. Required fields are marked *