-
Resize Chart as Picture
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?
[VBA]
ThisWorkbook.Sheets("Trend Charts").ChartObjects("Chart2").CopyPicture
'Somehow resize the image in the clipboard
pappword.Selection.Goto Name:="CHART_WATCH"
pappword.Selection.Paste
[/VBA]
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!
-
Size the chart before making it a picture. HTH. Dave
-
I need to resize as a picture, leaving the chart untouched. I'm not looking for simple work-arounds... only code-based solutions.
-
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
-
-
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
[VBA]
'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
[/VBA]
Last edited by IRish3538; 02-20-2013 at 03:12 PM.
-
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.
-
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
-
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.
Last edited by LusiJohn; 11-22-2013 at 03:00 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules