Consulting

Results 1 to 15 of 15

Thread: Solved: How to move ALL Bookmarks from Word to Excel

  1. #1

    Solved: How to move ALL Bookmarks from Word to Excel

    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:

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

    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:

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

    (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:

    [vba]
    WordDoc.FormFields(WordDoc.Bookmarks(x).Name).Result
    [/vba]


    If anyone can help, I would reallly appreciate it!

  2. #2
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    Michael,

    No code or file is attached that I can see.

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

    Regards,
    Brandtrock




  3. #3
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

  4. #4
    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..

    [VBA] 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 [/VBA]

    << Hey these tags are cool! >>

  5. #5
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

  6. #6
    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...!

  7. #7
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Hi Mike,

    At the top of your module you have this:

    [VBA]On Error Resume Next[/VBA]

    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 ..

    [VBA]For x = 1 To WordDoc.Bookmarks.Count
    :
    :
    Next[/VBA]

    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:

    [VBA]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[/VBA]
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  8. #8
    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

  9. #9
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Sorry, and well done!
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  10. #10
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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?
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  11. #11
    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

  12. #12
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by Michael 514
    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
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  13. #13
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    Hi Michael -

    Quote Originally Posted by Michael 514
    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/
    _______


  14. #14
    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!

  15. #15
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    Quote Originally Posted by Michael 514
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •