Need VBA for editing Excel Charts embedded in Powerpoint

[Jody]

Hello!

I have two different scenarios I need to solve and I
imagine they will be handled similarly. FYI, I program
frequently in VB and using VBA in Excel, but using VBA in
Powerpoint is new to me.

The users at my company create huge Powerpoint documents.
Each Powerpoint file will contain many pages and each page
will contain up to four charts. The charts are all linked
to Excel spreadsheets.

The first request I had was to give them an option for
moving the legend location on each chart to the bottom.

The second request is to create a tool for changing the
path for the linked files (the files are moving to a
different file server).

I'm sure the logic will be similar in each case, but I
have no clue how to do this within Powerpoint.

I could really use some direction on how to edit EACH
Excel chart within a Powerpoint document.

TIA!

~~Jody

 

[Steve Rindsberg]

I could really use some direction on how to edit EACH

Excel chart within a Powerpoint document.

To get some ideas about editing the actual charts, google on "automating
Excel" and variants like automate, automation

If you only need to work with the link paths, you'd alter the
..LinkFormat.SourceFullName property of the linked items.

These may give you some further ideas:

EDIT LINKS - Edit link range of linked spreadsheet
http://www.rdpslides.com/pptfaq/FAQ00204.htm

Show me the link and let me edit it
http://www.rdpslides.com/pptfaq/FAQ00433.htm

General outline of what you'll likely need to do:

Dim oSl as Slide
Dim oShapes as Shapes
Dim oSh as Shape

For each oSl in ActivePresentation.Slides
set oShapes = oSl.Shapes
for each oSh in oShapes
' is it an excel chart object?
if oSh.OLEFormat.ProgID = "Excel.Chart.8" Then
' do your thing; it might be a different version of Excel, so
substitute the correct version or test like:
' if Ucase(Mid$(oSh.OLEFormat.ProgID,1,11)) = "EXCEL.CHART"
end if
next oSh
Next oSl