Live feed from Excel, possible?

[Dancey_]

Hey guys,

Please can you help me?

I have an excel database, that is updated by users (using a macro) which records the 'sales' they have made as they make them.

Can anyone help me with regards to being able to update a 'leaderboard' style spreadsheet, or powerpoint, or something similar so that it can be displayed on a big TV in our office?

My initial ideas is have a spreadsheet that has Sheet1 that is just a linked copy of the database, and then on Sheet2 have a pivot table as a leader board.

I have a Macro to update pivot tables however I can only trigger the macro manually, is there any way that I can make the table update every thirty seconds?

If it can be done a different way I am completely open!

Cheers

 

[dantheman9]

Hi Dancey,

I have been working on something long the same lines with help fro mthis forum.
I Started out (before using vba), by using the Update Links addin (just google it to get the website). that wored fine, but it would take time to setup all the links correctly to update the screen.

Recently I have been trying out using vba to create a html file, which will also refresh a scrolling marquee in internet explorer (maybe not the best but still im still learning lots about coding things!!).

My system is based on looking for new text files, then updating the html file, before working out if the page should be refreashed (which im not sure I have right).

The first method looked better (as I could style the show etc), the second method is quick to setup (as no need to develop the layouts etc which is all done in vba).

for your Pivot table you can use the Now() Time option which will rerun code at what ever interval you set it too, or as mentioned try the update links addin for powerpoint (which will update all links on on each cycle of the show)

 

[Dancey_]

Hi,

Thanks for the info, it hasnt given me lots to go on with.

Do you have an example of the NOW() function working, I cant seem to make it work?

I think that would be the easiest way for me, would be to refresh the pivot table every thirty seconds with that code until I tell it to stop!

Cheers

 

[dantheman9]

Sub Reloadcode()
Dim TimeRun as Date
   
Msgbox("Code running")
'Set Timerun value to current system time + 30 seconds
Timerun = Now() + TimeValue("00:00:30")    
' make the system reload the sub at Timerun (set above)
Application.OnTime Timerun, "Reloadcode"
 
End Sub

...so with the above you will want to add the last couple of lines to the end of your update (via vba).

To stop the Sub loop you can use the below code (in another sub/button) (and set Timerun as Public Timerun as Date at the top of the code)

Sub stopload()
Application.OnTime Timerun, "Reloadcode", , False
Timerun = 0
 
End Sub