MACRO : Excel -> Word copy and paste CHARTS
MACRO : Excel -> Word copy and paste CHARTS
(OP)
Hi to all,
gimme a hand please!
I need to print a huge number of excel charts and add them on a paper, which may be done with MSWord
is there any mean to create a macro that copies an array of charts from excel and paste them on a wordsheet in a printable and orderly fashion?
thanks in advance

gimme a hand please!
I need to print a huge number of excel charts and add them on a paper, which may be done with MSWord
is there any mean to create a macro that copies an array of charts from excel and paste them on a wordsheet in a printable and orderly fashion?
thanks in advance

RE: MACRO : Excel -> Word copy and paste CHARTS
Here's the code I used to copy all the charts from my "Book1", "Sheet1" to a word document. (I also added the chart name (more of a chart index really) below each chart in word. The word document is then saved on the desktop and closed. (Note: If you want the word sheet to be visable, type in "WordApp.Visable = True" before the for-next statement.)
By the way, before running this macro, you need to go into "TOOLS" -> "References..." and check the "Microsoft Word X.0 Object Library" otherwise you will get an error.
Sub ExcelToWord()
Dim WordApp As New Word.Application
SaveAsName = "C:\Windows\Desktop\Charts.doc"
ChartNum = 0
WordApp.Documents.Add
For Each i In ActiveSheet.ChartObjects
ChartNum = ChartNum + 1
Workbooks("Book1").Sheets("Sheet1").ChartObjects(ChartNum).Select
ActiveChart.ChartArea.Copy
With WordApp.Selection
.Paste
.TypeParagraph
.TypeText Text:="Chart " & ChartNum
.TypeParagraph
End With
Next i
WordApp.ActiveDocument.SaveAs (SaveAsName)
WordApp.Quit
Set WordApp = Nothing
MsgBox ChartNum & " charts were copied to Word and saved in " & SaveAsName
End Sub
If you want to customize the output, I suggest you record a macro in word to get the macro temenology, then just paste it in the code above.
Good Luck!
jproj
RE: MACRO : Excel -> Word copy and paste CHARTS
thanks
RE: MACRO : Excel -> Word copy and paste CHARTS
it works perfectly when the charts are created in a progressive way (Chart1, Chart2,...Chartn)
but sometimes it gives troubles when (as in my case) there's a previous MacroPrev that brings all the charts to Sheet1, and the charts are just thrown in according to another counter (let's say Chart12, Chart13...)
or even without any order.
now the situation is:
1)Each Chart is assigned a sheet on his own (SheetChart1, SheetChart2...)
MacroPrev moves Chart(i) from SheetChart(i) to Sheet1
and assign it a name according to any previous chart operation i've done
how can i make MacroPrev rename progressively the Charts once they're brought to Sheet1, or even name them just before moving?
2)Since Charts are too heavy, and Charts.doc turns out to be too big, How can i make
ExcelToWord() 'PasteSpecial' the charts on Charts.doc as images?
or just copy in a light way?
3)How can i ,then, make word resize the Charts ?
thanks
...and if i bother you just tell me where to read more about these subjects
bye
RE: MACRO : Excel -> Word copy and paste CHARTS
Here's my revised code... it shrinks the chart then saves it in word as a metafile. The only problem is that it pastes each chart in the middle of the document (each on top of each other). I can't figure out how to keep it from doing this except by just using the "paste" command (instead of paste special) which generates large files.
I also changed the "label" the macro places under each sheet so it uses the actual sheet number (not the index) and it displays the sheet's label also. I also entered an event handeler that exits the macro if there is a problem.
Sub ExcelToWord()
Dim WordApp As New Word.Application
SaveAsName = "C:\Windows\Desktop\Charts.doc"
Chartnum = 0
On Error GoTo Handler
WordApp.Documents.Add
For Each i In ActiveSheet.ChartObjects
Chartnum = Chartnum + 1
With Workbooks("Copy Charts To Word").Sheets("Sheet1").ChartObjects(Chartnum)
.Select
MyTitle = ActiveChart.ChartTitle.Caption
ChartName = ActiveChart.Name
.Width = 360
.Height = 180
.CopyPicture
End With
ActiveChart.ChartArea.Copy
With WordApp.Selection
.PasteSpecial Placement:=wdFloatOverText, DataType:=wdPasteEnhancedMetafile
.TypeParagraph
.TypeText Text:="''" & MyTitle & "''" & " (" & ChartName & ")"
.TypeParagraph
End With
Next i
WordApp.ActiveDocument.SaveAs (SaveAsName)
WordApp.Quit
Set WordApp = Nothing
MsgBox Chartnum & " charts were copied to Word and saved in " & SaveAsName
Exit Sub
Handler:
MsgBox "An Error has occured."
WordApp.Quit
Set WordApp = Nothing
MsgBox "Error #" & Err.Number & " " & Error
End Sub
Hopefully you can figure out the differences in the code. Sorry I can't be of more help, but if you get stuck, one of the most useful things to do is to record your own macro then analyze the recorded code. You might also look at your local bookstore for books on VBA for Excel / Word.
Good luck!
jproj