[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7784
  • Last Modified:

Auto refresh an powerpoint slide in every 10 seconds

Hi,

I have excel workbook with two sheets.
One called Status Board, other one is Scheduler
I build a powerpoint slide wih a link of excel sheet Status Board
The Status Board contain data from Scheduler sheet.
How can I set auto refresh the PowerPoint slide in every 10 seconds.

Thanks
0
alam747
Asked:
alam747
  • 3
  • 3
1 Solution
 
luconstaCommented:
Tu update the embeded Excel object from a PowerPoint slide you could use this code that will "scan" the entire presentation and will update all the embeded excel objects:

Sub UpdateExcelLinks()
    Dim oShape As Shape
    Dim oSlide As Slide
    
    For Each oSlide In ActivePresentation.Slides
        For Each oShape In oSlide.Shapes
            If oShape.Type = msoLinkedOLEObject Then
                oShape.LinkFormat.Update
            End If
        Next oShape
    Next oSlide
End Sub

Open in new window


To have this job done using a timer, I have modified the code from here: Powerpoint run macro on timer to call the above code:

First the declaration part needed for timer to work (put it in the very beginning of an module):
Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Dim lngTimerID As Long
Dim blnTimer As Boolean

Open in new window

To start the timer use this code:
Sub StartOnTime()
    If blnTimer Then
        lngTimerID = KillTimer(0, lngTimerID)
        If lngTimerID = 0 Then
            MsgBox "Error : Timer Not Stopped"
            Exit Sub
        End If
        blnTimer = False
    Else
        lngTimerID = SetTimer(0, 0, 10000, AddressOf HelloTimer)
        If lngTimerID = 0 Then
            MsgBox "Error : Timer Not Generated "
            Exit Sub
        End If
        blnTimer = True
    End If
End Sub

Open in new window

The above function will call HelloTimer (using the function address) from below at each 10 seconds:
Sub HelloTimer()
    UpdateExcelLinks
End Sub

Open in new window

And finally to stop the timer use this function:
Sub KillOnTime()
    lngTimerID = KillTimer(0, lngTimerID)
    blnTimer = False
End Sub

Open in new window

If is easier for you you could put 2 buttons on that slide - one to START the timer and one to STOP it.
0
 
alam747Author Commented:
Hi Luconsta,
Thanks for your prompt response.
If there any addon in powerpoint updatelinks, I do not have any idea how it works and how to add this to powerpoint to update the links.

instead of using code more easy way to do that.

Thanks
0
 
luconstaCommented:
I'm not aware of such add-on, but there is no need for one.
The code provided above it could be easely insterted in the VBA project as follows:
1. When in PowerPoint press Alt+F11 to open the VBA Project (in brackets you'll have the name of the ppt(x) file.
2. Right click on the VBAProject keyword and select Insert\Module - you'll see that a "Module 1" will appear
3. In the code windows of this module just paste the code in my previous coment starting with the "declarations" and then the rest of procedures, no matter their order - only declarations must be at the beginning.

That's all - you could run the procedures by clicking somwhere between Sub...End Sub and press F5 or you could add 2 buttons on the slide as follows:

1. In the PowerPoint editor (not the Visual Basic Editor) you must have the Developer ribbon - if you don't have it, just activate it from PowerPoint Options\Popular by checking the "Show Developer tab in the Ribbon" option.
2. From the Developper ribbon in the controls group choose the Command Button and click/drag in your slide where the button should be.
3. If you want to change its properties right click on it and select Properties - the most 2 important ones are Name if you want to have a more descriptive name for your button in code window, and Caption - this is what you see written on the button - for example you could write "Start Refreshing" for the button that will start the refreshing.
4. Right click on the button and select View Code to open the code window that will look like this if you let the default name for that button:

Private Sub CommandButton1_Click()
    StartOnTime
End Sub

Open in new window


I have already completed the code by adding StartOnTime to start the updating links procedure.

5. If needed the "Stop refersh" button - repeat steps 2-4 for adding the second button for stopping the automatic refresh.

For exmple if I named the button cmdStopRefresh, the code will look like this:
Private Sub cmdStopRefresh_Click()
    KillOnTime
End Sub

Open in new window


And when showing the presentation, when you reach the slide, just press on buttons.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
luconstaCommented:
If you still want to use an add-in, I just found this one (but I did not tryied myself):

Update links during slide show Add-in for PowerPoint 97 or later
0
 
alam747Author Commented:
I download the updatelinks and it works.

Thanks
0
 
alam747Author Commented:
Thanks a lot
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now