none
How to automatically update links at Powerpoint every 2 minutes in a Macros? RRS feed

  • Question

  • I have a PowerPoint file, which has a link with an Excel file. This PowerPoint file will get data from the Excel file. Now we want to  automatically update links at Powerpoint every 2 minutes in a Macros. How to do it? Thanks! 
    Thursday, August 28, 2014 11:25 PM

Answers

  • I got the answer from here. It works! Thanks for all replies.

    http://www.experts-exchange.com/Software/Microsoft_Applications/Q_27853468.html

    • Marked as answer by FugersonHall Tuesday, September 2, 2014 6:06 PM
    Tuesday, September 2, 2014 6:06 PM

All replies

  • Excel has an Application.OnTime method that does what you want. PowerPoint does not. So one solution is to use the OnTime method in Excel to write the data to PowerPoint rather than have PowerPoint read the data from Excel.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Monday, September 1, 2014 9:01 AM
  • As I see, you can't reflesh hyperlinks in PP. You can change addresses only.

    Sub lkl()
    Dim h As Hyperlink, s As Slide, x&, inf$
    Dim asl As Slides: Set asl = ActivePresentation.Slides
    For Each s In asl
        For Each h In s.Hyperlinks
             x = x + 1
             inf = inf & x & " " & h.Address
        Next
    Next
    If Len(inf) > 0 Then _
    MsgBox inf, vbExclamation, "VBATools.pl"
    End Sub


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Monday, September 1, 2014 3:23 PM
    Answerer
  • Hello,

    There might be some confusion here no?

    You can update links (he's not talking about hyperlinks).

    eg:

     Dim objPpt As New PowerPoint.Application, Pres As PowerPoint.Presentation, _
             shp As PowerPoint.Shape
       
        objPpt.Visible = True
        objPpt.DisplayAlerts = ppAlertsNone  
        Set Pres = objPpt.Presentations.Open(strPptPath)
        With Pres
             For Each shp In .SlideMaster.Shapes
                If shp.Type = msoLinkedOLEObject Then
                    If InStr(1, UCase(shp.LinkFormat.SourceFullName), ".XLS", vbTextCompare) > 0 Then
                            Call shp.LinkFormat.Update
                        End If
                End If
            Next 
        End With

    I would suggest you run a proces in batch, that runs every two minutes (some kind of executable), to refresh your powerpoint.

    Tuesday, September 2, 2014 6:46 AM
  • Hi Wouter

    I check it out your code. Thanks for correct me. 

    Anyway your code return:

    Title Placeholder 1
    Text Placeholder 2
    Date Placeholder 3
    Footer Placeholder 4
    Slide Number Placeholder 5

    This code should have 2 loops:

    Dim objPpt As New PowerPoint.Application
    Dim Pres As PowerPoint.Presentation: Set Pres = ActivePresentation
    Dim asl As PowerPoint.Slides:        Set asl = Pres.Slides
    Dim shp As PowerPoint.Shape
    Dim s As PowerPoint.Slide
    
        objPpt.Visible = True
        objPpt.DisplayAlerts = ppAlertsNone
        
        For Each s In asl
            For Each shp In s.Shapes
                On Error Resume Next 'LinkFormat (unknown member) : Invalid request.
                 Debug.Print shp.Name
                 shp.LinkFormat.Update
            Next
        Next
    What you think?


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Tuesday, September 2, 2014 10:19 AM
    Answerer
  • You're right. The slidemaster only contains some formatting. You should loop every slide.
    Tuesday, September 2, 2014 11:04 AM
  • How to run a process in batch at Powerpoint? Thanks!
    Tuesday, September 2, 2014 4:18 PM
  • I got the answer from here. It works! Thanks for all replies.

    http://www.experts-exchange.com/Software/Microsoft_Applications/Q_27853468.html

    • Marked as answer by FugersonHall Tuesday, September 2, 2014 6:06 PM
    Tuesday, September 2, 2014 6:06 PM
  • Rod I was trying use early and late binding to Excel in PP and:

    In late binding OnTime does not react at all, but in early, code do not find name of procedure (I've prompt "macros are disabled").

    So solution is only in COM add-in


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Wednesday, September 3, 2014 10:02 PM
    Answerer