CODEWITHSUNDEEP
vbams-wordcopy-paste
default
default

Reputation: 11635

Copy charts from excel to word - indeterministic behaviour

I am currently copying a lot of diagrams from excel to word via macro. I used the Record Macro functionality which helped me to produce the following code:

Set charts = Sheets("Charts").ChartObjects
For Each chart In charts
    WordApplication.Selection.TypeParagraph
    WordApplication.ActiveDocument.Tables.Add Range:=WordApplication.Selection.Range, NumRows:=2, NumColumns:= _
        1, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
    With WordApplication.Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = False
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = False
        .ApplyStyleRowBands = True
        .ApplyStyleColumnBands = False
    End With
    WordApplication.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
    chart.Copy
    WordApplication.Selection.Paste


    WordApplication.Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    ' configure the shape (resizing)
    WordApplication.Selection.MoveDown Unit:=wdLine, Count:=2
Next

So what I do is, put a Return, add a table with 2 rows and align the first row to Center. Then add the chart by copying it from Excel and pasting it into Word. Do some tinkering with the shape (removed) by selecting it (via the MoveLeft command) and finally, move 2 steps down (to leave the table) and redo for all the charts.

If I step through this with F8 I get the result I want. However, if I just let it run I see different result all the time, for instance:

My question:
How can I make it work without having to step through the macro manually?

Using Windows XP, Excel 2007 (12.0.65.62.5003). Note that the issue does not behave the same on Windows 7 (not tested on Windows Vista).

Upvotes: 0

Views: 2082

Answers (1)

default
default

Reputation: 11635

It seems the last line didn't always leave the table that I inserted. I replaced the following line:

WordApplication.Selection.MoveDown Unit:=wdLine, Count:=2

with this

Do Until Not WordApplication.Selection.Information(wdWithInTable)
    WordApplication.Selection.MoveRight Unit:=wdCharacter, Count:=1
Loop

And now it works as it should

Upvotes: 1

Related Questions