Convert all Charts on Worksheet to Pictures

S

screech

Active Member
Joined
May 27, 2004
Messages
296
Hello, I am running into a problem when I create a PDF file out of many Excel Charts. The resulting PDF file is massive and I know that I can greatly reduce the PDF file size if I change all of the charts into pictures before printing.

I am wondering if it is possible to have a macro loop through all of the charts on a worksheet, copy them, and then paste them back in the exactly same place as a picture. I think it should be possible but I'm not sure how I would handle the names of the charts or paste them back down right where they are. Can someone tell me if this is possible and how it can be done, or if there's an alternative to converting all charts to pictures (external program). Thank you for the help!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Hi screech

Try:

Code: 
Sub ChtPic()
Dim chtO As ChartObject
Dim dTop As Double, dLeft As Double
 
With Worksheets("Sheet6")
    For Each chtO In .ChartObjects
        chtO.CopyPicture
        .Paste
        With .Shapes(.Shapes.Count)
            .Top = chtO.Top
            .Left = chtO.Left
            .Name = chtO.Name & "_pic"
        End With
        chtO.Delete
    Next chtO
End With
End Sub
 
Upvote 0
Agreed! I have a similar issue (I use Excel 2010) where I create a page in a manual by pasting a picture of 4 charts from another excel file. A certain chart file I use has had the pesky problem of when I "Copy as picture" "As shown when printed" the resulting picture to paste is nearly 5 MB. After running this macro before doing the "Copy as picture" "As shown when printed", the size is less than 300kb. I am still curious why some charts just explode in size, whether copied as a "picture" or put to PDF like screech.
 
Upvote 0
Okay total newb but I can't find this anywhere else on the internet so could really use help. Going through hundreds of slides for work and we always have to individually copy and past as PDF/image and I open Visual Vasic in PowerPoint, create module, paste code, and the run. And I keep hitting this issue: "User-defined type not defined" and I think it's highlighting the "Dim ch0 As ChartObject"


Can someone help? I think this could end up saving hours of my team's time throughout the year.
 
Upvote 0
Redfield11 said:
Okay total newb but I can't find this anywhere else on the internet so could really use help. Going through hundreds of slides for work and we always have to individually copy and past as PDF/image and I open Visual Vasic in PowerPoint, create module, paste code, and the run. And I keep hitting this issue: "User-defined type not defined" and I think it's highlighting the "Dim ch0 As ChartObject"


Can someone help? I think this could end up saving hours of my team's time throughout the year.
Click to expand...
I also tried this one from stack overflow: stackoverflow (dot)com/questions/13057432/convert-all-worksheet-objects-to-images-in-powerpoint/13080486 but ran into the issue "Method or data member not found" and it's highlighting "PasteSpecial".
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA Dynamic Print to PDF
Replies
2
Views
596
jolivanes
jolivanes
L
  • Question
Correct mistaken info
Replies
1
Views
105
Lux Aeterna
L
R
  • Question
Excel Formula for showing a picture.
Replies
1
Views
272
Anfinsen
A
D
  • Question
Inserting pictures in UserForm from within workbook
Replies
7
Views
358
John_w
J
C
  • Solved
VBA to search for table from PDF File and copy and paste to Worksheet
Replies
4
Views
457
Alex Blakenburg
A

Forum statistics

Threads
1,214,908
Messages
6,122,186
Members
449,071
Latest member
cdnMech

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top