automatically create an index in word using vba

[sakura.saki]

Hi, I have exported about 30 excel tables into a new word document and above each table there is a text line which is the name of them. I can do manually to create an index for them, showing which table on which page,but it was a lot of work.

so does anyone know if and how I can have a code to let the index done by the vba code? I have all the names for the index above the tables, and I just need a code to tell the computer to find the texts which should be the index names and show the corresponding page numbers.I really know little about vba code so I need some help here...

I really appreciate any help!thanks in advance!

[macropod]

Do you want an Index, or a Table of Contents? To create a Table of Contents, basically all you need to do is to apply a Heading Style to the heading paragraphs, then insert a TOC field into the document.

[sakura.saki]

Hi Macropod, thanks for the reply. I don´t really understand the difference between the two, but I can explain:I would like to have an index showing the page numbers of my tables.the reason is there are too many tables and it is difficult to find. it doen´t matter how it looks like. the only important thing is the information shown. But somehow I found a way without VBA to do it. but if you could give me sone hint how to do it with the vba code, I would appreciate it.

[macropod]

Try the following macro:
Sub Demo()
Dim Tbl As Table
With ActiveDocument
For Each Tbl In .Tables
Tbl.Range.Characters.First.Previous.Paragraphs.First.Range.Style = "Heading 1"
Next
.TablesOfContents.Add Range:=.Range(0, 0)
End With
End Sub
You may need to play around with the Heading1 Style and Table of Contents layout afterwards, but it should get you well & truly started.

[sakura.saki]

thanks a lot macropod! I will try it later. :)

[fumei]

Paul's code makes a Table of Contents. You did not answer if that is what you want. As a possible alternative, here is code that:

1. Marks the paragraph before each table as an Index entry, using the text of that paragraph. From your post it appears this is the filename, but in case it uses the text of the paragraph before each table.

2. Goes to the end of the document, makes a new Section (as a new page), puts in some text as a title/heading ("Table List").

3. makes an Index of two c0olums with the Index entries and page that entry is on.
Sub DemoIndex()
Dim Tbl As Table
Dim r As Range
With ActiveDocument
' for each table
For Each Tbl In .Tables
' set range for paragraph before the table
Set r = Tbl.Range.Characters.First.Previous.Paragraphs.First.Range
' use text that pparagraph for Index entry
.Indexes.MarkEntry Range:=r, _
Entry:=r.Text, _
EntryAutoText:=r.Text, _
CrossReference:="", CrossReferenceAutoText:="", _
BookmarkName:="", Bold:=False, Italic:=False, Reading:=""
Set r = Nothing
' go to next table
Next
' go to end of document and make a new section
' put in text for a title/heading
With Selection
.EndKey Unit:=wdStory
.InsertBreak Type:=wdSectionBreakNextPage
.TypeText "Table List" & vbCrLf & vbCrLf
End With
' create Index listing
.Indexes.Add Range:=Selection.Range, HeadingSeparator:= _
wdHeadingSeparatorNone, Type:=wdIndexIndent, RightAlignPageNumbers:= _
False, NumberOfColumns:=2, IndexLanguage:=wdEnglishCanadian
.Indexes(1).TabLeader = wdTabLeaderDots
End With
End Sub

Press Ctrl-G to execute the macro demo.

[macropod]

Mine's simpler :whistle:

[fumei]

Simpler? Yes, I suppose. However, it is rather different, yes? What if the document has (or needs) an actual ToC? The OP is asking for an index of the tables, and my code does just that, and only that. Your code makes a Table of Contents by changing the paragraph style.

Again, what if there is a paragraph that is using Heading 1 elsewhere in the document? That would be included along with the table pointer.

I am not saying my suggestion is better, just an alternative. It creates an true index of only the tables.

[Frosty]

Fight, fight! ;)

What neither of you considered is whether the OP uses empty paragraphs to create "space" between the text which is a table title and the actual table.

:P

[macropod]

Actually, I did consider it. But, as the OP says only "above each table there is a text line which is the name of them", I decided not to code for something not specified. Gerry might have considered it too - and dismissed it for the same reasons - or he may have simply taken my code as his starting point (after all, why reinvent the wheel).

