Consulting

Results 1 to 9 of 9

Thread: Resize Chart as Picture

  1. #1

    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!

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    Size the chart before making it a picture. HTH. Dave

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

  4. #4
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    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

  5. #5

  6. #6
    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.

  7. #7
    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.

  8. #8
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    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

  9. #9
    VBAX Newbie
    Joined
    Nov 2013
    Posts
    1
    Location
    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
  •