Tracking usage data for excel macros

[Cuz77]

Hi guys,

I spent considerable amount of time today searching for an answer and didn't find anything so I thought that someone here might be able to help (I often look for solutions here but didn't create an account till now).

I'm responsible for developing and supporting some macros at work and with the number of macros growing (over 25 at this point) it became essential to prove to management that my work is relevant. Unfortunately this is not easy in a corporate world so I'm looking for the best way to track how often people use macros I support There are multiple users in multiple locations. I've noticed that there are some old and complex macros that updates existing xml files by adding new tags every time a particular macro is being used. Each macro has a separate xml file for each user in a shared location. This is however inefficient since consolidating data takes A LOT of time (there is this one guy who spends more than entire day each month to gather data from just few macros). I believe that even a CSV file would be more efficient to handle but maybe I don't have correct approach to the issue.

The question is: is there any standard for generating usage reports for macros that are being used by multiple individuals?

PS. I'm using Office 365.

 

[skorpionkz]

Hi Cuz77,

I am not sure about standard approach, but I made my own which sits in the access DB, you can change code and link it to cvs.

NOTE: I just cut it from much bigger code and changed some parts of the code, therefore it might have some minor error, but should be enough, so you can get an idea.

Option Explicit


Public MyFolder As String
Public Const pbl_LOG = "Log_Database.accdb"


Private Sub UserLog()


Dim l_ID As Long
Dim str_Headers As String
Dim str_Table As String
Dim arr_Val(1 To 3) As Variant
    
MyFolder = "Access Log Folder"


str_Headers = "UserName, [Log In], [Macro_Name]"
str_Table = "Usage_Log"


arr_Val(1) = Environ("username")
arr_Val(2) = Now
arr_Val(3) = "This Macro Name"
l_ID = CreatingRecord(str_Table, str_Headers, arr_Val)


End Sub


Public Function CreatingRecord(ByVal str_Table As String, ByVal str_Headers As String, ByRef arr_Values() As Variant) As Long


    Dim myConnection            As ADODB.Connection
    Dim myCommand               As New ADODB.Command
    Dim sSQL                    As String
    
    Dim str_Values As String
    Dim i As Integer
    
    If UBound(arr_Values, 1) < 1 Then Exit Function
    
    On Error GoTo CreateRecordErrorHandler
    
    Set myConnection = ConnectTo_DB
    
    ' CREATE RECORD BODY
    For i = 1 To UBound(arr_Values, 1)
        If i = 1 Then
            str_Values = "p" & i
        Else
            str_Values = str_Values & ",p" & i
        End If
    Next i


    sSQL = "INSERT INTO " & str_Table & " (" _
    & str_Headers & _
    ") VALUES (" _
    & str_Values & ")"
    
    With myCommand
        .ActiveConnection = myConnection
        .CommandType = adCmdText
        .Prepared = True
            For i = 1 To UBound(arr_Values, 1)
               .Parameters.Append .CreateParameter("p" & i, adBSTR, adParamInput, , arr_Values(i))
            Next i
         'Debug.Print sSQL
        .CommandText = sSQL
        .Execute
    End With
    
    ' CREATE RECORD END
     
    CreatingRecord = myConnection.Execute("SELECT @@Identity", , adCmdText).Fields(0).value 'FIND ID NUMBER OF ADDED ELEMENT
        
    myConnection.Close
    
    Set myConnection = Nothing
    
    On Error GoTo 0
    Exit Function
CreateRecordErrorHandler:


    MsgBox "An Error has occured" & vbCrLf & vbCrLf & Err.Description, vbCritical, "Databse Error"
    'Debug.Print sSQL
    Err.Clear
End Function


Public Function ConnectTo_DB() As ADODB.Connection 'As ADODB.Connection


    Dim myConnection            As New ADODB.Connection
    Dim sConnectionString       As String
    Dim str_Link As String
    
    Const myPassword = "My Password"
    
    str_Link = MyFolder & pbl_LOG
    sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & str_Link & _
                        ";Jet OLEDB:Database Password=" & myPassword & ";"
                        '";Persist Security Info=False;"
                        
                        
    
    myConnection.Open sConnectionString
    
    Set ConnectTo_DB = myConnection
    
End Function

 

[skorpionkz]

Little clarification. The log sits in the access DB. The code it self, will need to be part of the macro you want to track usage.
Whenever user will use your macro it will need to trigger "UserLog" code

 

[Cuz77]

Thank you so much!

I analyzed your piece of code and figured out something really easy that can be adopted and implemented into any macro:

Option Explicit

Sub UserLog()

Dim DBconnection As ADODB.Connection
Dim DBrecordset As ADODB.Recordset
Dim DBFullname As String

DBFullname = "S:\Reports Team\CBR Reports\CBR5037\usage reports\User log.accdb"
Set DBconnection = New ADODB.Connection
Set DBrecordset = New ADODB.Recordset

DBconnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & DBFullname & ";Persist Security Info=False;"
DBconnection.Open

With DBrecordset
    .Open "CBR5037", DBconnection, adOpenKeyset, adLockPessimistic, adCmdTable
End With

    DBrecordset.AddNew
    DBrecordset!TimeStamp.Value = "time value"
    DBrecordset!UserName.Value = "user"
    DBrecordset!Site.Value = "POLAND"
    DBrecordset.Update
    DBrecordset.Close
    Set DBrecordset = Nothing
    DBconnection.Close
    Set DBconnection = Nothing

End Sub

It returns a record with three fields but I can easily manipulate the output for my needs. I have just one concern. So far this code takes just a second to execute and everything works fantastically. Nevertheless, I'm thinking about something around 200-300 new records every day from multiple users (often few of them trying to add a record at the same time). I will be the only one reviewing this data via a front-end file's queries and macros while users will be adding records to the back-end database. Do you think that they won't interfere with each other and won't create an excessively large file?

 

[skorpionkz]

So far this code takes just a second to execute and everything works fantastically. Nevertheless, I'm thinking about something around 200-300 new records every day from multiple users (often few of them trying to add a record at the same time). I will be the only one reviewing this data via a front-end file's queries and macros while users will be adding records to the back-end database. Do you think that they won't interfere with each other and won't create an excessively large file?

If you use Access to store the data, you should not have issue with multiple users, but I am not an Access expert .
Also you can have a query in Access that will create monthly tables and move data into it. This way you will have small archive tables for each month and one table for current month (less than 10000 rows each), but to have quarterly or annual report you will have to rejoin the data. Although I don't believe it is necessary.

 

[Cuz77]

This is really convenient and I'm very excited that there is such an elegant solution to my problems I can go back to creating forms, queries, and reports for management.

Thank you!