×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

MACRO : Excel -> Word copy and paste CHARTS

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


RE: MACRO : Excel -> Word copy and paste CHARTS

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").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

(OP)
...can't wait to try it!!

thanks

RE: MACRO : Excel -> Word copy and paste CHARTS

(OP)
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



RE: MACRO : Excel -> Word copy and paste CHARTS

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").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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close