Posts by VBABasiks

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?

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)

pPreso.Slides(1).Shapes(var(i)).LinkFormat.Update

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.