And yes, I changed the paragraph Style, but even that might not have been necessary - it was primarily done as a demonstration of how the project might be tackled. I have no emotional investment in what choice the OP makes - both approaches are equally valid. I was just giving Gerry a :razz: and he took the bait.

[sakura.saki]

Hi Fumei, thanks for your kind and thoughtful help!
it is a beautiful piece of code indeed. I will study it.
don´t take it so seriously . I know you guys are just trying to help and learn from each other and the forum is a great place to make it happen.

thanks again very much!wish you all the best!:yes

[sakura.saki]

thanks macropod. thank you. just take it easy :-)

[fumei]

hook line and sinker....

And yes, I did consider there may be other empty paragraphs between the paragraph in question and the table, and I left it alone for the same reason Paul did.

[sakura.saki]

Hi Macropod, I do have another quesition for you. I don´t know if you know the excel as well? I want to use the code you provide for the word, which is created from an excel code ,it creates a new word and copy the tables from excel sheet into the word created, but i copied your code into the code I have in excel, somehow it doens´t do the job it did when it was in word... so how to change it a little so that the same procedure could be done directly from excel? and what if I want to add a title for the documents above all the tables and the index as well? how can I rewrite the orinigal code a little, so that the index appear after the title?
here are my code in excel :

Public Sub CreateNewWordDoc() ' this is for the end product: the word report
' add a reference to the Word-library
Dim wrdapp As word.Application
Dim wrdDoc As word.Document
Dim wrdTable As word.Table
Dim osec As word.Section
Dim HeadPic As InlineShape
Dim xrange As Range
Dim HeadPicPath As String

Dim i As Integer
Set wrdapp = CreateObject("Word.Application")
Set wrdDoc = wrdapp.Documents.Add ' create a new document
Set wrdRange = wrdDoc.Range
wrdapp.Visible = True

With wrdapp ' add a title
With .Selection
.Font.Size = 16
.TypeText Text:="Weekly Report" & " " & Sheets("Data").Range("E2").Value & "-" & Sheets("Data").Range("F2").Value
.TypeParagraph
End With
End With
With wrdapp
With .Selection
.Font.Size = 12
End With
End With
Sheets("excelReady").Select
For j = 1 To 27
Set wrdRange = wrdDoc.Range
With wrdRange
.Collapse Direction:=wdCollapseEnd
.InsertParagraphAfter
.InsertAfter Worksheets("excelReady").Cells(51, j).Value
.Collapse Direction:=wdCollapseEnd
End With

'Create table
'Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=38, NumColumns:=1)
Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=Sheets("excelReady").Range("A32767").Offset(0, j - 1).End(xlUp).Row - 49, NumColumns:=1)
With wrdTable

'For i = 0 To 38
For i = 0 To Sheets("excelReady").Range("A65536").Offset(0, j - 1).End(xlUp).Row - 49
With .Cell(i + 1, 1).Range
.InsertAfter Worksheets("excelReady").Cells(50, j).Offset(i, 0).Value
End With
Next i

End With
Next j

''''''''''''''''''
' from here is the code which works perfect in word but got ignored in excel:
Dim Tbl As Table
With ActiveDocument
For Each Tbl In .Tables
Tbl.Range.Characters.First.Previous.Paragraphs.First.Range.Style = "Heading 1"
Next
.TablesOfContents.Add Range:=.Range(0, 0)
End With
'''''''''''''''''''
End Sub

[macropod]

You would need to use:
Dim Tbl As Word.Table
With wrdDoc
For Each Tbl In .Tables
Tbl.Range.Characters.First.Previous.Paragraphs.First.Range.Style = "Heading 1"
Next
.TablesOfContents.Add Range:=.Range(0, 0)
End With

[sakura.saki]

you are really en expert... so if I ask you another question please don´t be mad... is there a method to make the title line above the index created? as shown in the code above, it creates a title, but the index seems to appear above everything, even the title.

[macropod]

I'm not sure I understand your question. Perhaps you want something like:
.Range(0, 0).InsertBefore "Some Text" & vbcr

[fumei]

ooo, yeah, simpler.