OLE links to Word bookmarks are problematic in Excel

[John]

Hello group -

I'm trying to populate an Excel spreadsheet with links to bookmarks in
Word. It sort of works, but not perfectly. Sometimes I get the text I
want, other times I get the heading number (e.g. 1.2.2) or just blanks
instead.

For debug, I msgbox the sentence in Word every time I create a bookmark
and it's correct. Please help!

In WORD, I add bookmarks like this:

With Selection 'check size of selection
If .Start = .End Then 'if nothing selected, mark entire sentence
.Sentences(1).Select
.Sentences(1).bookmarks.Add Name:=bookmark_name
Else: 'if something is selected, mark just
selection
.bookmarks.Add Name:=bookmark_name
End If
End With

In EXCEL, I build the link to the bookmark this:

With ActiveSheet.Cells(targetrow, 2)
.Formula = "=" & " Word.Document|'" & filepathname & "'!'!" &
bookmark_name & "'"
.WrapText = True
.Calculate
End With

 

[Marco Pagliero]

I'm trying to populate an Excel spreadsheet with links to bookmarks in
Word. It sort of works, but not perfectly. Sometimes I get the text I
want, other times I get the heading number (e.g. 1.2.2) or just blanks
instead.

For debug, I msgbox the sentence in Word every time I create a bookmark
and it's correct. Please help!

Please, where exactly do you put this msgbox?

Marco P

 

[John]

It can go anywhere in the Word vba code after the line:

.Sentences(1).Select

and look like this:

msgbox Selection.sentences(1)

 

[John]

Just trying to keep this post live.
Please, if anyone can help...

Thank you!

 

[Jean-Guy Marcil]

John was telling us:
John nous racontait que :

Just trying to keep this post live.
Please, if anyone can help...

Thank you!

I have just tried your code as is, and it ran fine.

But, if your "Sentence" is numbered with the an automatic list number, this
number will not be part of the bookmark.

So it is hard to see how you would sometimes end up with just such a number.

One thing, a sentence for Word may not what you expect it to be. You may
want to use "Paragraphs(1).Range" instead of "Sentences(1)"

Also, are you sure that the unexpected results are related to the first part
of the If statement or the second part?


--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[John]

Jean-Guy -
Thank you for testing out my code and for your insight. What do you
mean by it "ran fine"?
Did you always get the full sentence even when the sentence was not
highlighted?

I guess it would be important to know how what Word considers to be a
sentence. From what I could tell, it was all the characters between
two periods, or all the characters on one line that had no periods.

What I'm trying to accomplish is a means to easily bookmark text from a
Software Requirements Document which then gets exported into a
spreadsheet for tracking verification tests. I want to export the
entire sentence when you just click on the sentence, but only the
highlighted words when words get selected. I don't think I can use
paragraphs, since one paragraph may contain multiple requirements that
should be bookmarked individually.

For debug purposes, I msgbox the sentence or selected text that I have
just bookmarked and it looks perfect. It seems that something is being
lost in the OLE link...the way Excel brings in the text.

If .Start <> .End (which means I have highlighted some text), then I
always get the correct text in Excel.

I wonder, could .Start ever be greater than .End? What would that do?

Maybe I'll try flip-flopping the if statement by checking if .Start =
..End first.

Don't know if this is another clue, but sometimes the incorrect text
isn't even the correct heading number for that sentence. It has the
right format (x.x.x), but it is sometimes the first heading number of
the section. And this can be incorrect for multiple sentences in a
row.

I find it baffling how little documentation there is for such a widely
used set of applications!

Thanks again for the help!

John

 

[Jean-Guy Marcil]

John was telling us:
John nous racontait que :

Jean-Guy -
Thank you for testing out my code and for your insight. What do you
mean by it "ran fine"?

I mean, there was no error or unexpected results... it was fine!

Did you always get the full sentence even when the sentence was not
highlighted?

I did not test with highlighted test because somehow I knew that this did
not cause any problem.

I guess it would be important to know how what Word considers to be a
sentence. From what I could tell, it was all the characters between
two periods, or all the characters on one line that had no periods.

If a paragraph has no period (or Any !, ? etc.) then the whole paragraph
gets selected, not just the line.
Watch out for stuff like "Text?Text". If the cursor is in eh text before the
"?", the stuff after the "?" gets selected...

What I'm trying to accomplish is a means to easily bookmark text from
a Software Requirements Document which then gets exported into a
spreadsheet for tracking verification tests. I want to export the
entire sentence when you just click on the sentence, but only the
highlighted words when words get selected. I don't think I can use
paragraphs, since one paragraph may contain multiple requirements that
should be bookmarked individually.

For debug purposes, I msgbox the sentence or selected text that I have
just bookmarked and it looks perfect. It seems that something is
being lost in the OLE link...the way Excel brings in the text.

If .Start <> .End (which means I have highlighted some text), then I
always get the correct text in Excel.

I wonder, could .Start ever be greater than .End? What would that do?

No, but do you have tables? This could cause problems.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org