Is anybody reading your report?

Today I want to share the biggest time-saving tip of all.  It’s not a tip which is specific to Excel, but to everything you do.

Here it is:

Stop doing things you don’t need to do.

It’s not exactly rocket science is it.  But, if you can crack this then you’ll save a lot of time.

A number of years ago I worked as a project accountant.  There were approximately 90 active projects at any one time, plus a few hundred projects in the pipeline.  All of these projects had to be tracked.  Each month we issued progress reports on every project.  It took us days of time (often working late) to get updates from each project manager, to collate all of that information together in a consistent manner and to issue a report.  My year-end objectives were set based on the quality and timeliness of this report, so we worked hard to get it done.

We never received any feedback or questions on the report, this was used by me as evidence of how comprehensive it was.  I’m sure you’ve guessed it, the truth is, nobody was actually reading it.  One time due to illness it was not sent on time, there were no comments or complaints.  So I decided not to send it the following month and see how long it was before somebody would request it.  It was 9 months passed before any even noticed.

Then, I was informed that this was a key report to the success of the organization and was forced to send it again, every month.  We certainly stopped busting ourselves to get it done.

I would love to save you from the same problem – then you can reclaim hours of your life back.  How can we find out if anybody is looking at our Excel reports?  Well, that partly depends on how you distribute your reports and what type of IT systems you have:

• Automatic e-mail sent to you from Outlook each time the report is opened
• Record the usage of the file within the file itself
• Record the usage of the file within a separate file

In many circumstances these three options will still not work.  But you might be able to change your distribution method so that one of them could work.

Automatic e-mail sent from Outlook

If your organization uses Outlook then you can automate a message which is sent to you once the workbook is opened.  Insert the following code in the This Workbook section of the VBA editor.  If you’re not sure how, follow my Beginning VBA course.

Private Sub Workbook_Open()

Dim OutlookApp As Object
Dim OutlookItem As Object
Dim Message As String
Dim MessageAnswer As Long
Dim UserName As String
Const OutlooklMailItem As Long = 0

'Link to Outlook using Late Binding
On Error Resume Next
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookItem = OutlookApp.CreateItem(OutlooklMailItem)

'If linking to Outlook causes error, because Outlook can't be found, then exit sub
If Err.Number <> 0 Then Exit Sub

'Reset errors after error message
On Error GoTo 0


'Ask permission to send an e-mail
Message = "We are conducting a survey to understand usage of this report."
Message = Message & vbNewLine & vbNewLine
Message = Message & "Are you happy for outlook to send a message to confirm you have opened the report?"
Message = Message & vbNewLine & vbNewLine
Message = Message & "Yes - Send an e-mail" & vbNewLine
Message = Message & "No - Do not send an e-mail" & vbNewLine

MessageAnswer = MsgBox(Message, vbQuestion + vbYesNo, ThisWorkbook.Name)

'If the user clicked 'Yes' then send the mail
If MessageAnswer = vbYes Then
    UserName = Application.UserName
    With OutlookItem
        .To = "you@your-email-address.com"
        .Subject = UserName & " opened " & ThisWorkbook.Name
        .BodyFormat = 2
        .HTMLBody = "Your message here"
        .send
    End With

End If

End Sub

Record usage within the file

If the individual opening the file does not use Outlook, then the above code won’t work.  But if the report is sent as a link on a shared server, then you could record the usage of the report within the report itself.

Insert a worksheet called Users, make that worksheet invisible and then insert the following code in the This Workbook section of the VBA editor.  If you’re not sure how, follow my Beginning VBA course.

Private Sub Workbook_Open()

'Find the last used row in a Column: column A in this example
Dim LastRow As Long
Dim Message As String
Dim MessageAnswer As Long

'Ask permission to record the users access
Message = "We are conducting a survey to understand usage of this report."
Message = Message & vbNewLine & vbNewLine
Message = Message & "Are you happy for us to record that you have opened the report?"
Message = Message & vbNewLine & vbNewLine
Message = Message & "Yes - Record my access of this report" & vbNewLine
Message = Message & "No - Do not record my access of this report" & vbNewLine

MessageAnswer = MsgBox(Message, vbQuestion + vbYesNo, ThisWorkbook.Name)

'If the user clicked 'Yes' then record their user name
If MessageAnswer = vbYes Then
    LastRow = ThisWorkbook.Sheets("Users").Cells(ThisWorkbook.Sheets("Users").Rows.Count, 1).End(xlUp).Row
    ThisWorkbook.Sheets("Users").Cells(LastRow + 1, 1).Value = Application.UserName

End If

End Sub

This code has other uses too.  If you want to track any usage of your files, then you could ‘spy’ on who is opening those files.  You could adapt the code as follows:

Private Sub Workbook_Open()

'Find the last used row in a Column: column A in this example
Dim LastRow As Long

LastRow = ThisWorkbook.Sheets("Users").Cells(ThisWorkbook.Sheets("Users").Rows.Count, 1).End(xlUp).Row

ThisWorkbook.Sheets("Users").Cells(LastRow + 1, 1).Value = Application.UserName

End If

End Sub

Is it ethical to do this?  Maybe not, that’s for you to decide.  But is it possible? certainly.

Record usage within a separate file

What if the individual opening the workbook does not use Outlook and the report is not distributed as a linked file?  We have another option.

If the individual is on the same network as you, then you could set-up a separate Excel file to record the usage.  Each time the report is opened, another workbook is opened in the background, the username is recorded, then the workbook is saved and closed again. Insert the following code in the This Workbook section of the VBA editor.  If you’re not sure how, follow my Beginning VBA course.

Private Sub Workbook_Open()

'Find the last used row in a Column: column A in this example
Dim LastRow As Long
Dim Wb As Workbook
Dim Ws As Worksheet
Dim Message As String
Dim MessageAnswer As Long

'Ask permission to record the users access
Message = "We are conducting a survey to understand usage of this report."
Message = Message & vbNewLine & vbNewLine
Message = Message & "Are you happy for us to record that you have opened the report?"
Message = Message & vbNewLine & vbNewLine
Message = Message & "Yes - Record my access of this report" & vbNewLine
Message = Message & "No - Do not record my access of this report" & vbNewLine

MessageAnswer = MsgBox(Message, vbQuestion + vbYesNo, ThisWorkbook.Name)


'If the user clicked 'Yes' then record their user name in another workbook
If MessageAnswer = vbYes Then
    
    Set Wb = Workbooks.Open("C:\FilePath\WorkbookName.xlsx")
    Set Ws = Wb.Sheets("Users")
    LastRow = Ws.Cells(Ws.Rows.Count, 1).End(xlUp).Row
    Ws.Cells(LastRow + 1, 1).Value = Application.UserName
    
    Wb.Save
    Wb.Close

End If

End Sub

Other solutions

If you’ve got other solutions to solving this problem, then please share them in the Comments section below, so that we can all learn together.