Convert Externally Linked Graphs Into Pictures in PowerPoint (multiple charts on one slide)

loubdizzy

New Member
Joined
Mar 18, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Hey Guys

I have had great success with a macro sourced form Spreadsheet guru, however having difficulties modifying to meet my requirements.
I have multiple graphs on the same sheet, this script currently only converts the first graph on the slide, not the bottom one (any others) there are usually only 2 charts to a slide, but I would like it to loop through all of them and ... if i could use all my genie wishes, also convert linked tables to images also, in the same way.

This would mean opening the ppt, refreshing data and then running the macro, it would save me hours of time snipping.

VBA Code:
Sub LinkedGraphsToPictures()
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim shp As Shape
Dim sld As Slide
Dim pic As Shape
Dim shp_left As Double
Dim shp_top As Double

'Loop Through Each Slide in ActivePresentation
  For Each sld In ActivePresentation.Slides
    For Each shp In sld.Shapes
      
      If shp.Type = msoChart Then
        'Retrieve current positioning
          shp_left = shp.Left
          shp_top = shp.Top
        
        'Copy/Paste as Picture
          shp.Copy
          
          sld.Shapes.PasteSpecial DataType:=ppPastePNG
          
          Set pic = sld.Shapes(sld.Shapes.Count)
        
        'Delete Linked Shape
          shp.Delete
        
        'Reposition newly pasted picture
          pic.Left = shp_left
          pic.Top = shp_top
      
      End If
      
    Next shp
  Next sld

End Sub

Thank you in advance!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

loubdizzy

New Member
Joined
Mar 18, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
*update*

all though clunky if i add another loop, as above with msoEmbeddedOLEObject, my tables will save as a picture fine:) .... I just still cant work out how to run this to include all charts on one slide... the below macro works for all objects. however it changes the location and size of the object. So although it works, i want in to retain original position and size...
VBA Code:
Sub ConvertAllShapesToPic()
    Dim oSl As Slide
    Dim oSh As Shape

    For Each oSl In ActivePresentation.Slides
        For Each oSh In oSl.Shapes
            ' modify the following depending on what you want to
            ' convert
            Select Case oSh.Type
                Case msoChart, msoEmbeddedOLEObject, msoLinkedOLEObject
                    ConvertShapeToPic oSh
                Case msoPlaceholder
                    If oSh.PlaceholderFormat.ContainedType = msoEmbeddedOLEObject _
                        Or oSh.PlaceholderFormat.ContainedType = msoLinkedOLEObject _
                        Or oSh.PlaceholderFormat.ContainedType = msoChart _
                        Then
                        ConvertShapeToPic oSh
                    End If
                Case Else

            End Select
        Next
    Next

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,061
Members
417,067
Latest member
rohitbabshet

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
Top