Consulting

Results 1 to 2 of 2

Thread: Auto-update links PPT and Excel

  1. #1

    Auto-update links PPT and Excel

    Quick question.

    I am going to have a PowerPoint presentation running constantly on a monitor in my group's area. This PPP will be pulling data from an Excel workbook that is updated frequently.

    Is there a way to force PowerPoint to auto update by way of vba code say every 5 min or whenever the source changes?

    One more thing. My group is going from office 03 to office 07 so the solution will have to at least work for 07.

    thanks for your help!

    -wm



  2. #2
    VBAX Regular mike31z's Avatar
    Joined
    Apr 2005
    Location
    Highland, Wisconsin
    Posts
    89
    Location

    Smile refresh links

    http://www.vbaexpress.com/forum/show...661#post205661

    The link is almost the same subject look at that frirst the come back here.

    Lesson learned for this type of project: Since your source is on ( x ) number of files that others input data into, I recommend you create a seperate spreadsheet with all the links to other xls file that are network locations and store that consolidated spreadsheet in the same folder as the powerpoint file.

    The new problem is to automatically update(refresh) the spreadsheet file links. Here how I do it.

    This goes in a Module for the spreadsheet that is the interface to the powerpoint.

    Dim mdteScheduledTime As Date
    '
    'change the \\Squad\team09\Team09.xls to your source file
    ' add extra lines for each file location
    'source data must be saved file can be open.
    'Change (0,3,0) to meet your needs time is set at 3 min
    'Format for time is (Hours, Minutes, Seconds)
    '
    '
    'Good luck
     
         Sub RefreshData()
             ThisWorkbook.UpdateLink Name:="\\Squad\team09\Team09.xls", Type:=xlExcelLinks
             mdteScheduledTime = Now + TimeSerial(0, 3, 0)
             Application.OnTime mdteScheduledTime, "RefreshData"
         End Sub
         Sub StopRefresh()
             Application.OnTime mdteScheduledTime, "RefreshData", , False
         End Sub
    To run the code open your MACROS (ALT+F8) and RUN "RefreshData"
    That turns the timer on.
    At the end of the slide show RUN "StopRefresh" if you are going to keep using excel for other projects.

    The xls source data must be in some kind of shared folder.
    I would create a Mapped drive using the same letters so the code doesn't have to be changed.
    You may need to verify the mapped drive link before the show starts but that should only be daily.



    good luck

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •