MACRO : Excel -> Word copy and paste CHARTS 1

[peppiniello]

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
;-)

 

[jproj]

peppiniello:

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&quot.Sheets("Sheet1&quot.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

 

[peppiniello]

...can't wait to try it!!

thanks

 

[peppiniello]

Thanks a lot jproj! it solves almost entirely my problem

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 ;-)

 

[jproj]

peppiniello:

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&quot.Sheets("Sheet1&quot.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 & &quot"
.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