PowerPoint table and charts linked to excel updates by VBA

[Manish Sharma]

Dear friends,

THIS POST IS MIRRORED @ http://www.excelforum.com/excel-pro...nd-charts-linked-to-excel-updates-by-vba.html

I have one excel file (Excel Template) and one Presentation file (Presentation Template Macro). The idea is to…

---Open Excel file -> update the data -> save -> close it.
---Then open powerpoint -> run VBA code -> update the presentation (which is linked to the excel)

Problem: Code updates the first slide properly from sheet1 but the second slide is not getting updated. The code I have used is not mine. Can anyone please let me know why slide 2 is not getting updated?

For your reference i have uploaded the two files
(Sorry i used third party to upload the file, as in my browser i was not able to upload the file directly in the Post)
http://s000.tinyupload.com/?file_id=...90137459088195

Here is the code used to update the presentation:

Option Explicit

Sub UpDateLinks()
    '~~> Powerpoint Variables/Objects
    Dim ofd As FileDialog
    Dim initDir As String
    Dim OldSourcePath As String, NewSourcePath As String

    '~~> Excel Objects
    Dim oXLApp As Object, oXLWb As Object

    '~~> Other Variables
    Dim sPath As String, OldPath As String, sFullFileOld As String
    Dim oldFileName As String, newFileName As String

    'Set the initial directory path of File Dialog
    initDir = "C:\"

    '~~> Get the SourceFullName of the chart. It will be something like
    '  C:\MyFile.xlsx!Sheet1![MyFile.xlsx]Sheet1 Chart 1
    OldSourcePath = ActivePresentation.Slides(1).Shapes(1).LinkFormat.SourceFullName

    Set ofd = Application.FileDialog(msoFileDialogFilePicker)

    With ofd
        .InitialFileName = initDir
        .AllowMultiSelect = False

        If .Show = -1 Then
            '~~> Get the path of the newly selected workbook. It will be something like
            '  C:\Book2.xlsx
            sPath = .SelectedItems(1)

            '~~> Launch Excel
            Set oXLApp = CreateObject("Excel.Application")
            oXLApp.Visible = True

            '~~> Open the Excel File. Required to update the chart's source
            Set oXLWb = oXLApp.Workbooks.Open(sPath)

            '~~> Get the path "C:\MyFile.xlsx" from
            '~~> say "C:\MyFile.xlsx!Sheet1![MyFile.xlsx]Sheet1 Chart 1"
            OldPath = Split(OldSourcePath, "!")(0)

            '~~> Get just the filename "MyFile.xlsx"
            oldFileName = GetFilenameFromPath(OldPath)
            '~~> Get just the filename "Book2.xlsx" from the newly
            '~~> Selected file
            newFileName = GetFilenameFromPath(.SelectedItems(1))

            '~~> Replace old file with the new file
            NewSourcePath = Replace(OldSourcePath, oldFileName, newFileName)

            'Debug.Print NewSourcePath

            '~~> Change the source and update
            ActivePresentation.Slides(1).Shapes(1).LinkFormat.SourceFullName = NewSourcePath
            ActivePresentation.Slides(1).Shapes(1).LinkFormat.Update
            DoEvents

            '~~> Close Excel and clean up
            oXLWb.Close (False)

            Set oXLWb = Nothing
            oXLApp.Quit
            Set oXLApp = Nothing
        End If
    End With

    Set ofd = Nothing
End Sub

Public Function GetFilenameFromPath(ByVal strPath As String) As String
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = _
        GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

Thanks in advance