Excel Chart to Word..

[IkEcht]

Hi all,

probably a stupid question, but I'm more used to Excel VBA then Word VBA.

I'm trying to fill a word document with data from several sources. All is well when it comes to most data, that I paste at bookmarks within the document. Alas this doesn't seem to work for excel charts that I want to copy (as a bitmap preferably, but any other format that keeps the formatting without copying the whole workbook will do).

Is there a default way to pull a chart from excell (on a special chart-sheet within the excel-document) and paste it a bookmark within the word doc, using word-vba?

Thanks a lot,
Jaap

[lucas]

From a search of our knowledgebase

http://www.vbaexpress.com/kb/getarticle.php?kb_id=133

[IkEcht]

Thanks Lucas, but this example copies from excel to word, using Excel VBA.
I want to use Word VBA to reach the same goal.
Might be an easy translation from one to the other, but I don't know of it.

[fumei]

1. have Excel reference so you can use early binding
2. create the Excel instance and use it to open the Excel file
3. grab the range data
4. paste it at the Word bookmark.

[IkEcht]

Somehow I can't grab the chartarea when I try:

xl.sheets("blabla").chartarea.select

I get the error message that the method select of the chartarea fails.


also other attempts to get the program to work fail.. not that I expected it to work, but I even tried:
ActiveDocument.Bookmarks("vaakfietsen").Range = xl.Sheets("vaakfietsen").ChartArea.PasteSpecial(DataType:=wdPasteBitmap)


Will I have to refrain to using macrobuttons instead of bookmarks to get it to work? I hope not, I'm probably messing up on something simpel. But who can tell me what?

Just for the full picture here is the full code:

Option Explicit
Sub vullen()
Dim xl As Excel.Application
Dim gemeente As String

gemeente = InputBox("Van welke gemeente wil u een rapport maken?", "gemeente invoeren")

ActiveDocument.SaveAs ("X:\evaluatierapport " & gemeente & ".doc")

Set xl = CreateObject("Excel.Application")
xl.workbooks.Open "X:\input " & gemeente & ".xls"

ActiveDocument.Bookmarks("gemeente").Range.Text = gemeente
ActiveDocument.Bookmarks("datum").Range.Text = xl.Sheets("output").Range("B2")
ActiveDocument.Bookmarks("gemleeftijd").Range.Text = xl.Sheets("output").Range("B4")
ActiveDocument.Bookmarks("fietsvoor").Range.Text = xl.Sheets("output").Range("F2")

ActiveDocument.Bookmarks("vaakfietsen").Range = xl.Sheets("vaakfietsen").ChartArea.PasteSpecial(DataType:=wdPasteBitmap)

ActiveDocument.Bookmarks("percdage").Range.Text = xl.Sheets("output").Range("J2")
ActiveDocument.Bookmarks("percweek").Range.Text = xl.Sheets("output").Range("J3")

xl.workbooks.Close



End Sub

[Dave]

From XL, the follwoing code adds a chart .gif to the Word selection point. Perhaps it will help? Dave

Set WordApp2 = CreateObject("Word.Application")
Set ObjSelection = WordApp2.Selection
Set ObjShape = ObjSelection.InlineShapes.AddPicture("C:\ChartIt.gif")

on edit:

Set ObjShape = Selection.InlineShapes.AddPicture("C:\ChartIt.gif")

should probably work where ObjShape is an object.