Excel Chart into Word Document automatically using VBA

[EB2003]

Hi,
I have 62 Excel files all in the same format. One sheet has data, the other sheet has a set of 6 charts. Does anyone know how I can automatically put the set of 6 charts from each of the 62 Excel files into a word document (either in separate pages, or separate WORD documents)?
Thank you.

 

[RobBroekhuis]

The following sub should get you started: run from within Word, it creates a new document and pulls in each of the charts from the currently open excel workbook/worksheet. You can customize once you've played around with this a bit.
Rob

 

[SkipVought]

EB2003,

Here's what I would actually do as well as my suggestion to you...

In Word, Macro Record the process of inserting a file into your document. The code will be "dirty" and will have to be cleaned up and then that code put in a loop(s) for the purpose of opening workbooks and selecting charts.

Are all your files in one folder -- that would make it easier, that is if ALL the files in that folder were targets for this process.

Do you have some sort of naming convention for the files and charts? That will help in the process.

Hope this get you off in the right direction. Lots of help here when you run into snags Skip,
Skip@theofficeexperts.com

 

[RobBroekhuis]

Oh, yeah, here's the code...

Sub GetExcelCharts()
Dim xl As Excel.Application, ch As Excel.ChartObject
Documents.Add
Set xl = GetObject(, "excel.application&quot
For Each ch In xl.ActiveWorkbook.ActiveSheet.ChartObjects
ch.CopyPicture
Selection.Paste
Selection.Collapse wdCollapseEnd
Selection.TypeParagraph
Next ch
Set xl = Nothing
End Sub

Rob

 

[EB2003]

I am trying to use the code from Rob in WORD and getting a compile error message on the dim statement for both the Excel.Application and Excel.ChartObject. Do I need to modify the code? Sorry, I have never done VBA in WORD before.
Thanks,
EB

 

[SkipVought]

In your VB Editor, menu item Tools/References - you need to have a reference to Excel version whatever. Skip,
Skip@theofficeexperts.com

 

[EB2003]

Thanks Rob and Skip,
Now its working and I'm on my way! It works when I have the excel file already open on the proper worksheet.
Can anyone tell me how to get it to open an excel file then use it? I tried typing the path in the Getobject function (e.g., GetObject("c:\filename.xls&quot, but it gives me a type mismatch error. If I change the dim statement to dim xl as object, I can open the file but then the "For Each..." statement doesn't work (Object doesn't support this property or method)

Thanks again,
EB

 

[RobBroekhuis]

The proper answer depends on whether you already have Excel open and want to use that instance. If not, you would use something like:

set xl=new excel.application
xl.workbooks.open "c:\filename.xls"
for each ch in xl.activeworkbook.sheets("MyChartSheet&quot.chartobjects
...
...
next ch
xl.activeworkbook.close
xl.quit

Rob

 

[SkipVought]

Have you tried reading HELP? You do not put the path in the GetObject statement.

1. You create an application object

2. You open a file using that application object Skip,
Skip@theofficeexperts.com

 

[RobBroekhuis]

Skip,
Actually, it may work just fine with GetObject and a filename (the automation will know to open Excel owing to the .xls file type), but I agree that it's cleaner to just open the application and open the workbook within the application.
Rob

 

[barleywine]

Hi,
I too am trying to copy Excel charts into Word, but the charts were created as separate sheets in Excel rather than as objects in one sheet. Can anyone tell me how to do this please - loop through the sheets which are charts but ignoring the sheets which are data tables?

Cheers

Barleywine

 

[RobBroekhuis]

dim Sh as sheet
for each Sh in activeworkbook.sheets
if typename(Sh)="Chart" then
Sh.activate
activechart.copypicture ....
end if
next Sh
Rob

 

[EB2003]

Rob and Skip,
Thanks so much for all your help. You guys are great!
Skip, I did read help for GetObject and that is where it told me to put the path in -- both in the description and in an example -- perhaps because I am using an old (Office97)version.
In any case, thanks to the two of you, I am almost done with my project -- just some formatting to do.

EB