kemidan2014
12-16-2022, 08:16 AM
I am sorry this is so wordy.
I have been working on a Visual Management Kiosk presentation for use on the plant floor to visuallize component replacements of tools for quicker reaction by engineers.
I am NEARLY there with having this Powerpoint behave the way I want it too
I have a couple questions about the code because Google is not giving me answers i need
below is my code. it works, i just need to understand it more to see if I am using the correct settings.
alot of this is mash up of code references and google research
My first question is where i have the Directory of the excel file i want to open: what are ,True,False associated with? what part of the file opening process is it manipulating?
If i set xlApp.Visible=False how do i gain confidence the links are actually updating? is excel actually opening in the background or does this just cancel out the whole thing?
The only reason i am Opening the workbook in the first place was to avoid the pop up to "update, dont update, cancel" pop up that power point would ask every time the updatelinks macro ran.
Ultimate goal is:
Every time the presentation returns to the starting slide then i want it to update links.
i want no pop ups to happen when this occurs. thats the goal.
my 1st itteration
Slide show begins
Links update
Pop up happens, freezes presentation
2nd itteration
Slide show begins
Excel opens (read only)
Links update, No pop up
Excel closes, asks for file save, presentation doesnt freeze but in theory excel will eventually try and reopen and then you cant have 2 files open. so link updating wont happen
3rd Itteration (below)
Slide show begins
Excel opens (read only) (Visibly)
Links update, No pop up
Excel closes, No pop up
Sub OnSlideShowPageChange(ByVal SSW As SlideShowWindow)
'Trigger Macro to run at starting slide
If SSW.View.CurrentShowPosition = _
SSW.Presentation.SlideShowSettings.StartingSlide Then
'The macro i want to run
Updatelinks
End If
End Sub
Sub Updatelinks()
'Make declarations
Dim xlApp As Object
Dim xlWorkBook As Object
Dim osld As Slide
Dim oshp As Shape
'Open source workbook
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWorkBook = xlApp.Workbooks.Open("I:\'Redacted intentionally'\Core pin map Calculations.xlsx", True, False)
'look for linked OLE objects in all slides
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
'Update the link if it is
oshp.LinkFormat.Update
End If
'Loop
Next oshp
Next osld
xlWorkBook.Close SaveChanges:=False
xlApp.Quit
End Sub
I have been working on a Visual Management Kiosk presentation for use on the plant floor to visuallize component replacements of tools for quicker reaction by engineers.
I am NEARLY there with having this Powerpoint behave the way I want it too
I have a couple questions about the code because Google is not giving me answers i need
below is my code. it works, i just need to understand it more to see if I am using the correct settings.
alot of this is mash up of code references and google research
My first question is where i have the Directory of the excel file i want to open: what are ,True,False associated with? what part of the file opening process is it manipulating?
If i set xlApp.Visible=False how do i gain confidence the links are actually updating? is excel actually opening in the background or does this just cancel out the whole thing?
The only reason i am Opening the workbook in the first place was to avoid the pop up to "update, dont update, cancel" pop up that power point would ask every time the updatelinks macro ran.
Ultimate goal is:
Every time the presentation returns to the starting slide then i want it to update links.
i want no pop ups to happen when this occurs. thats the goal.
my 1st itteration
Slide show begins
Links update
Pop up happens, freezes presentation
2nd itteration
Slide show begins
Excel opens (read only)
Links update, No pop up
Excel closes, asks for file save, presentation doesnt freeze but in theory excel will eventually try and reopen and then you cant have 2 files open. so link updating wont happen
3rd Itteration (below)
Slide show begins
Excel opens (read only) (Visibly)
Links update, No pop up
Excel closes, No pop up
Sub OnSlideShowPageChange(ByVal SSW As SlideShowWindow)
'Trigger Macro to run at starting slide
If SSW.View.CurrentShowPosition = _
SSW.Presentation.SlideShowSettings.StartingSlide Then
'The macro i want to run
Updatelinks
End If
End Sub
Sub Updatelinks()
'Make declarations
Dim xlApp As Object
Dim xlWorkBook As Object
Dim osld As Slide
Dim oshp As Shape
'Open source workbook
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWorkBook = xlApp.Workbooks.Open("I:\'Redacted intentionally'\Core pin map Calculations.xlsx", True, False)
'look for linked OLE objects in all slides
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
'Update the link if it is
oshp.LinkFormat.Update
End If
'Loop
Next oshp
Next osld
xlWorkBook.Close SaveChanges:=False
xlApp.Quit
End Sub