# Finding TOC reference Style in Word from Excel



## PappySki (Oct 28, 2016)

I have an Excel file that will process multiple Word files looking for comments. What I need Excel to do when it finds a comment is to search in reverse from the comment location to the first use of the style "Heading n" (could be Heading 1, Heading 2, Heading 3, etc...). When it finds this Heading-n style, it needs to copy all of the text that is formatted as Heading-n to the excel file.


----------



## Macropod (Oct 29, 2016)

A word macro you could integrate into your code is:

```
Sub Demo()
Dim Cmnt As Word.Comment, i As Long, j As Long
With Word.ActiveDocument
  j = 0
  For Each Cmnt In .Comments
    For i = .Range(j, Cmnt.Reference.Paragraphs(1).Range.End).Paragraphs.Count To 1 Step -1
      With .Paragraphs(i)
        If InStr(.Style, "Heading ") = 1 Then
          MsgBox Cmnt.Range.Text & vbCr & .Range.Text
          j = .Range.Start
          Exit For
        End If
      End With
    Next
  Next
End With
End Sub
```


----------



## PappySki (Nov 7, 2016)

Below is what I have so far, but it doesn't work as planned. Two problems:

1. In reference to the line number for where the comment is located: Everytime a heading is found, the line numbering starts over from there
2. The macro isn't actually providing the section info

------------CODE--------------

'This gives the line number that the selected text appears on and if it goes across multiple lines it presents that as well
'.Scope is the range object of the whole selected text, whereas .Reference is the range object of just the last character
                txtLine = cmt.Scope.Information(10) ' wdFirstCharacterLineNumber = 10
                txtLine2 = cmt.Reference.Information(10) ' wdFirstCharacterLineNumber = 10
                If txtLine <> txtLine2 Then txtLine = txtLine & "-" & txtLine2

'This should provide the formatted paragraph #, i.e. 3.2.1.3.4 or whatever
'first must check if the comment actually falls on the numbered item
                If cmt.Scope.ListFormat.listType >= 3 Then '3 is simple numbering, 4 is outline, 5 = mixed numbering 'Still may not work perfectly in all instances of comments
                    txtPar = cmt.Scope.ListFormat.ListString
                Else ' Needs to back up & find the previous numbered outline level
                    txtPar = cmt.Scope.GoToPrevious(11).ListFormat.ListString 'wdGoToHeading = 11 'Again not sure it always works
                End If


----------



## Macropod (Nov 7, 2016)

Your previous post said you wanted to get the text associated with the previous heading, so that's what I coded for. You made no mention of getting its numbering (ListString) instead or of not always wanting to get the heading details.

As for the line numbering, line numbers in Word start from 1 on each page. Headings are of no particular relevance in that regard. If you want to know what lines a range spans, you could use a Function such as:

```
Function GetLines(Rng As Word.Range) As String
'Const wdFirstCharacterLineNumber As Long = 10
With Rng
  GetLines = .Information(wdFirstCharacterLineNumber)
  If GetLines <> .Characters.Last.Information(wdFirstCharacterLineNumber) Then _
  GetLines = GetLines & "-" & .Characters.Last.Information(wdFirstCharacterLineNumber)
End With
End Function
```
Accordingly, you could use code like the following to retrieve a range of data about the comments, including page & line #s, list numbering, comment text, scope text, associated heading & text:

```
Private Sub GetDocData(wdDoc As Word.Document)
Dim Cmnt As Word.Comment, StrScope As String, StrTxt As String
'Const wdGoToHeading As Long = 11
'Const wdActiveEndAdjustedPageNumber As Long = 1
With wdDoc
  For Each Cmnt In .Comments
    With Cmnt.Scope
      StrScope = "Page: " & .Duplicate.Information(wdActiveEndAdjustedPageNumber) & ", Lines: " & _
        GetLines(.Duplicate) & vbCr & .Text & vbCr & vbCr & "Comment: "
      'With list numbering: 0 = List with no bullets, numbering, or outlining; 1 = ListNum field
      '2 = Bulleted list; 3 = simple numbering; 4 = outline; 5 = mixed numbering; 6 = picture bullet
      Select Case .ListFormat.ListType
        Case 3 To 5
          StrTxt = .ListFormat.ListString & " " & .Paragraphs(1).Range.Text
        Case Else ' Needs to back up & find the previous heading
          With .GoToPrevious(wdGoToHeading)
            StrTxt = "Heading: " & .ListFormat.ListString & " " & .Paragraphs(1).Range.Text
          End With
      End Select
    End With
    StrScope = StrScope & Cmnt.Range.Text
    MsgBox StrTxt & vbCr & StrScope
  Next
End With
End Sub
```
which you might test in Word with a sub as simple as:

```
Sub Test()
GetDocData ActiveDocument
End Sub
```
For use with late binding, simply change 'Word' in the above Sub & Function to whatever other variable name you've assigned to the Word application and uncomment the Const expressions.

Note that, as yet, the GetDocData sub only outputs a message box display; I have no information about how you want to process the output.

As for your (2), it's not clear what you mean about a section number. In Word, a Section is a range delimited via Section breaks at either end (except for the first and last Sections).


----------



## PappySki (Nov 9, 2016)

When I said section info, I was referring to text in Word formatted with the Heading Style. Any text formatted as a heading can be easily added to the Table of Contents and is included in the Navigation Window. As for the line numbering, it is something I was trying since I couldn't get the section info to be captured by Excel. In my perfect little world, the VBA script, run from Excel, would capture the comments in a Word document and identify the Header info and paste it to the Excel file. In this way, I could sort my Excel file by this header info (example 8.a.1 or 1.1.1) and see all of the comments by all of the reviewers associated with each portion/section of the file.


----------



## Macropod (Nov 9, 2016)

OK, but did you try the code I posted?


----------

