Resize Chart as Picture

[IRish3538]

Hey guys.. I'm copying over a chart object (as a picture) into a word bookmark and resize the picture. I can copy the chart as a picture no problem, just can't figure out how to resize (as a picture). I want to leave the actual chart alone. Any ideas?


ThisWorkbook.Sheets("Trend Charts").ChartObjects("Chart2").CopyPicture

'Somehow resize the image in the clipboard

pappword.Selection.Goto Name:="CHART_WATCH"
pappword.Selection.Paste



I'm pretty sure that I need to set the picture as a variable and edit it that way, but the above is the only way I know how to copy the chart as a picture. Thanks for any help you can give me!

[Dave]

Size the chart before making it a picture. HTH. Dave

[IRish3538]

I need to resize as a picture, leaving the chart untouched. I'm not looking for simple work-arounds... only code-based solutions.

[Dave]

I'll re-phrase... copy the chart, resize the copy then make the copy a picture (and then delete the copy). I don't think you will be able to re-size the picture in the clipboard. I'll post if I google anything that helps. Dave

[Dave]

Well maybe MS does it for you?
http://stackoverflow.com/questions/2029724/add-an-image-to-word-document-and-scale-it-using-vba

[IRish3538]

thanks dave... it gets messy resizing charts because excel decides to scale things differently.. title, data values, etc... but when they're rendered as pictures, it scales perfectly. i think i found a solution albeit not pretty

Since I have several charts that I'm converting, I did it as a function. The function gets called and then the contents gets pasted to a bookmark position in a word document


'Converts a chart object into a picture, resizes, then cutcopies it to be transferred to reports
Function Chart_to_Pic(shtname, chartname, w, h)
'Goto the sheet and copy the chart
Sheets(shtname).Select
ActiveSheet.ChartObjects(chartname).Activate
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
Range("f13").Select

'Paste as picture
ActiveSheet.PasteSpecial Format:="Picture (JPEG)", Link:=False, _
DisplayAsIcon:=False

'Rename and scale
with selection
.Name = "Graph"
.ShapeRange.ScaleWidth w, msoFalse, msoScaleFromTopLeft
.ShapeRange.ScaleHeight h, msoFalse, msoScaleFromTopLeft
'Cutcopy (essentially deleting the copied picture)
.Cut
end with
End Function

[IRish3538]

now I have a followup question... I don't have a lot of experience in transferring into word but for some reason, I get an error at the "PasteSpecial" line when I run it in sequence while the word document open.

I get "PasteSpecial method of worksheet class failed". I'm assuming that it's because I'm working with the word doc but I don't know how to get back to the excel class (if that's right??) without ending my word session.

[Dave]

If you try the link it shows you how to resize a pic in the bookmark of a Word doc. As is, if you want to resize the pic in XL first, you need to save it to a file after resizing it (rather than using cut) and then insert the pic file in the bookmark, again the link outlines how to open the Word file and insert a pic file in a bookmark. Dave
ps. I like your function idea. You should however code to limit the use of selection... it usually is not necessary and slows code execution

[LusiJohn]

Yes you can set the chart appearance by using the XML file format.There is a chart software known as Koolchart.com which provides this feature.