Find and Replace from Excel to Word ruins formatting

[Pichon]

***UPDATE*** The problem persists, but it appears to have something to do with my Normal.dotm temple because if the Normal style is set to double spaced in normal.dotm, the it generates a double spaced document. If normal style is single spaced in normal.dotm, then it works properly. The other thing that I noticed is that the subroutine is changing the top margin from 1.5" to the default 1". This is super annoying.

I want to execute a find and replace in Word from an Excel subroutine.

I have a worksheet (variable name "stage") with 2 columns of data. Column A has values like <<FirstName>>, <<LastName>>, etc., and column B has values like James, Smith, etc.

My Word document has something like

Hello!

My First Name is: <<FirstName>>
My Last Name is: <<LastName>>

My document is single spaced, 12 pt, Times New Roman

I used this code:

For fieldRow = 2 To lastRow    
    tagName = stage.Cells(fieldRow, 1)
    tagValue = stage.Cells(fieldRow, 2)
    With wdDoc.Content.Find
        .Text = tagName
        .Replacement.Text = tagValue
        .Wrap = wdFindContinue
        .Execute Replace:=wdReplaceAll
    End With
Next fieldRow

This code cycles through the rows in the worksheet and finds the value in column A and replaces it with the value in column B for each row. That part works great. The problem is that the resulting document is double spaced, which I don't want.

Is there a way to preserve Word's formatting while executing this subroutine?

[werafa]

Hi,

I'm not familiar with word objects, but there should be an object for the text that you are replacing, just as there is a range object in excel.

option 1:
An excel range object has Value and Value2 properties. the Value property also contains formatting info, and Value2 does not.

what does

 
tagName = stage.Cells(fieldRow, 1).value2
tagValue = stage.Cells(fieldRow, 2).value2

do?

Option 2, you could try

With wdDoc.Content.Find
        .ClearFormatting
        .Text = tagName
        .Replacement.ClearFormatting 
        .Replacement.Text = tagValue
        .Wrap = wdFindContinue
        .Execute Replace:=wdReplaceAll
    End With

Option 3: can you read (and then reapply) the formatting properties of wdDoc.content object?

Werafa

[Pichon]

Thanks for the reply, Werafa! I just checked your solutions, but no luck. In doing so, I had a thought, perhaps it is the method that I am using to generate the document.

I am using this:

Set wd = New Word.Application
Set wdDoc = wd.Documents.Add
wd.Selection.InsertFile pathToMyDocument

I think this may be the issue. Perhaps the Word document is being copied/pasted into a new document before the Find Replace is being executed. It is being pasted based on my Normal.dotm styles.

Does anyone one know a better way?

[Pichon]

Confirmed. Changing the above code to this fixed the problem:

Set wd = New Word.Application
Set wdDoc = wd.Documents.Open(pathToMyDocuent)

[macropod]

There is nothing about any of the code discussed in this thread that would have any necessary effect on a document's line spacing or its margins. Whatever the cause is concerns code you haven't posted or differences in the way you're accessing whatever pathToMyDocument refers to...

Your code:

Set wdDoc = wd.Documents.Add

creates a new document, using Word's Normal template, whereas:

Set wdDoc = wd.Documents.Open(pathToMyDocument)

opens an existing document.

Obviously, though, if Word's Normal template has different margins & line spacing from whatever pathToMyDocument refers to, that could account for the difference you're seeing when you use the InsertFile method.

Unless the intention is to edit whatever document pathToMyDocument refers to, you should be creating a new document and modifying that. You could achieve that via:

Set wdDoc = wd.Documents.Add(pathToMyDocument)

[Pichon]

Thanks, macropod! I’ll try that. What the code is set to do at the moment is open the document, make edits and save as a new document then close everything up. I prefer your code if it will function the same. As far as the Normal template is concerned, yes, my “Normal” margins are the standard Word 1” all around and the line spacing of the Normal Style in my Normal template is set to double spacing. My current document has different line spacing throughout and a 1.5” top margin.