Excel to PowerPoint automatic update Code - Does not work after reopening the file

  • Hi everyone. Completely new here and also new with VBA programming. So you might see very dumb questions from me, i apologize for that in advance.

    Im trying to automatically update Power Point report which I have to update every few months, using Excel. I use Excel to save all the data tables and charts. So i thought if i use dynamic charts and link it to Slide, I will only have to update Excel and Power Point will update automatically. So i went to youtube and copied the scripts below. It worked - while both files were open.

    But failed, the moment I closed the files and re-opened it.

    Below are the Module Code and Worksheet code :

    Module Code:Please also check the attached screen shot, the first line is highlighted Yellow and in the last row, i is also highlighted. Is the VBA indicating errors in those lines?

    ========================================================================[Blocked Image: https://www.excelforum.com/attachments/excel-programming-vba-macros/782365d1654003807-excel-to-powerpoint-automatic-update-code-does-not-work-after-reopening-the-file-code-screen-shot.jpg]

    Sub Refresh(ParamArray var() As Variant)

    Dim pApp As Object

    Dim pPreso As Object

    Dim pSlide As Object

    Dim SPreso As String

    'Define Powerpoint Dashboard File Path

    SPreso = "\Users\xxx.xxxx\OneDrive - xxx\Documents\xxx\xxx\Automatic Reports\xxxxx.pptx"

    'Open and reference PowerPoint Dashboard File

    On Error Resume Next

    Set pApp = GetObject(, "PowerPoint.Application")

    If Err.Number <> 0 Then

    Set pApp = CreateObject("PowerPoint.Application")

    pApp.Visible = True

    End If

    On Error Resume Next

    Set pPreso = pApp.Presentations(SPreso)

    If Err.Number <> 0 Then

    Set pPreso = pApp.Presentations.Open(Filename:=SPreso)

    'Update every Chart in ParamArray var

    Dim varSize As Integer

    Dim i As Interior

    varSize = UBound(var) - LBound(var) + 1

    For i = 0 To (varSize - 1)


    Next i

    End Sub


    Worksheet Code:

    Sub Worksheet_Change(ByVal Target As Range)

    'Dealer User Data

    If Not Application.Intersect(Target, Range("D9:D15")) Is Nothing Then

    Call Refresh("Chart 16", "Chart 17", "Chart 22", "Chart 23")

    End If

    'Number of Dealers

    If Not Application.Intersect(Target, Range("D13")) Is Nothing Then

    Call Refresh("Chart 20")

    End If

    'Page Access

    If Not Application.Intersect(Target, Range("R33:R43")) Is Nothing Then

    Call Refresh("Chart 7")

    End If

    End Sub


    All of the above was working only the first time. But failed after closing the files. I'm using Microsoft Excel 365. Any ideas what might be happening?

    Another question from VBA pros - any time i add or delete a slide in power point file, will that make an impact? Will i have to change the script or chart numbers?

    Thank you.

  • Guys - im confused. I can see almost 10+ people have reviewed my post. But no response. Have I asked it on the wrong forum? My apologies if thats the case. Or is this task really impossible?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!