How to move ALL Bookmarks from Word to Excel

[Michael 514]

Hello again,

It looks like I got ahead of myself in my last post.

Perhaps someone here can help me modify a line of code to help me transfer Word Bookmark values to Excel.

The code that I have works partially. I would imagine that to get it to work fully is a relatively simple thing.

I hope you can help.

The Excel macro in question asks for a starting row number, a starting column number, the Word file(s) to use, and whether or not it should print the bookmark names (as opposed to the values) on the first row.

That part works excellent. Every single Bookmark name, in alphabetical order, gets listed across the first row.

The macro designer cycles through variable x, which is 1 to WordDoc.Bookmarks.Count.

They are spit out like this:

ActiveSheet.Cells(intRow, x + intCol - 1) = WordDoc.Bookmarks(x).Name

And it's fine. 50 Bookmark names get printed across the row.

Now, here is the difficulty I am having....

I noticed that in the subsequent rows, where I want the bookmark values, some cells are blank!!

(I hope I am making sense here...)

I noticed that any time I have named (bookmarked) a formfield, such as a Drop Down or text box, the value indeed gets pasted.

However, if it is a "regular" bookmark (for example, if I select a phrase in the protected part of the form.... or if I select a calculated field).. then it leaves it blank.

Looking at the code that I have, the guy wrote this:

ActiveSheet.Cells(intRow + intFileNum, x + intCol - 1) = WordDoc.FormFields(WordDoc.Bookmarks(x).Name).Result

(It cycles through X to number of bookmarks again..)

...so from what I gather... he is pasting the Formfields that have Bookmark names attached to them.

..and that is what it is doing. But that is not what I want it to do!

I then have blank cells where all my calculated fields should be, as well as my values that are hardcoded into the form in protected areas that have bookmarks attached.

Can someone please tell me what I would need instead of:

WordDoc.FormFields(WordDoc.Bookmarks(x).Name).Result

If anyone can help, I would reallly appreciate it!

[Brandtrock]

Michael,

No code or file is attached that I can see.

If posting code, be sure to use the code tags.

Regards,

[Anne Troy]

I changed his code tags, but we're gonna have to ask Mark007 why the word "result" is being spaced out. The text is NOT spaced that way.

I have notified Mark.

[Michael 514]

Thanks for fixing up my tags, Dreamboat!

I didn't even know that such a thing existed. And yes, weird thing about the word "result" being spaced out.


I am trying all sorts of combinations here... I just can't figure out how to return the value of a bookmark. Arghhh!

This is so depressing!!

I am going to paste the whole function using those tags... Let's see how it looks..

 Private Function FillExcel(strFile As String, intFileNum As Integer)
    Dim WordDoc As Word.Document
    Dim WordRange As Word.Range
    Dim x As Integer
    On Error Resume Next
    Set WordDoc = WordObj.Documents.Open(strFile)
    ' Uncomment the ' on the next line to see Word cycle through the values
    WordObj.Visible = True
    ' Activate the current WorkSheet (Change Sheet1 to whatever Worksheet you are using)
    Worksheets("Sheet1").Activate
    ' Prompt to use FieldNames as Column Headers
    If intFileNum = 1 Then
        If MsgBox("In the first row, would you like to paste the headers (instead of a blank line)?", _
        vbYesNo, "Column Headers") = vbYes Then
            For x = 1 To WordDoc.Bookmarks.Count
                ' This line takes the name of the bookmark and puts it
                ' on the first row (only for the first doc opened)
                ActiveSheet.Cells(intRow, x + intCol - 1) = WordDoc.Bookmarks(x).Name
                ' This line works like a charm. I see all the bookmarks in the document.
            Next
        End If
    End If
    ' Loop through all the fields
    For x = 1 To WordDoc.Bookmarks.Count
        ' intRow is the file order number (each file will import on a new row)
        ' the x is the column - Essentially it'll cycle through all the bookmarks in the Word doc
        ActiveSheet.Cells(intRow + intFileNum, x + intCol - 1) = _
        WordDoc.FormFields(WordDoc.Bookmarks(x).Name).Result
        ' This is the problematic line. I want all the bookmark values,
        ' not just the ones that are formfields...
    Next
    WordDoc.Close
    Set WordDoc = Nothing
End Function

<< Hey these tags are cool! >>

[Anne Troy]

Dumb question: Why AREN'T those other bookmarks in formfields? If it's static data, why can't it retrieved some other way?

Anyway...you still need to upload the file that contains all yer code.

[Michael 514]

Great question, actually!

The answer is simple.

In my form, yes, there are several formfields that the user types in. For example, let's say the user must answer three questions on the form.

The form looks like this, and is protected.

Question1 - Your answer: [ ] Max Value: 10
Question2 - Your answer: [ ] Max Value: 5
Question3 - Your answer: [ ] Max Value: 100

In that case, there are three formfields, yet six bookmarks.

The formfield bookmarks are named Q1_Answer, Q2_Answer, Q3_Answer.

The three other bookmarks (that are not actual formfields) are Q1_Max, Q2_Max, Q3_Max.

So when I run the routine, the top line is great:

Q1_Answer | Q1_Max | Q2_Answer | Q2_Max....

And it is quite exciting.

However, because of the way it's coded, all the values under Q1_max, Q2_max, and Q3_max are blank!!

I _do_ hope there is a way to return bookmark values. There must be, right??

I can manipulate them easily in Word.

If I insert the following formula into a table: =Q2_Max, well... it's there! I'll see '5'.

