VBA in PowerPoint to run updates from Excel worksheets

[InnocentRascel]

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

 

[sijpie]

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.