Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have a pretty unique situation at work. We have order takers who take orders and each individual references the same shared read-only excel document on a network storage drive. This excel document contains multiple sheets that has prices and other formulas that contribute to correct pricing for a customer's order. Along with those sales prices there are sheets that contain reference information, etc.

Certain places where the orders are being filled only have so many resources available. We would like to include in this shared, read-only spreadsheet information that will automatically update without having each user close and re-open the excel document.

It is my understanding that you can specify a data source (such as a database or a web document, etc) to pull some of this information directly into the excel document. I have thought about creating a local web page to feed data into the main excel spreadsheet which is on a network drive.

We are running Office 2003 (still) but have Office 2007 on some computers. Additionally most of the computers in the office are Windows XP and we are close to migrating everyone to Windows 7 64-bit machines.

Summary:

  • Automatically push information into a shared, read-only excel document.
  • The excel document is saved as a 97-2003 .xls file.
  • The computers are all currently running Windows XP, upgrade coming in a few months.

What is the best way to set this up? Any suggestions without having our office re-invent the wheel?

share|improve this question
1  
A little confused on what this has to do with network infrastructure design and configuration? –  SpacemanSpiff Jul 10 '12 at 17:58
add comment

closed as off topic by Evan Anderson, Tim Brigham, rnxrx, Ward, Scott Pack Aug 27 '12 at 17:03

Questions on Server Fault are expected to relate to professional server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

If you really want to stick with Excel, consider using its "export to HTML" features. You can maintain a master copy of the document, export it as HTML to a web server upon change (maybe with a macro), and people will only have to hit refresh to see their updated data. You can even add in an automated refresh say once an hour with a META tag or JavaScript.

However, Excel is not a database. You have identified its many limitations for this use case. I suggest that you start re-architect things now before the pain gets much worse. Put normalized "source data" (inventory and costs) into a real multi-user database server that can be updated (SQL Server Express is free), and business logic (pricing formulas, etc) into some form of web application or reporting tool. A simple set of web pages with business logic in your language of choice would be appropriate to start. They could even be static HTML pages with all logic in JavaSscript using something like dojo.data, and you can have a very simple web service which queries the database returns the source data in whatever format you like (XML, JSON, CVS, etc.)

share|improve this answer
1  
YES! A thousand times, yes! Stop using Excel as a database. –  Ryan Bolger Jul 11 '12 at 16:25
    
I'm not using Excel as a database. I want to pull info from a data source (database, website, etc) INTO an excel document that is shared and read-only. –  cyborgcommando0 Jul 11 '12 at 17:53
    
It's not read-only if you want to update it for multiple viewers while they have it open. –  rmalayter Jul 12 '12 at 3:22
add comment

We do this with some of our business metrics...

Create a local database (MySQL, access, csv file, etc) and create a DSN for it on each computer that will be accessing the spreadsheet...

Then go into a new excel tab and insert external data...point to your DSN and Bob's your uncle.

Actually a few more steps in there, but it works well for us.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.