Macro for format the Picture size in a word.

[sreekhosh]

Hi,

How to format the Height and width of the chart. (chart is pasting from excel to word).

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
WDApp.Visible = True

For iCht = 1 To ActiveSheet.ChartObjects.Count
   
    With ActiveSheet.ChartObjects(iCht).chart
    On Error GoTo Line00:
    ActiveSheet.ChartObjects(iCht).Select
    ActiveChart.ChartArea.Copy
    End With
     WDApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
    Placement:=wdInLine, DisplayAsIcon:=False
 WDApp.Selection.ShapeRange.Height = CentimetersToPoints(1.5)
 WDDoc.Content.InsertParagraphAfter
 WDApp.Selection.InsertBreak Type:=wdPageBreak


Next

Line00:




    Set WDDoc = Nothing
    Set WDApp = Nothing

End Sub

Regards

Sreekhsoh

 

[Deepak]

Check This...

Option Explicit

Sub ChartsToWord()

Dim WDApp As Object
Dim WDDoc As Object
Dim iCht As Integer
Dim Msg As String

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

For iCht = 1 To ActiveSheet.ChartObjects.Count
  
    With ActiveSheet.ChartObjects(iCht).Chart
        On Error GoTo Line00:
        ActiveSheet.ChartObjects(iCht).Select
   ' Application.ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
        ActiveChart.CopyPicture ' ChartArea.Copy
    End With
  
    WDApp.Selection.Paste 'Special Link:=False, DataType:=wdPasteMetafilePicture,
    
    ' WDApp.Selection.ShapeRange.LockAspectRatio = True
    With WDApp.ActiveDocument
        .InlineShapes(iCht).Height = 200
        .InlineShapes(iCht).Width = 200
    End With

Next

Line00:

    Set WDDoc = Nothing
    Set WDApp = Nothing

End Sub

 

[sreekhosh]

Hi Deepak,

Its works fine for me. thanks a ton

Sreekhosh.AP