Excel Hints

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 23

Thread: Activedocument.Footnotes.Reference.Text

  1. #1

    Question Activedocument.Footnotes.Reference.Text

    Hi Everyone,

    I have a bit of a problem for which I'd like some input from the community. I have created a VBA macro in Word (2000, 2003, 2007 respond in the same manner) that requires me to obtain pre-existing reference mark values of footnotes and endnotes. When I attempt to obtain the actual text of the reference mark using "ActiveDocument.Footnotes.Reference.Text" I am given a square(!) -- presumably because Word cannot store/display the proper reference mark text -- whereas I am expecting a footnote numeral (e.g. "1" or "i" or "a").

    Has anyone else experienced this, and does a work-around exist?

    Here is an example that gives me a square instead of the proper "1" text value:

    [vba]
    Call ActiveDocument.Footnotes.Add(Selection.Range, , "Footnote text")
    MsgBox ActiveDocument.Footnotes.Item(1).Reference.Text
    [/vba]
    Thanks for any insight!

  2. #2
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    I think it's a range.
    [VBA]MsgBox ActiveDocument.Footnotes.Item(1).Range.Text[/VBA]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    Quote Originally Posted by lucas
    I think it's a range.
    [vba]MsgBox ActiveDocument.Footnotes.Item(1).Range.Text[/vba]
    Thanks for the quick reply.

    .Range will return the text of the footnote, not the reference mark number of the footnote.
    Last edited by dorogoi; 03-29-2010 at 03:52 PM.

  4. #4
    Is this it?

    [VBA]
    ActiveDocument.Footnotes (1).Range.FootnoteOptions.StartingNumber
    [/VBA]

    Paul

  5. #5

    Not it

    Quote Originally Posted by Paul_Hossler
    Is this it?

    [vba]
    ActiveDocument.Footnotes (1).Range.FootnoteOptions.StartingNumber
    [/vba]
    Paul

    Thanks for the ideas, but, unfortunately, no, that's not it. This will only provide the ordinal value of the first reference.

    For each foot/endnote in the Footnotes or Endnotes collection, it is supposed to be able to return its "reference mark".

    I.e. If footnotes are using roman numerals, and one is iterating through each footnote item in the footnotes collection, the footnotes object's ".reference.text" is ~supposed~ to return "i", "ii", "iii" etc. If the footnotes are using lower case letters it should return "a", "b", "c". A programmer should not have to "calculate" the proper value.

    I am getting a lovely yet completely useless square.

  6. #6
    Is there anyone out there that isn't receiving a square in the msgbox?

  7. #7
    Hi dorogoi,

    Try something along the lines of:
    Sub Test()
    Dim oRng As Range
    Set oRng = Selection.Range
    With oRng
      .Start = .End
      .Footnotes.Add oRng, , "Footnote text"
      .MoveEnd wdCharacter, 1
      MsgBox "The footnote you just inserted is # " & .Footnotes(1).Index & "."
    End With
    End Sub
    Cheers
    Paul Edstein
    [MS MVP - Word]

  8. #8
    Not sure if it means anything, but I think the box that is being displayed ...

    MsgBox ActiveDocument.Footnotes.Item(1).Reference.Text
    ... is part of the internal Word document structure. It might the the 'marker' for a Foot note (ASCII 2 I think)

    I did a little test. Put 3 footnotes in a doc (lower case roman numeals), and looped thru them

    #1 - was .Index 1
    #2 - was .Index 2
    #3 - was .Index 3

    Then I added a new footnote before #1, and they renumbered 1-4 as expected

    Looped through again

    New one - was .Index 1
    #1 - was .Index 2
    #2 - was .Index 3
    #3 - was .Index 4


    I think you're right --MS made it pretty hard to retreive the footnote number. I guess it's because it has to calculate them on the fly

    Hopefully some much more experienced person has something better, because the only thing I can up with is UGLY


    [vba]
    Option Explicit
    Sub ListFootnotes()
    Dim iNumber As Long
    Dim oFootnote As Footnote

    For Each oFootnote In ActiveDocument.Footnotes
    With oFootnote

    iNumber = .Range.FootnoteOptions.StartingNumber + .Index - 1

    Select Case .Range.FootnoteOptions.NumberStyle
    'only format I did
    Case wdNoteNumberStyleLowercaseRoman
    'brute force
    Select Case iNumber
    Case 1
    MsgBox .Index & " -- " & .Range.Text & " -- " & "i"
    Case 2
    MsgBox .Index & " -- " & .Range.Text & " -- " & "ii"
    Case 3
    MsgBox .Index & " -- " & .Range.Text & " -- " & "iii"
    Case 4
    MsgBox .Index & " -- " & .Range.Text & " -- " & "iv"
    Case 5
    MsgBox .Index & " -- " & .Range.Text & " -- " & "v"
    End Select

    Case wdNoteNumberStyleUppercaseRoman
    Case wdNoteNumberStyleUppercaseLetter
    Case wdNoteNumberStyleSymbol
    Case wdNoteNumberStyleLowercaseLetter
    Case wdNoteNumberStyleArabic
    End Select



    End With
    Next
    End Sub
    [/vba]


    Lots and lots of luck

    Paul

  9. #9
    Quote Originally Posted by Paul_Hossler
    MS made it pretty hard to retreive the footnote number.
    Somehow I don't think so - check out the code in my last post. Not exactly difficult IMHO.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  10. #10
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Quote Originally Posted by dorogoi
    Is there anyone out there that isn't receiving a square in the msgbox?
    Using you code from post #1, I get no square

    I get an empty msgbox

    macropod's code at post 7 gives me the number of the last footnote added.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  11. #11
    Absolutely correct - Mac's works to give the last foot note added

    I was (possibly) misunderstanding the OP's comment ...

    that requires me to obtain pre-existing reference mark values of footnotes and endnotes.
    ... to mean that they were looking for the reference number for ANY footnote, not just the last one added.

    Can the code be generalized to work with any pre-existing footnotes?

    Paul

  12. #12
    Thanks Macropos, Paul_Hossler, and lucas for the great input.

    Firstly, the code I posted is to quickly and easily demonstrate that the .Reference.Text property is not working as intended/expected. Believe it or not, I was able to retrieve the reference mark value when I first started my project. I am not sure what the difference is yet, but I suspect it is possible to determine.

    Secondly, the heart of the matter. My intent is to convert an existing document's (created by a third party) foot/endnotes to regular text at the end of the document, maintaining the original reference marks precisely (i.e. superscript mark in-paragraph and at the beginning of the footnote text). There is potential for foot/endnote marks to be interlaced, upper/lower case letters/roman numerals and numbers -- with manual edits (renumbering) as well.

    It becomes very complicated when the numbering rule for footnotes is different than that of endnotes...especially when it can be per page, section etc etc. Reassigning reference marks is not an option, so I either need to create a routine that will A) precisely calculate the reference mark via its .Index, and the document's ActiveDocument.Footnotes.NumberRule, and ActiveDocument.Footnotes.NumberStyle properties, or b) use the handy-but-mostly-broken .Reference.Text property.

    .NumberRule is going to be the hard part as I will have to determine when the number resets, not my idea of fun.

    And all of the above does not take into account if there is a manually assigned value (i.e. maybe the original author skipped numbers 10 through 19 for some reason).

    Paul: I suspect you are correct. I believe that the "square" that I see is either a pointer of sorts, or perhaps an internal reserved value indicating that it should, indeed, be calculated on the fly. Yes, it is an ASCII value of 2 that I have here as well. If I assign .reference.text a string value of anything (i.e. "1"), it replaces the in-paragraph reference mark with a non-superscript value (i.e. 1) and deletes the foot/endnote entirely. If I assign it Chr(2), it replaces the in-paragraph mark with a square and deletes the foot/endnote. This indicates to me that its currently assigned Chr(2) value is special in some way.

    It still doesn't make sense, as most everything is calculated on the fly in any programming language, what is so unusual about this? (except that I need it to function properly)

    I just did some testing. Oddly enough, if the reference mark is a "custom mark", .Reference.Text returns the proper value (i.e. I created a footnote with a "$" as its mark, and it was properly returned via the .Reference.Text property).

  13. #13
    This indicates to me that its currently assigned Chr(2) value is special in some way.
    Word uses a lot of special 'markers' that are not displayed or only partial displayed. For example, the paragraph mark symbol displays as a single character, but there's a lot of information there (style, section information, header/footer, etc.)

    On the Document (not VBE) side, if you look for Edit in the Help, and then go to "Find and Replace Text or other Items" you can see some of the internal values (you can search for them, but there are rules: wild cards on/off / In find box / In replace box)

    ^2 is a Footnote, ^19 and ^21 are Fields,^5 is a Comment, ^14 is a column break. Some have ^letter equivalents, but internally I think Word marks things with a ASCII 2 or ACSII 19.

    Paul

  14. #14
    Little more experimenting -- simple doc with 6 footnotes

    ran little macro

    First msgbox was index (1-6) and the text of the correspnding footnote
    Second msg box was the square and the number 2 (the ascii value of the square)

    [VBA]
    Option Explicit
    Sub ListFootnotes()

    Dim oFootnote As Footnote

    For Each oFootnote In ActiveDocument.Footnotes
    With oFootnote

    MsgBox .Index & " - " & .Range.Text

    'this displays box and '2' the value of the footnote marker
    MsgBox .Reference.Text & " -- " & Asc(.Reference.Text)



    End With
    Next
    End Sub
    [/VBA]

    Interesting

    Hope you get it figured out

    Paul

  15. #15
    Hi dorogoi,
    My intent is to convert an existing document's (created by a third party) foot/endnotes to regular text at the end of the document, maintaining the original reference marks precisely (i.e. superscript mark in-paragraph and at the beginning of the footnote text).
    For converting endnotes, you could use code like:
    Sub UnLinkEndNotes()
    Dim eRng As Range, eNote As Endnote, i As Integer
    With ActiveDocument
      For Each eNote In .Endnotes
        With eNote
          i = .Index
          With .Reference.Characters.Last
            .InsertBefore i
            .Style = "Endnote Reference"
          End With
          .Range.Cut
        End With
        Set eRng = .Range
        With eRng
          .InsertAfter vbCr & i & " - "
          .Start = .End
          .Paste
          .Style = "Endnote Text"
        End With
      Next
      For Each eNote In .Endnotes
        eNote.Delete
      Next
    End With
    End Sub
    With minor changes, the code can process footnotes instead. Nonetheless, footnotes will be inserted at the end of the document - programatically inserting them at the foot of each page is problematic, especially because Word uses the current printer driver to work out where the automatic page breaks should occur. Change printers and you could end up with page breaks occuring in a different position.
    There is potential for foot/endnote marks to be interlaced, upper/lower case letters/roman numerals and numbers -- with manual edits (renumbering) as well.
    Provided you convert the footnotes and endnotes separately, the fact they're interspersed within the document shouldn't matter. A limitation of the above macro, though, is that it doesn't handle the different numbering schemes that usually entails - for whichever references require it, you would need to do a bit of post-processing on the reference numbers to convert them to, say, roman numerals. The fact the macro preserves the corresponding Style names makes this fairly easy to do. An alternative is to change the footnotes to endnotes before running the macro, then process them all as a single set. I do note, though, your desire not to do this.
    And all of the above does not take into account if there is a manually assigned value (i.e. maybe the original author skipped numbers 10 through 19 for some reason).
    With Word, that's not a problem - simply because any re-starts & skipped ranges will be ignored by the macro. Usually, you can only re-start on a Section by Section basis. Again, the macro could be modofied to process each Section independently and to put the notes at the end of the Section rather than at the end of the document. What you might need to deal with, though, is footnote/endnote cross-references. I suggest dealing with those by converting them to plain text (eg by selecting them and pressing Ctrl-Shift-F9) before running the above macro.
    Last edited by macropod; 03-30-2010 at 04:55 PM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  16. #16
    Hi dorogoi,

    Here's an enhanced version of the macro, that faithfully replicates the referencing type (eg roman numerals):
    Sub UnLinkEndNotes()
    Application.ScreenUpdating = False
    Dim eRng As Range, eNote As Endnote, eRef As String
    With ActiveDocument
      For Each eNote In .Endnotes
        With eNote
          With .Reference.Characters.First
            .Collapse
            .InsertCrossReference wdRefTypeEndnote, wdEndnoteNumberFormatted, eNote.Index
            eRef = .Characters.First.Fields(1).Result
            .Characters.First.Fields(1).Unlink
          End With
          .Range.Cut
        End With
        Set eRng = .Range
        With eRng
          .InsertAfter vbCr & eRef & " - "
          .Start = .End
          .Paste
          .Style = "Endnote Text"
        End With
      Next
      For Each eNote In .Endnotes
        eNote.Delete
      Next
    End With
    Set eRng = Nothing
    Application.ScreenUpdating = True
    End Sub
    To get the reference type, I (simply?) inserted a cross-reference to the endnote and captured the cross-reference text before unlinking it.
    Last edited by macropod; 03-30-2010 at 05:06 PM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  17. #17

    Red face Most excellent!

    With minor changes, the code can process footnotes instead. Nonetheless, footnotes will be inserted at the end of the document - programatically inserting them at the foot of each page is problematic, especially because Word uses the current printer driver to work out where the automatic page breaks should occur. Change printers and you could end up with page breaks occuring in a different position.
    This is not a problem, all the references will be at the end of the document.

    Provided you convert the footnotes and endnotes separately, the fact they're interspersed within the document shouldn't matter. A limitation of the above macro, though, is that it doesn't handle the different numbering schemes that usually entails - for whichever references require it, you would need to do a bit of post-processing on the reference numbers to convert them to, say, roman numerals. The fact the macro preserves the corresponding Style names makes this fairly easy to do. An alternative is to change the footnotes to endnotes before running the macro, then process them all as a single set.
    I intend to process both separately (then assign a different color to the reference marks in case the author haphazardly decides to use the same numbering style for both foot and endnotes, so I suspect that it won't be an issue.


    Usually, you can only re-start on a Section by Section basis. Again, the macro could be modofied to process each Section independently and to put the notes at the end of the Section rather than at the end of the document.
    I don't think this will be necessary, but I know exactly what you mean. If numbering is restarted, there is no way to easily discriminate once the notes are at the end of the document. I wonder if I can pre-pend a (Section X or Page X) to each reference...but I'm not worried about that.

    What you might need to deal with, though, is footnote/endnote cross-references. I suggest dealing with those by converting them to plain text (eg by selecting them and pressing Ctrl-Shift-F9) before running the above macro.
    I am not sure to what you are referring. The resultant text appears to be plain text, which should be sufficient.

    There's a bit of tweaking I have to do, so I'm off to go testing. I'll be back in a little bit.

    Thank you very much macropod, and everyone that offered a helping hand.

  18. #18
    Please post the result so I can see how you did it

    Paul

  19. #19
    Quote Originally Posted by dorogoi
    Quote Originally Posted by macropod
    What you might need to deal with, though, is footnote/endnote cross-references. I suggest dealing with those by converting them to plain text (eg by selecting them and pressing Ctrl-Shift-F9) before running the above macro.
    I am not sure to what you are referring. The resultant text appears to be plain text, which should be sufficient.
    If the author insert a footnote (or endnote), then inserts a cross-reference to it, the cross-reference fields will still be in the document after the macro has done its thing. Once you do a print preview or print the document, though, all those now-invalid cross-references will update and you'll have a document littered with 'Error! Reference source not found.' messages instead.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  20. #20

    Thumbs up

    Quote Originally Posted by macropod
    If the author insert a footnote (or endnote), then inserts a cross-reference to it, the cross-reference fields will still be in the document after the macro has done its thing. Once you do a print preview or print the document, though, all those now-invalid cross-references will update and you'll have a document littered with 'Error! Reference source not found.' messages instead.
    Ah, right. That makes sense. I'm quite sure that the source text will never include a Word cross-reference, so I'm not worried about it at all.

    Here's my final VB code that color codes footnotes green, and endnotes as red in the unlikely event that the author uses the same .numberstyle for both:
    [VBA]
    Sub VBAXFootnote()
    '
    ' VBAXFootnote Macro
    ' Macro created 31/03/2010 by None
    '
    Application.ScreenUpdating = False
    Dim eRng As Range
    Dim fRng As Range
    Dim eNote As Endnote
    Dim fNote As Footnote
    Dim eRef As String

    'Foot Notes
    With ActiveDocument
    For Each fNote In .Footnotes
    With fNote
    With .Reference.Characters.First
    .Collapse
    .InsertCrossReference wdRefTypeFootnote, wdFootnoteNumberFormatted, fNote.Index
    eRef = .Characters.First.Fields(1).Result
    Selection.Start = fNote.Reference.Start - Len(eRef)
    Selection.End = fNote.Reference.Start
    Selection.Font.ColorIndex = wdGreen
    .Characters.First.Fields(1).Unlink
    End With
    .Range.Cut
    End With
    Set eRng = .Range
    With eRng
    .InsertAfter vbCr & eRef
    Selection.Start = .End - Len(eRef) - 1
    Selection.End = .End
    Selection.Font.Superscript = True
    Selection.Font.ColorIndex = wdGreen
    .Start = .End
    .Paste
    .Style = "Endnote Text"
    End With
    Next
    For Each fNote In .Footnotes
    fNote.Delete
    Next
    End With
    Set eRng = Nothing

    'End Notes
    With ActiveDocument
    For Each eNote In .Endnotes
    With eNote
    With .Reference.Characters.First
    .Collapse
    .InsertCrossReference wdRefTypeEndnote, wdEndnoteNumberFormatted, eNote.Index
    eRef = .Characters.First.Fields(1).Result
    Selection.Start = eNote.Reference.Start - Len(eRef)
    Selection.End = eNote.Reference.Start
    Selection.Font.ColorIndex = wdRed
    .Characters.First.Fields(1).Unlink
    End With
    .Range.Cut
    End With
    Set eRng = .Range
    With eRng
    .InsertAfter vbCr & eRef
    Selection.Start = .End - Len(eRef) - 1
    Selection.End = .End
    Selection.Font.Superscript = True
    Selection.Font.ColorIndex = wdRed
    .Start = .End
    .Paste
    .Style = "Endnote Text"
    End With
    Next
    For Each eNote In .Endnotes
    eNote.Delete
    Next
    End With
    Set eRng = Nothing
    Selection.Range.Start = 1
    Selection.Range.End = 1
    Application.ScreenUpdating = True

    End Sub
    [/VBA]

    A big thank you to everyone that helped.

Posting Permissions

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