none
automatic update linked excel object RRS feed

  • Question

  • 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? 
    Wednesday, August 16, 2017 3:00 PM

All replies

  • Hi antonanton5,

    PowerPoint application does not support OnTime method, for your issue, I suggest you use SetTimer/KillTimer api to do this job.

    Here is the example.

    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

    Private Declare Function GetForegroundWindow Lib "user32" () As Long

    Private hWnd As Long

    Sub StartOnTime()

      hWnd = GetForegroundWindow

      SetTimer hWnd, 1, 60000, AddressOf UpdateExcelLinks

    End Sub

    Sub KillOnTime()

       KillTimer hWnd, 1

    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

    End Sub

    You could get more information from below link

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_powerpoint-msoffice_custom/using-settimer-api/b99bc473-d721-458b-a6f7-f0f1be0a693d

    Best Regards,

    Terry

    Thursday, August 17, 2017 2:30 AM
  • Hello Terry,

    I tryed your code but the method startontime() is failing, with the error message that the types are not matching.

    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long

    Public Declare PtrSafe Function GetForegroundWindow Lib "user32 () As Long" ()
    Public hWnd As Long

    Sub StartOnTime()

      hWnd = GetForegroundWindow(SetTimer(hWnd, 1, 5000, AddressOf UpdateExcelLinks))

    End Sub

    Sub KillOnTime()

       lngTimerID = KillTimer(hWnd, 1)

    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
    End Sub

    I'm running this code on Office 64 bits, thats's why I used PtrSafe in the declare part. 

    Do you have any idea on the error?

    Is there some another way to  perform automatic execution of my UpdateExcelLinks macro ?

    Best regards,

    Anton.

    Thursday, August 17, 2017 7:16 AM
  • Hi antonanton5,

    Please try to change code like this.

    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As Long

    Sub StartOnTime()

      'hWnd = GetForegroundWindow(SetTimer(hWnd, 1, 5000, AddressOf UpdateExcelLinks))

     hWnd = GetForegroundWindow

      SetTimer hWnd, 1, 5000, AddressOf UpdateExcelLinks

    End Sub

    Please refer to

    https://support.microsoft.com/en-us/help/2210978/you-must-declare-the-return-type-explicitly-in-a-vba-macro-that-you-ru

    Best Regards,

    Terry

    Thursday, August 17, 2017 9:24 AM