2

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?

cyborgcommando0
  • 123
  • 1
  • 7

2 Answers2

5

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.)

rmalayter
  • 3,744
  • 19
  • 27
1

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.

Brian Adkins
  • 420
  • 3
  • 7