CODEWITHSUNDEEP
vbaexcel
user2082695
user2082695

Reputation: 250

Macro to copy chart Excel-Word changes chart appearance

I copy-paste charts from Excel (2010) as images in Word. The copy-paste works but the final chart in Word is different than the original and what I copy-paste manually (Paste special - Picture).

I have tried to change the DataType in the Paste and the Format in the copy but the result is always the same. Any other option?

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:=xlBitmap


    WDApp.Selection.Range.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, _
    Placement:=wdInLine, DisplayAsIcon:=False


   WDApp.Selection.MoveEnd wdStory
   WDApp.Selection.Move
Next
WDDoc.SaveAs ("C:\Temp\charts.docx")
WDDoc.Close ' close the document

' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing

End Sub

Upvotes: 0

Views: 814

Answers (1)

ThunderFrame
ThunderFrame

Reputation: 9461

Try copying the ChartObject with the Copy Method

ActiveSheet.ChartObjects(iCht).Copy

And try pasting as a Bitmap or PNG:

WDApp.Selection.Range.PasteSpecial Link:=False, DataType:=wdPasteBitmap, _
Placement:=wdInLine, DisplayAsIcon:=False

WDApp.Selection.range.PasteSpecial Link:=False, DataType:=14, Placement:=wdInLine, _
DisplayAsIcon:=False

Upvotes: 1

Related Questions