Subscriber Linda Jordan wrote with an unusual request. She’s working with an Excel file, and needs other employees to be able to access the same information.
I’m a relatively new fan of you and your site. Thanks for sharing your expertise. I’ve used several tips already.
I have two spreadsheets exactly the same, I want to be able to input data into my working copy and it update the public copy automatically. This is a multi-sheet document with the first sheet set up as an index with hyperlinks to each tab.
I will be adding new sheets, new info or adding info to each spreadsheet as our department grows. I would prefer not to have to do double work by inputting into each doc separately. I use my working copy daily, but other people need to see the same info sometimes without me having to close out.
Can you help?
I wrote to Linda noting that she needs to make changes, save the file, and have another file updated automatically.
Excel can link one spreadsheet to another using something called OLE – Object Linking and Embedding. That would work, except for your other requirement – that you want to add new sheets to the spreadsheet or new info (rows & columns that previously were blank).
Now, you’ve got a task that your IT people can help you with. Or, you may be able to handle it. The key is that the steps are in Windows, not in Excel.
You need to create a command file (somethingname.cmd) that copies your working file (after you save it) to the location where the other people can access it.
You also will want to be able to:
1) make the command job run when you want it to run, and
2) make the command job run on a schedule using the Windows task scheduler.
If you need multiple people to be able to access it at the same time, perhaps you can save the spreadsheet in HTML format. Use Excel’s Save As function and change the file extension to .html and the Save As Type to "Web Page (*.htm, *.html). Your IT people should be able to store that on their internal web server, assuming you have an intranet at work.
Another way to do it is to store the copy of the file on a network server, in a directory where you have both read and write capability, but your readers have read-only capability. This could be set up to be a manual action on your part, or the copying could be automated via the Windows Task Scheduling.