CODEWITHSUNDEEP
excelms-wordvba
Jakob
Jakob

Reputation: 4854

copy multiple charts to word document

I'm trying to copy a series of charts in one sheet to one document in word, but for some reason I only get the latest paste (meaning the last chart on the sheet). I know that the iteration goes through all charts, becausewhen I modofiy the code to print a single word doc for each chart it does so, but I want the charts together, so please help me out

The code:

Sub ChartsToWord()

Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Dim iCht As Integer
Dim Msg As String

Set WDApp = CreateObject("Word.Application")
Set WDDoc = WDApp.Documents.Add

For iCht = 1 To ActiveSheet.ChartObjects.Count
    ' copy chart as a picture
    ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
        Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture


    WDDoc.Content.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
        Placement:=wdInLine, DisplayAsIcon:=False

    WDDoc.Content.InsertParagraphAfter
Next
WDDoc.SaveAs ("C:\Users\confidential\Documents\charts.doc")
    WDDoc.Close ' close the document

' Clean up
    Set WDDoc = Nothing
    Set WDApp = Nothing

End Sub

Upvotes: 2

Views: 9212

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Please replace beginning of PasteSpecial line into:

WDApp.Selection.Range.PasteSpecial ... 'and so on

In your situation you paste chart into whole document instead of current paragraph.

One more suggestion. You could use the following to insert new paragraph:

WDApp.Selection.MoveEnd wdStory
WDApp.Selection.Move

Upvotes: 4

Related Questions