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

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 2

I like the suggestions being made so far and what you wrote. I personally like to keep things simple so here's my humble suggestion. It sounds like you have a lot of templates you may be managing and with excel things get messy quick and it's hard to know that formulas are not being tampered with. With that in mind I'd forget any database change management solution, instead:

• Create a share drive folder that's set to read only and accessible by everyone in the company
• You can create a sub folder structure that makes sense by team, department, location, whatever works
• Store the latest copies of the excel templates in the folders
• I'd also suggest locking the templates that have critical calculations in them --> Try to leave it as open as you can so they can be customized where they need be but at your discretion

You may also consider setting up a version control repository that manages the changes to this folder structure. Look into version control with a simple interface like tortoiseSVN so you can track what changes were made and when. The standard share drive back ups are a life saver but I'd still supplement with a version control system (just makes things a little easier). Here's a couple of links to help you get started, you can try subversion locally and see what you think:

Instructions to setup Subversion on Windows

Tortoise Client to interact with Subversion

Also note, IF you chose to implement some kind of database connection for a spreadsheet to perform logging on a database server as has already been noted you can use "on error resume next". Here's what you can do:

• After resume next attempt to open the connection to the DB

• You can choose to handle the error then with an if statement such as:

  if err.number = 3024 then
      msgbox "Database file not found, check network connection and retry"
      exit
  end if
  

Here's a link to look up additional error codes for trapping in similar fashion:

Error Trapping in VBA

Answer 3

I do something very similar to this to check the current version of the Excel application. You could just as easily use this same code to make a web-request to a server that will log 'hits'. Here's my code:

In ThisWorkbook:

Option Explicit

Private Sub Workbook_Open()
    Updater.CheckVersion
End Sub

Elsewhere (in a module called Updater)

Option Explicit

Const VersionURL = "http://yourServer/CurrentVersion.txt"
Const ChangesURL = "http://yourServer/Changelog.txt"
Const LatestVersionURL = "http://yourServer/YourTool.xlsm"

#If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If


Public Sub CheckVersion()
    On Error GoTo fail
    Application.StatusBar = "Checking for newer version..."

    Dim ThisVersion As String, LatestVersion As String, VersionChanges As String
    ThisVersion = Range("CurrentVersion").Text
    If ThisVersion = vbNullString Then GoTo fail

    LatestVersion = FetchFile(VersionURL, , True)
    VersionChanges = FetchFile(ChangesURL, , True)
    If LatestVersion = vbNullString Then
        Application.StatusBar = "Version Check Failed!"
        Exit Sub
    Else
        If LatestVersion = ThisVersion Then
            Application.StatusBar = "Version Check: You are running the latest version!"
        Else
            Application.StatusBar = "Version Check: This tool is out of date!"
            If (MsgBox("You are not running the latest version of this tool. Your version is " & _
                ThisVersion & ", and the latest version is " & LatestVersion & vbNewLine & _
                vbNewLine & "Changes: " & VersionChanges & vbNewLine & _
                vbNewLine & "Click OK to visit the latest version download link.", vbOKCancel, _
                "Tool Out of Date Notification") = vbOK) Then
                ShellExecute 0, vbNullString, LatestVersionURL, vbNullString, vbNullString, vbNormalFocus
            End If
        End If
    End If
    Exit Sub
fail:
    On Error Resume Next
    Application.StatusBar = "Version Check Failed (" & Err.Description & ")"
End Sub

As you can see, error handling is in place to make sure that if the URL is unavailable, the app doesn't crash, it just writes a message to the user in the status bar.

Note that if you don't want to set up a web service that does this, you could try to have the spreadsheet write to a database - you could still re-use a lot of this code, but not as much of it.

Answer 4

Your idea is good. Database availability is usually higher than the availability of the users' laptop. And there is a kind of primitive error (exception) handling in VBA, so they won't necessarily see freaking error messages.

Yes, you have a loss. Any uses of the sheet saved offline when the user is not on your network - will be missing from the database. But I don't think that there's a 100% foolproof solution for this.

Try to search for a Financial Times article like "Excel - a tool that is too ad hoc and open for errors". Even the title says it all.

Answer 5

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 6

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.

Answer 7

You could do a file-based approach with any of several file integrity monitoring solutions. Samhain is one free open source example. That would allow your employees to access their spreadsheets without interference, but would report when new spreadsheets are discovered or when their timestamps or hash values change. It would also detect changes made while the developer was off-line (on their laptops, for example) once they've reconnected to the network.

Answer 8

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.