Capturing spreadsheet usage throughout a company

Question

We have a lot of customized spreadsheet solutions that are being used and we want some programmatic way of keeping track of them. Obviously since they are spreadsheets, people can save them locally, rename them, etc so we need a solution that can account for that.

Some ideas are:

1. On spreadsheet open, handle the OnOpen event and write a message to a database for tracking

the issues with this are where do we store database details. If the database is down, we dont want the spreadsheet to crash, etc

has anyone come up with a good spreadsheet inventory management solution that handles all the issues above.

Answer 1

write to the db, if the write fails, catch the error and send an e-mail to someone that can manually increment the count when the database is back up.

Answer 2

Have the excel spreadsheet make a request out to a web server.

Add msinet.ocx to your toolbox and create a form with the Inet control. Add the ocx by right clicking somewhere in the toolbox area.

Then you can set the location of the Inet control somewhere you can handle that the spreadsheet was opened.

Answer 3

I don't understand the problem you're trying to solve here: you don't need spreadsheet usage logging as an end-result, something is causing pain and this is what you've devised to try to fix it.

If you need seriously reliable logging of all spreadsheet usage, then I don't think this is going to work. If you need mostly reliable logging, then just use a database and don't worry about the (rare) occasions that the database is down. On Error Resume Next should be enough to ensure the spreadsheet continues in that event.

That said, I'd be more inclined to go for a web-based solution: that way you don't have to get involved with ensuring everyone has the necessary database drivers, working connection strings and other horridness.

Some more awkward questions that are making me think that you may need another approach:

How are you going to deploy changes to your logging solution?

Do your users have control over their macro security level? Or the ability to write and edit macros? Could they therefore (innocently or otherwise) disable logging?

Can the users operate offline? What happens then?

Answer 4

Although you may need logging in the short term, the long term solution should be to bring your spreadsheets under control. You should gather the "definitive" copy of the spreadsheets, and move them to a file share, where they will all be protected - users will be able to change the data in them, but will be unable to change the formulas.

If you need a more controlled collaboration solution, then you should look into using SharePoint, possibly the MOSS version which has Excel Services on it.

You might also need to explore how the spreadsheets are being used. Perhaps they are being used instead of someone writing a program, and in some cases, it may be time to do that.

Lastly, you don't want to track spreadsheet usage - you don't care if someone creates a spreadsheet to track their kid's soccer team scores. It's particular spreadsheets you're interested in. The logging may help you track that down to start with, but that's all it can help you with.