[mocha99]

Hi all,

in my Word document i have some placeholders in the footnotes like [22 .

I have an Excel spreadsheet were in column A I have the same placeholder
of the Word document and in column B the associated value like in

A B
[22 cow

Is there a way to automatically substitute the string [22 in the Word
doc with the string cow from Excel? I have tried with mailmerge but
could not figure out how to do it.

Thanks

[Doug Robbins - Word MVP]

The following code in a Word macro should do what you want if you follow the
Requirements below the code:

Dim i As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
With ActiveDocument
For i = 1 To .Footnotes.Count
If InStr(.Footnotes(i).Range.Text, "[") 0 Then
' Open the database
Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel
8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `myDatabase`")
'Loop through all the records in the table until the end-of-file
marker is reached
Do While Not rs.EOF
If rs.Fields(1) = .Footnotes(i).Range.Text Then
.Footnotes(i).Range.Text = rs.Fields(2)
End If
rs.MoveNext
Loop
' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End If
Next i
End With

Requirements:

First, you need to set a reference in your project to the "Microsoft DAO
3.51 (or 3.6) Object Library". This is done by selecting References from
the Tools menu in the Visual Basic Editor.

If you are needing to retrieve the data from an Excel 2007 spreadsheet,
instead of the reference being to the "Microsoft DAO 3.51 (or 3.6) Object
Library, it needs to be to the Microsoft Office 12.0 Access database engine
Object Library and replace the following line of code in the above macro:

Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel
8.0")

with:

Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel
12.0")

In the above lines of code, you will need to replace the

C:\Test\Book1.xls

with the path and name of your Excel Spreadsheet.

In that Excel Spreadsheet, you will need to have assigned the name

myDatabase

to the cells in Column A and B that contain the placeholders and the text
associated with them.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"mocha99" wrote in message
...
Hi all,

in my Word document i have some placeholders in the footnotes like [22 .

I have an Excel spreadsheet were in column A I have the same placeholder
of the Word document and in column B the associated value like in

A B
[22 cow

Is there a way to automatically substitute the string [22 in the Word doc
with the string cow from Excel? I have tried with mailmerge but could not
figure out how to do it.

Thanks