I am having problems trying to insert a Excel chart into a Word document, from Excel using VBA. I have made a chart in Excel , and opened Word and added a new document. I can write text, but I can not insert the Excel chart just made.
Here is my VBA code from Excel:
Private Sub CommandButton1_Click()
Dim appWord As Word.Application
Dim objWchart As Excel.Chart
Dim objChart As ChartObject
'Create a simple chart in Excel
Worksheets("Sheet1".Cells(1, 1) = 42
Worksheets("Sheet1".Cells(1, 2) = 117
Set objChart = Worksheets("Sheet1".ChartObjects.Add(0, 25, 350, 200)
objChart.Activate
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1".Range("A1:B1", PlotBy:= _
xlColumns
'Start up Word
Set appWord = CreateObject("Word.Application"
appWord.Visible = True
appWord.Documents.Add
appWord.Selection.TypeText Text:="This is text written in the Word document"
appWord.Selection.TypeParagraph 'New line
'I WOULD LIKE TO WRITE CODE LIKE THE FOLLOWING CODE
Set objWChart = appWord.Selection.InlineShapes.AddOLEObject objChart
' OR
Set objWChart = appWord.Selection.InlineShapes.AddOLEObject(ClassType:="Excel.Chart.8", Filename:= _
"", LinkToFile:=False, DisplayAsIcon:=False)
objWChart = objChart
'Close down
appWord.Quit
Set appWord = Nothing
Set objWChart = Nothing
Set objChart = Nothing
End Sub
How do I set a chart in Word equal to the just made chart in Excel? I tried different and way and types like: Excel.chart, ChartObject, OLEObject and Chart.
Thanks
Henrik K-N
Here is my VBA code from Excel:
Private Sub CommandButton1_Click()
Dim appWord As Word.Application
Dim objWchart As Excel.Chart
Dim objChart As ChartObject
'Create a simple chart in Excel
Worksheets("Sheet1".Cells(1, 1) = 42
Worksheets("Sheet1".Cells(1, 2) = 117
Set objChart = Worksheets("Sheet1".ChartObjects.Add(0, 25, 350, 200)
objChart.Activate
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1".Range("A1:B1", PlotBy:= _
xlColumns
'Start up Word
Set appWord = CreateObject("Word.Application"
appWord.Visible = True
appWord.Documents.Add
appWord.Selection.TypeText Text:="This is text written in the Word document"
appWord.Selection.TypeParagraph 'New line
'I WOULD LIKE TO WRITE CODE LIKE THE FOLLOWING CODE
Set objWChart = appWord.Selection.InlineShapes.AddOLEObject objChart
' OR
Set objWChart = appWord.Selection.InlineShapes.AddOLEObject(ClassType:="Excel.Chart.8", Filename:= _
"", LinkToFile:=False, DisplayAsIcon:=False)
objWChart = objChart
'Close down
appWord.Quit
Set appWord = Nothing
Set objWChart = Nothing
Set objChart = Nothing
End Sub
How do I set a chart in Word equal to the just made chart in Excel? I tried different and way and types like: Excel.chart, ChartObject, OLEObject and Chart.
Thanks
Henrik K-N