This is not the most practical way, but other examples I tried did not work.
My goal is to refresh PPT links via Excel VBA.
My boss has an Excel spreadsheet with our project status. I display that with a Raspberry Pi via PowerPoint on a TV monitor.
I made a "refresh" button in Excel. After adding more to the spreadsheet, it will update the PowerPoint.
Everything is working, except when I try to open the PowerPoint after the refresh, I see:
I am saving it to a cloud to make is accessible to everyone in the company. I get the error message, not my boss.
Sub CopyRangeToPowerPoint()
'Declare PowerPoint Variables
Dim PP As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim SlideTitle As String
Dim exlRange As Range
Dim filePath As String
'Opening PowerPoint and Creating a new Presentation
Set PP = CreateObject("PowerPoint.Application")
Set PPPres = PP.Presentations.Add
'PP.ActiveWindow.WindowState = ppWindowMinimized
'Defining the path
filePath = ("PathToFile\TV Display PowerPoint.pptx")
PP.DisplayAlerts = ppAlertsNone
'Adding a new slide in PowerPoint Presentation and selecting that slide for further use
For i = PPPres.Slides.Count To 1 Step -1
Set PPSlide = PPPres.Slides(i)
PPSlide.Delete
Next i
Set PPSlide = PPPres.Slides.Add(1, ppLayoutLargeObject)
PPSlide.Select
Set exlRange = Range("A1:H45")
exlRange.Copy
PPSlide.Shapes.Paste
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PP.Activate
PPPres.SaveAs (filePath)
'PP.ActiveWindow.WindowState = ppWindowMaximized
PPPres.Close
PP.Quit
Set PPSlide = Nothing
Set PPPres = Nothing
Set PP = Nothing
End Sub
I have a feeling that it has to do with saving over the same path but I need it to be in the same location.
Here is the code that solved my issue. I believe the way I was saving it before was causing this "upload blocked" error message.
Sub CopyRangeToPowerPoint()
'Declare PowerPoint Variables
Dim PP As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim SlideTitle As String
Dim exlRange As Range
Dim filePath As String
'Opening PowerPoint and Creating a new Presentation
Set PP = CreateObject("PowerPoint.Application")
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
Set PPPres = PP.Presentations.Open("PATH TO FILE")
PP.DisplayAlerts = ppAlertsNone
'PP.ActiveWindow.WindowState = ppWindowMinimized
'Deleting current slide
For i = PPPres.Slides.Count To 1 Step -1
Set PPSlide = PPPres.Slides(i)
PPSlide.Delete
Next i
Set PPSlide = PPPres.Slides.Add(1, ppLayoutLargeObject)
PPSlide.Select
Set exlRange = Range("A1:H45")
exlRange.Copy
PPSlide.Shapes.Paste
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PP.Activate
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
PPPres.Save
PPPres.Close
PP.Quit
Set PPSlide = Nothing
Set PPPres = Nothing
Set PP = Nothing
End Sub