Use Excel VBA for editing and find/replace in a Word document

**drdavidge** · 06-29-2010, 10:26 PM

Hey guys - I just discovered there are ways to use Excel macros to open and edit Word documents, but I can't seem to find much info or examples on how to do a few things. So far I have only been able to input text from my Excel sheet into a specific pre-defined location (via Word "bookmarks"). Anyone have any good websites or info that have some info on the following things?

Excel Sheet for examples:
A B
Field Name - Data
1 Seller John
2 Buyer Jane
3 Price $100
4 Amount 50
5 Terms Terms terms terms blah blah

1) If i set up a Word document that has some text in there like [SELLER], can I have excel go through the Word doc and do a find/replace for [SELLER] and replace it with John above?

2) Say I wanted to insert text somewhere in the Word doc at a bookmarked location, but I wanted to add formatting. For example, I want to underline or bold certain words that I insert. (Like in "terms" above). How can I do that?

3) If I have a checkbox in Word.. how do I toggle checking that on/off from Excel?

thanks.

**Leith Ross** · 06-30-2010, 01:29 AM

Hello drdavidge,

Not surprising you haven't found much. Excel is a spreadsheet program and Word is text editor. There isn't a whole lot of crossover between them. One of the most crossover functions is a mail merge. Excel is suited for creating data tables and Word for formatting and arranging the text on the page.

Word has its own Find and Replace function that can be accessed using VBA. All formatting of the document has to be done through Word 's VBA routines.

Your first 2 questions are more Word VBA questions than Excel. The third question is an Excel question, but really doesn't seem germane to the previous two. In cases like this, you really should post the Excel workbook and the Word document.

**snb** · 06-30-2010, 04:06 AM

The simplest way of combining Excel Data in a word-document is using Document variables.{DOCVARIABLE} is a field in Word.
You can assign an Excel value to the docvariable and then update in Word.

Sub example()
  With getobject(C:\example.doc")
    .variables("seller")=sheets(1).Range("A5")
    .fields.update
    .fields.unlink
    .close -1
  End with
End Sub

**drdavidge** · 06-30-2010, 01:20 PM

For #2, here's an example:

Excel's cell A1 = "This is what Cell A1 contains"

I have a bookmark in Word called "InsertLocation"

Excel VBA Code:

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open("h:\file.doc")

wrdDoc.Bookmarks("InsertLocation").Range.Text = Range("A1").Value

Word ends up with having this at the "InsertLocation" bookmark location:

"This is what Cell A1 contains" (Lacking the intra-cell bold / underline)

How can I have the intra-cell formatting copy over? I tried replacing Range("A1").Value with Range("A1").Text and still had the same result.