Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Excel Auto Refresh Standalone

Posted on 2014-01-23
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 53

Accepted Solution

Rgonzo1971 earned 668 total points
ID: 39803052

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

Change to 10 Minutes

Click OK

LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 668 total points
ID: 39803478
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 664 total points
ID: 39803887
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

ID: 39809721
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

618 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