Problem adding TOC to Word Document from Excel

[shaunk]

I have an Excel document with a series of graphs displayed that I would like to copy and paste to a Word document, and then insert a table of contents.
The code works perfectly, except for the line that adds the TOC. It is falling over with the message "Wrong number of arguments or invalid property assignment".

Code as follows

Sub MS_Word()

    Dim intChartCount As Integer
    Dim cht As Chart
    Dim strChartTitle As String
        
    'Create a Microsoft Word session
    Set wd = CreateObject("word.application")
       
    'Make document visible
    wd.Visible = True
    'Activate MS Word
    AppActivate wd.Name
   
    'Open a new document in Microsoft Word
    wd.Documents.Add
    wd.Selection.Style = wd.ActiveDocument.Styles("Heading 3")
    wd.Selection.TypeParagraph
    
    intChartCount = Worksheets("Chart").ChartObjects.Count
    For Counter = 1 To intChartCount
        Worksheets("Chart").ChartObjects(Counter).Chart.ChartArea.Copy
        strChartTitle = Worksheets("Chart").ChartObjects(Counter).Chart.ChartTitle.Text
        With wd
            .Selection.Style = wd.ActiveDocument.Styles("Heading 3")
            'Insert the Chart title
            .Selection.TypeText Text:=strChartTitle
            .Selection.TypeParagraph
            'Paste the chart
            .Selection.PasteSpecial link:=False, DisplayAsIcon:=False, Placement:=wdInLine
            ' Insert spacing
            .Selection.TypeParagraph
            .Selection.TypeParagraph
            .Selection.TypeParagraph
        End With
    Next Counter
    
    wd.Selection.WholeStory
    wd.Selection.HomeKey
    With wd
        .TablesOfContents.Add Range:=Selection.Range, RightAlignPageNumbers:= _
        True, UseHeadingStyles:=False, IncludePageNumbers:=True, AddedStyles _
        :="", UseHyperlinks:=True, HidePageNumbersInWeb:=True
        
        .TablesOfContents(1).TabLeader = wdTabLeaderDots
        .TablesOfContents.Format = wdIndexIndent
    End With
    
   wd.ActiveDocument.SaveAs Filename:="Charts.doc", FileFormat:=wdFormatDocument, _
        LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
        :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
        SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
        False
    
   Application.Worksheets("Chart").Activate
    
   Set wd = Nothing
End Sub

Any help much appreciated.

 

[fumei]

Try:

With wd.[COLOR=red]ActiveDocument[/color red]
        .TablesOfContents.Add Range:=Selection.Range, RightAlignPageNumbers:= _
        True, UseHeadingStyles:=False, IncludePageNumbers:=True, AddedStyles _
        :="", UseHyperlinks:=True, HidePageNumbersInWeb:=True    
        .TablesOfContents(1).TabLeader = wdTabLeaderDots
        .TablesOfContents.Format = wdIndexIndent
    End With

wd is the application object. You should use the document explicitly.

May I make some comments?

wd.Selection.WholeStory
wd.Selection.HomeKey

does contrary operations.

Selection.WholeStory - selects the entire document
Selection.HomeKey - collapses the Selection to the first character. There does not seem much point in selecting the entire document, then immediately unselecting it.

Using the Selection for everything is not a great idea. Plus it can be done with less code. For example:

With wd
  .Selection.Style = wd.ActiveDocument.Styles("Heading 3")
  'Insert the Chart title
  .Selection.TypeText Text:=strChartTitle
  .Selection.TypeParagraph
  'Paste the chart
  .Selection.PasteSpecial link:=False, _
      DisplayAsIcon:=False, Placement:=wdInLine
  ' Insert spacing
  .Selection.TypeParagraph
  .Selection.TypeParagraph
  .Selection.TypeParagraph
End With

Could be done as:

With wd.ActiveDocument
  With Selection
    .Style = wd.ActiveDocument.Styles("Heading 3")
    'Insert the Chart title
    .TypeText Text:=strChartTitle & vbCrLf
    'Paste the chart
    .PasteSpecial link:=False, DisplayAsIcon:=False, _
         Placement:=wdInLine
    ' Insert spacing
    .TypeText Text:= vbCrLf & vbCrLf & vbCrLf
  End With
End With

The vbCrLf is equivalent to the TypeParagraph.

Lastly, using paragraphs to make spaces is not a good use of Word. It would be much better to use a Style. That is what Styles are for. The space between paragraphs can be built right into the style.


Gerry
My paintings and sculpture

 

[PHV]

Another qualification problem:
.TablesOfContents.Add Range:=[!]wd.[/!]Selection.Range, RightAlignPageNumbers:= _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

You could also use a Document object

Dim oDoc As Word.Document
Set wd = CreateObject("word.application")
       
    'Make document visible
    wd.Visible = True
    'Activate MS Word
    AppActivate wd.Name
   
    'Open a new document in Microsoft Word
    Set oDoc = wd.Documents.Add

Now you can use oDoc.Selection, or oDoc.TableofContents, etc.

In any case, you always need to qualify your objects when using instances of other applications.

One of the common ones working with Word from Excel is Range.

From Excel, even after a Word instance has been created,

Dim oRange As Range

will not make a Word Range. It would make an Excel Range. You would need to do:

Dim oRange As Word.Range

Or whatever name you used for the instance of Word.

Dim oRange As wd.Range

for example, if you are using late-binding.

Gerry
My paintings and sculpture

 

[shaunk]

Apologies for not responding...must be something wrong in my email notification.

I will look at the recommended mods and make adjustments.