Excel Charts to Word Pictures

[Jim Benet]

Please help! I am trying to copy Excel charts (graphs) as pictures into a
Word document. The Excel macro works fine, but the Word macro fails. The
Excel macro merely copies the chart and then calls the Word macro to do the
pasteing. In the word macro, I can paste the chart into the document, but it
pastes it in as an Excel chart. I want to paste it in as a picture.

My Excel macro and 3 versions of my Word macro are below. Version A of
the Word macro works fine, but copies the entire chart. Version B pastes as
a picture, but eliminates all the colors in my chart. Version C doesn’t work
at all.

Can someone suggest a better way to make the macro do what I want it to do?
Thanks, Jim Benet

-----------------------------------------
‘ Excel macro:

Sub Make_Report()
Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
‘ Start Word and create an object
With WordApp
.Documents.Open Filename:=Word_Dir & Word_FILE
End With
' Get plot from Excel file
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
' Send to Word
With WordApp
.Application.Run MacroName:="Macro3"
End With
' Kill the object
With WordApp
.ActiveDocument.SaveAs Filename:=WD_Dir & WD_file
End With
WordApp.Quit
Set WordApp = Nothing
End Sub

-------------------------------------------
‘Word Macros:

Sub Macro3() ‘Version A
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.Paste
Selection.EndKey unit:=wdStory
Selection.EndKey unit:=wdLine
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.TypeParagraph
End Sub

Sub Macro3() ‘Version B
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.PasteAndFormat Datatype:=wdPasteEnhancedMetafile
Selection.EndKey unit:=wdStory
Selection.EndKey unit:=wdLine
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.TypeParagraph
End Sub

Sub Macro3() ‘Version C
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.PasteAndFormat (wdChartPicture)
Selection.EndKey unit:=wdStory
Selection.EndKey unit:=wdLine
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.TypeParagraph
End Sub

 

[Jean-Guy Marcil]

Jim Benet was telling us:
Jim Benet nous racontait que :

Please help! I am trying to copy Excel charts (graphs) as pictures
into a Word document. The Excel macro works fine, but the Word macro
fails. The Excel macro merely copies the chart and then calls the
Word macro to do the pasteing. In the word macro, I can paste the
chart into the document, but it pastes it in as an Excel chart. I
want to paste it in as a picture.

My Excel macro and 3 versions of my Word macro are below. Version
A of the Word macro works fine, but copies the entire chart. Version
B pastes as a picture, but eliminates all the colors in my chart.
Version C doesn't work at all.

Why are you calling the macro from the Word document instead if just having
all the code in Excel?
Try PasteSpecial instead of PasteAndFormat

Can someone suggest a better way to make the macro do what I want it
to do? Thanks, Jim Benet

-----------------------------------------
' Excel macro:

Sub Make_Report()
Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
' Start Word and create an object
With WordApp
.Documents.Open Filename:=Word_Dir & Word_FILE
End With
' Get plot from Excel file
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
' Send to Word
With WordApp
.Application.Run MacroName:="Macro3"
End With
' Kill the object
With WordApp
.ActiveDocument.SaveAs Filename:=WD_Dir & WD_file
End With
WordApp.Quit
Set WordApp = Nothing
End Sub

-------------------------------------------
'Word Macros:

Sub Macro3() 'Version A
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.Paste
Selection.EndKey unit:=wdStory
Selection.EndKey unit:=wdLine
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.TypeParagraph
End Sub

Sub Macro3() 'Version B
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.PasteAndFormat Datatype:=wdPasteEnhancedMetafile
Selection.EndKey unit:=wdStory
Selection.EndKey unit:=wdLine
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.TypeParagraph
End Sub

Sub Macro3() 'Version C
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.PasteAndFormat (wdChartPicture)
Selection.EndKey unit:=wdStory
Selection.EndKey unit:=wdLine
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.TypeParagraph
End Sub

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jim Benet]

PasteSpecial requires an additional "wd" parameter. Excel does not recognize
these and doesn't know how to handle it.

If you have a suggestion on how to make that work, please let me know.

Thanks for your response.

Jim Benet

 

[Jean-Guy Marcil]

Jim Benet was telling us:
Jim Benet nous racontait que :

PasteSpecial requires an additional "wd" parameter. Excel does not
recognize these and doesn't know how to handle it.

If you have a suggestion on how to make that work, please let me know.

Thanks for your response.

Are you using late or early binding?

If you are using early binding, then the parameter will be recognized
because the Word Object library is loaded.

If you are using late binding, use the numeric value of the parameter
instead of the long constant name. In the VBA editor, make sure the Word
library is loaded, do F2 to get to the object browser, find the constant and
highlight it. At the bottom of the screen, on the right side (Under the
object list) you will see its numerical value. This should work.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jim Benet]

Thank, Jean-Guy. I will try that.