VBA in PowerPoint to run updates from Excel worksheets

InnocentRascel

New Member
Joined
Apr 20, 2017
Messages
1
I have a PowerPoint presentation ~100 slides that contains graphs, links and worksheet links to Excel. The source files all reside in TEAMS. I have very limited VBA knowledge, I can only get one update to run and it runs for ~1 hour.

I really really hope that I don't have to update 8 links and 8 graphs per slide manually for over 100 slides?
I have tried the below, but that will only update the graphs but not the links.
Sub update2()

Dim myPresentation As PowerPoint.Presentation
Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim myChart As PowerPoint.Chart
Dim Wb As Object
Dim App As Object

Set myPresentation = ActivePresentation

For Each sld In myPresentation.Slides
For Each shp In sld.Shapes
If shp.HasChart Then
Set myChart = shp.Chart
myChart.ChartData.Activate
myChart.Refresh
Set Wb = myChart.ChartData.Workbook
Set App = Wb.Application
Wb.Close (0)
End If
Next
Next


AutoUpdate

End Sub

I have tried this:
Sub EditPowerPointLinks()

Dim oldFilePath As String
Dim newFilePath As String
Dim pptPresentation As Presentation
Dim pptSlide As Slide
Dim pptShape As Shape

'The old file path as a string (the text to be replaced)
oldFilePath = "String of\File Path\To Be Replaced\https:://sites ......xlsx?web=1"

'The new file path as a string (replacing with the same file as I don't know how only have the vba update)
newFilePath = "String of\New File Path\Excel File 2.xlsx"

'Set the variable to the PowerPoint Presentation
Set pptPresentation = ActivePresentation

'Loop through each slide in the presentation
For Each pptSlide In pptPresentation.Slides

'Loop through each shape in each slide
For Each pptShape In pptSlide.Shapes

'Find out if the shape is a linked object or a linked picture
If pptShape.Type = msoLinkedPicture Or pptShape.Type _
= msoLinkedOLEObject Or pptShape.Type = msoLinkedChart Then

'Use Replace to change the oldFilePath to the newFilePath
pptShape.LinkFormat.SourceFullName = Replace(LCase _
(pptShape.LinkFormat.SourceFullName), LCase(oldFilePath), newFilePath)

End If
Next
Next

'Update the links
pptPresentation.UpdateLinks


End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,795
Hi InnocentRascal, welcome to MrExcel.

Please when you add code to your post, put it in between code brackets (see example below my post). Easy to do: click the little VBA icon above your post window and paste your code.

Where does your code fail? I am assuming when you run it, VBA will stop on a line shown in yellow, with an error message.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,781
Messages
5,766,430
Members
425,353
Latest member
ExcelRalph

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