Excel Auto Refresh Standalone

Posted on 2014-01-23
Last Modified: 2014-01-25
Hi all, trust you are keeping well!

Just stumped on this one.

Client wants a shared excel workbook to be displayed on a big screen down in their factory. The spreadsheet will be updated by the admin girls. The factory PC / Big screen needs to displayed the updated spreadsheet automatically and refresh every ten minutes.

Can this be done - using VBA/Macros or is there a better way?

Question by:SeanNij
LVL 48

Accepted Solution

Rgonzo1971 earned 167 total points
Comment Utility

Go to Review / Changes / Share Workbook / Advanced
Update Changes Automatically Every

Change to 10 Minutes

Click OK

LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 167 total points
Comment Utility
As soon as you mention the word Shared in Excel, the sirens start ringing. The Shared Workbook function in Excel is notoriously unreliable causing various issues ranging from File Size bloating to outright corruption.

The consensus of opinion around various EE threads is not to use the Shared Workbook function.

If the display version is purely for Display it doesn't need to have Write Access so therefore can be Read Only. As such there could be a routine on the machine displaying that file to close it and re-open the saved copy every 10 minutes. That would however be reliant on the Admin team ensuring they save changes as they are made so that the factory display version shows the latest version.

Will the Admin team need concurrent access, ie more than one person at a time?

We don't know the full scenario but do each of the Admin team have their own section to update?

How about this for an alternative scenario? Each Admin team member has their own workbook to update, these individual workbooks are linked to a master workbook which is then displayed in factory. Routine runs on Display machine to update the links every 10 minutes.

Rob H
LVL 19

Assisted Solution

regmigrant earned 166 total points
Comment Utility
just my five cents:-

in addition to Rob's comments above I would guess that they don't want to see the actual spread sheet data - since no matter how large the screen you wont be able to see/interested in numbers.

A separate 'dashboard' style workbook charting the information in the shared workbook would not need write access and would present data in a more visible and useful manner. The update points Rob makes still stand but at least this would de-couple the factory display from the update workbook

Author Closing Comment

Comment Utility
Guys, I've been struggling to award the points, as all the effort you presented is worth more then 500 "points".
That been said, I have split 500 equally between the three of you.
My reason, just cause that's as fair as I can be.
Rgonzo1971 - answered the question as if I asked it without thinking laterally - so he has to get the points, however, Rob and Regmigrant, you both have answered it laterally.
You have all given me more then I bargained for! Thank you.

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now