Convert all Charts on Worksheet to Pictures

[screech]

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!

 

[pgc01]

Hi screech

Try:

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

 

[rogerkaplan]

Just stumbled across this. It's brilliant. Thanks!

 

[helmbrecbe15]

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.

 

[Redfield11]

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.

 

[Redfield11]

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.

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