How do I import Word bookmarks into Excel cells

G

Guest

I would like to create an Excel database by importing information for each
record (row) from about 30 different bookmarks in each Word document
populating designated columns at the next available row at the bottom of the
data table. I anticipate that the data base will contain a record (row) for
each of about 50 different Word documents.
 
J

JE McGimpsey

one way:

Dim oWord As Word.Application
Dim oDoc As Word.document
Dim vBkMarks As Variant
Dim vRecord
Dim rRecord As Range
Dim nFields As Long
Dim i As Long

vBkMarks = Array("Bookmark1", "Bookmark2", "Bookmark3") 'etc...
ReDim vRecord(LBound(vBkMarks) To UBound(vBkMarks))
nFields = UBound(vBkMarks) - LBound(vBkMarks) + 1
On Error Resume Next
Set oWord = GetObject(, "Word.Application")
On Error GoTo 0
If oWord Is Nothing Then _
Set oWord = CreateObject("Word.Application")
Set oDoc = oWord.ActiveDocument
For i = LBound(vBkMarks) To UBound(vBkMarks)
vRecord(i) = oDoc.Bookmarks(vBkMarks(i)).Range.Text
Next i
With Sheets("DataTable")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize( _
1, nFields).Value = vRecord
End With
 
D

Dave Peterson

First, this may not help a bit...

MSWord has an option under Tools|Options|Save Tab:
Save data only for forms

From MSWord's help for that option.

Save data only for forms Saves the data entered into an online form as a
single, tab-delimited text file in plain text format. You can then import the
contents of that file into a database for analysis.

It may be something to consider if you're not too far into your project.

(If you have followups, you may want to ask in an MSWord newsgroup, though.)
 
G

Guest

Thanks for the information. The bookmarks are already in Word documents
provided by others that I have no influence over. But your information could
be valuable in some other projects in the future. Thanks again.
 
G

Guest

JE McGimpsey,
Thank you for your reply to my Bookmark question. It has been so long since
I worked with VB that I could not get the copy/pasted version of your code
inserted properly into Excel. I will keep trying to get the code in
properly. Thanks again for your help.
Glen
 
G

Guest

JE McGimpsey
I am making progress. I got an error message. The message was "Compile
error: User-defined type not defined".
 
J

JE McGimpsey

Yup - forgot to state that you need to set a reference to the Word
object library. With your project selected in the VBE, choose
Tools/References... and select the Word XX Object library from the
dialog.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top