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

[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.
Captura.jpg
Do you have any idea about this error?

[antonanton]

Hello,

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

Captura.jpg

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.