automatic update linked excel object

[antonanton]

Hello,


I'm trying to create a VBA macro in my PPT presentation, that every minute update all linked objects to the excel file. For that I havee the following code:



Public 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


Sub sequence()
Application.OnTime Now + TimeValue("00:01:00"), "UpdateExcelLinks"
End Sub


but it returns me a compilation error saying that OnTime method could not be found.


Does everyone know if I did something wrong in my code? Or is there another way to perform excel objects refresh every certain time without using OnTime object?

[mdmackillop]

Hi
This is a Powerpoint question. I'll move it to that forum
Meantime, have a read of this (https://social.msdn.microsoft.com/Forums/en-US/9f6891f2-d0c4-47a6-b63f-48405aae4022/powerpoint-run-macro-on-timer?forum=isvvba)

[mdmackillop]

Warning:


SetTimer and KillTimer are quite dangerous in inexperienced hands. It looks for the macro in memory and even if you close PPt (or there's a crash) without running KillTimer it will keep trying to run the code in that memory location (which no longer exists) Only bad things are going to happen!

John Wilson
Microsoft PowerPoint MVP




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


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 UpdateExcelLinks)
If lngTimerID = 0 Then
MsgBox "Error : Timer Not Generated "
Exit Sub
End If
blnTimer = True
End If
End Sub


Sub KillOnTime()
lngTimerID = KillTimer(0, lngTimerID)
blnTimer = False
End Sub


Public 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
MsgBox Now
End Sub

[antonanton]

Hello mdmackillop,

I have a doubt oun your coude abowe.

lngTimerID = SetTimer(0, 0, 60000, AddressOf UpdateExcelLinks) thise 60000 value, what exactlly does mean?

How can I change the time to execute the UpdateExcelLinks macro every 10 sec. lets say?

[mdmackillop]

See the link I posted, This runs the code every 5 seconds, hence 60000

lngTimerID = SetTimer(0, 0, 5000, AddressOf HelloTimer)
For 10 seconds try

lngTimerID = SetTimer(0, 0, 10000, AddressOf UpdateExcelLinks)

[mdmackillop]

Try adding this before End Sub to confirm your code is running

MsgBox Now

[antonanton]

The code is running, but I have some issue with the StartOnTime() method.

When I run it I have an error saying that the types are not matching and it doesn't execute.
20093
Do you have any idea about this error?

[antonanton]

Hello,

I still having the same issue with the code that you sent me.

20094

it says that the types are not the same and the execution of the StartOnTime crashs.

[mdmackillop]

I can't replicate that error, Is it at compile time? If not, try stepping through your Update code.

[John Wilson]

Are you using a 64 bit version of Office?

if not why have you added PtrSafe (incorrectly) to mdmackillop's declarations?

Just a further comment SetTimer and KillTimer are quite dangerous in inexperienced hands. It looks for the macro in memory and even if you close PPt (or there's a crash) without running KillTimer it will keep trying to run the code in that memory location (which no longer exists) Only bad things are going to happen!

[antonanton]

Hello, Yes I'm on 64 bit version!

Does it mean that I'm unable to execute this code?


Code deleted


It's still complaining abnout the type mismatch in StartOnTime method.

Does everyone know if there some other method to automaticallly run my UpdateExcelLinks every certain time?

[mdmackillop]

Despite the warning given to you in Post #12, you chose to post the code omitting the KillOnTime routine. Anyone running the code would be left with no way to terminate the routine. I'm closing this thread.