Sleeper: Automatically Update Linked Spreadsheet in Slides

[wilabern]

Howdy,

I am attempting to create a work board for our quality group in the office to track what jobs are in the shop and what process are to be completed. We have 3 different departments from our shop, so in an excel file I have 3 different worksheets. I have shared the excel file with the other quality members so the can update the board from the shop floor as they complete the processes.

I have the excel 3 worksheets linked to 3 different power point slides that are on a 30 second loop.

I am looking for code that will automatically update the worksheets on the powerpoint slides either every loop or after a set amount of time.

Thank you.

Update:


Sub OnSlideShowPageChange(ByVal SSW As SlideShowWindow)
If SSW.View.CurrentShowPosition = 2 Then
ActivePresentation.UpdateLinks
End If
End Sub
I am using the following code to update my links in the powerpoint slide in presentation. However, it is not very subtle when the slides update (The powerpoint it looks like the powerpoint is having a seizure when it updates the slide). Is there anything I can do to make it seem more professional?

[SamT]

Update after the last slide, before the loop starts over

[Paul_Hossler]

Shyam usually has the answer to everything

Try



ScreenUpdating = False

...updates, etc.

ScreenUpdating = True









Option Explicit

' --------------------------------------------------------------------------------
' Copyright ©1999-2009, Shyam Pillai, All Rights Reserved.
' --------------------------------------------------------------------------------
' You are free to use this code within your own applications, add-ins,
' documents etc but you are expressly forbidden from selling or
' otherwise distributing this source code without prior consent.
' This includes both posting free demo projects made from this
' code as well as reproducing the code in text or html format.
' --------------------------------------------------------------------------------
' UserDefined Error codes
Const ERR_NO_WINDOW_HANDLE As Long = 1000
Const ERR_WINDOW_LOCK_FAIL As Long = 1001
Const ERR_VERSION_NOT_SUPPORTED As Long = 1002

' API declarations for FindWindow() & LockWindowUpdate()
' Use FindWindow API to locate the PowerPoint handle.
Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

' Use LockWindowUpdate to prevent/enable window refresh
Declare Function LockWindowUpdate Lib "user32" _
(ByVal hwndLock As Long) As Long
' Use UpdateWindow to force a refresh of the PowerPoint window
Declare Function UpdateWindow Lib "user32" (ByVal hWnd As Long) As Long

Property Let ScreenUpdating(State As Boolean)
Static hWnd As Long
Dim VersionNo As String

' Get Version Number
If State = False Then
VersionNo = Left(Application.Version, InStr(1, Application.Version, ".") - 1)

' Get handle to the main application window using ClassName
Select Case VersionNo
Case "8" ' For PPT97:
hWnd = FindWindow("PP97FrameClass", 0&)
Case "9" ' For PPT2K:
hWnd = FindWindow("PP9FrameClass", 0&)
Case "10" ' For XP:
hWnd = FindWindow("PP10FrameClass", 0&)
Case "11" ' For 2003:
hWnd = FindWindow("PP11FrameClass", 0&)
Case "12" ' For 2007:
hWnd = FindWindow("PP12FrameClass", 0&)
'For 2010 you need to add this:
Case "14" ' For 2010:
hWnd = FindWindow("PPTFrameClass", 0&)
Case "16" ' For 2016
hWnd = FindWindow("PPTFrameClass", 0&)

Case Else
Err.Raise Number:=vbObjectError + ERR_VERSION_NOT_SUPPORTED, _
Description:="Supported for PowerPoint 97/2000/2002/2003 only."
Exit Property
End Select
If hWnd = 0 Then
Err.Raise Number:=vbObjectError + ERR_NO_WINDOW_HANDLE, _
Description:="Unable to get the PowerPoint Window handle"
Exit Property
End If
If LockWindowUpdate(hWnd) = 0 Then
Err.Raise Number:=vbObjectError + ERR_WINDOW_LOCK_FAIL, _
Description:="Unable to set a PowerPoint window lock"
Exit Property
End If
Else
' Unlock the Window to refresh
LockWindowUpdate (0&)
UpdateWindow (hWnd)
hWnd = 0
End If
End Property