CODEWITHSUNDEEP
vbaexcelpowerpoint
Cudny
Cudny

Reputation: 98

Power point tables linked to excel - How to change source (VBA)?

  1. I have big presentation (~300 slides) And i need to make few versions of it, each connected to diffrent excel file. I have code that changes links for all shapes inside prestations. Its all good for charts, but there is problem with linked tables. Source change is correct, but during this change range for table dissapires (range is set for 1st sheet cell A1). Is there way to keep the range unchanged?
  2. Additional question: changing chart source is very fast (<1s),whereas changing linked table source takes some time (~15s). This becomes a problem where there is a lot tables. When i run code few times ~50 slides in one run it went well (took ~5-10min), but when i tried run it on all ~300 slides i waited for 30min and it didn't finish (there was no crush, it looked like procedure frozed). Im really curious why this problem occures.

Belowe code i use for link change:

Sub UpdateLinks()
Dim ExcelFile
Dim exl As Object
Set exl = CreateObject("Excel.Application")

 'Open a dialog box to promt for the new source file.
ExcelFile = exl.Application.GetOpenFilename(, , "Select Excel File")

Dim i As Integer
Dim k As Integer

 'Go through every slide
For i = 1 To ActivePresentation.Slides.Count
    With ActivePresentation.Slides(i)
         'Go through every shape on every slide
        For k = 1 To .Shapes.Count
            'Turn of error checking s that it doesn 't crash if the current shape doesn't already have a link
            On Error Resume Next
             'Set the source to be the same as teh file chosen in the opening dialog box
            .Shapes(k).LinkFormat.SourceFullName = ExcelFile
            If .Shapes(k).LinkFormat.SourceFullName = ExcelFile Then
                 'If the change was successful then also set it to update automatically
                .Shapes(k).LinkFormat.AutoUpdate = ppUpdateOptionAutomatic 'other option is ppUpdateOptionManual/ppUpdateOptionAutomatic
            End If
            On Error GoTo 0
        Next k
    End With
Next i
End Sub

All tips are welcome! :)

Upvotes: 0

Views: 2715

Answers (1)

Steve Rindsberg
Steve Rindsberg

Reputation: 14809

Have you looked at what .SourceFullName returns? Usually it's not just the file name but also further code that indicates what sheet and range within the sheet the link points to. It looks like you're changing that to just the name of the replacement Excel file.

Instead, try using Replace to substitute the name of the new Excel file for the name of the old Excel file in .SourceFullName. That'll leave the rest of the link text intact.

Upvotes: 0

Related Questions