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