So the big question is how to return these values into Excel.

I hope it's making sense!

I see this thread has 60+ views and no answers yet....
I hope this can be done somehow...!

[TonyJollans]

Hi Mike,

At the top of your module you have this:

On Error Resume Next

This means that errors are ignored.

Next, you get all your headings (bookmark names) by looping through all the bookmarks in the document - so far so good.

Then, to get the bookmark values you loop using a counter for each bookmark in the document ..

For x = 1 To WordDoc.Bookmarks.Count
    :
    :
Next

Still good! Now, inside the loop you use the Bookmark counter to reference FormFields - not so good, but becuase you ignore errors you don't see it.

So you need to change the code inside the loop. Although I prefer to avoid them, an error trap is probably the easiest way to handle it in this case:

For x = 1 To Me.Bookmarks.Count
    ' intRow is the file order number (each file will import on a new row)
    ' the x is the column - Essentially it'll cycle through all the bookmarks in the Word doc
    ActiveSheet.Cells(intRow + intFileNum, x + intCol - 1) = _
    WordDoc.FormFields(WordDoc.Bookmarks(x).Name).Result
    If Err.Number <> 0 Then
        Err.Clear
        ActiveSheet.Cells(intRow + intFileNum, x + intCol - 1) = _
        WordDoc.Bookmarks(x).Text
    End If
Next

[Michael 514]

Hi Tony!

I'm back!

For some reason, your code isn't working...

In this line:

[VBA] ActiveSheet.Cells(intRow + intFileNum, x + intCol - 1) = WordDoc.Bookmarks(x).Text [/VBA]

...I am getting an error on the word .Text, right at the end.

Compile Error. Method or data member not found.

It also highlighted the first line:
Private Function FillExcel(strFile As String, intFileNum As Integer)

Any ideas???

Thank you!

Mike

I solved it!!

It is WordDoc.Bookmarks(x).Range.Text !!!!!!!

It works! It works!

This macro now pastes every single Word bookmark (both the bookmark name and it's contents) in Excel!

Done!


Thank you Thank you Thank you!

Mike

[TonyJollans]

Sorry, and well done!

[Ken Puls]

Hi Michael,

A couple of quick questions for you. It looks like you're relatively new to VBAX, so welcome! I'm not sure if you know, but you can mark your own threads solved here. (Just see my signature line.) I'll get this one for you.

Second... this would fit very nicely into the KB. Have you thought about submitting it?

[Michael 514]

Hey Ken,

Thanks for the note!

I was actually trying to find a way to mark the thread solved... thanks for pointing it out!

I did manage to give it five stars, though!

And I think this would be perfect for the KB. I can't believe that I am one of the first people to actually want to move bookmarked fields from Word to another Office app.

I have honestly scoured the web, from top to bottom, through meta search engines and through MSDN.. yet I have found NO mention of something as simple as "WordDoc.Bookmarks(x).Range.Text"

I am glad it worked out!

I am a very big office automation guy (not just in terms of the MS suite), but in terms of computing and telephony in general. Therefore, I think I am going to have lots of fun here!

(Now if only we could choose a nice, softer, lighter, gentler skin for this forum, I'd leave it open on my desktop all day... )

Mike

[Ken Puls]

I think I am going to have lots of fun here!

Well then we're glad you found us!

Do submit it to the KB. We're always hungry for more entries!

As for the skin, I'm a fan of the plain old green, but post your suggestion here. You just never know... if Jake likes it, he'll jump all over it. :rofl

[JonPeltier]

Hi Michael -

I have honestly scoured the web, from top to bottom, through meta search engines and through MSDN.. yet I have found NO mention of something as simple as "WordDoc.Bookmarks(x).Range.Text"

Not to be a PITA, but you're not using all the tools at your disposal. In Word's VBE, open the Object Browser, and select Bookmark in the list of classes on the left. There is a relatively short list of members on the right, Text isn't listed, but Range is. Among the huge number of members of the Range class, Text is in fact listed.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

[Michael 514]

Thanks Jon -- you're not being a PITA.

Thanks for the tip. Remember that I have never, ever done any programming in VBA, nor have I so much typed up a macro. (The only macros I've ever recorded are keystroke-macros!)

As of two weeks ago, I didn't know what a class is. Nor an object browser. Etc.

I've done some coding back in school, in assembler, COBOL, BASIC, and Pascal.. but in the past 7 years... Nothin!

I kid you not, though. I am a (self-described!) expert at finding knowledge on the web. And I honestly must have read over 100 articles about pasting, changing, naming, modifying bookmarks and formfields, and other stuff like.

Yet not one single page mentioned anything about returning the value of a bookmark.

Now you can understand why I was soooooo happy to get this solved!!


This forum rocks!

[JonPeltier]

I kid you not, though. I am a (self-described!) expert at finding knowledge on the web.

The last boss I ever had before I started my own company looked at me funny when I told him that not everything I know is in my head. I'm good at finding stuff wherever it may be, and using any help I can find. Google is my third most used programming tool, after the macro recorder and the object browser. I have very little formal training in programming, but loads of hands-on experience with a lot of Q&A and research.

It's frustrating that there are not more resources on VBA programming in Word. There's a few good web sites, but nowhere near the wealth of information you can dig up on Excel. Recently I've done a lot of work with Word templates, fields, form fields, and bookmarks, all being automated from Excel, and I used all three of the tools I mentioned above.

Keep on plugging, tho. If you're really into this automation stuff there's lots of places you can get assistance.

- Jon