Macro counter

[reggie55555]

I'm wondering if anyone has come up with a solution to count how many times a macro is executed. We have some macros distributed to multiple users and I'd like to know how many times they run them.

I thought of including some script to have a tally produced on a central excel spreadsheet but I can't get around multiple users using the macro at the same time.

Any thoughts?

 

[coswa]

Try using Access

Sub Add_To_DB(MyThing1 as string, MyThing2 as string)

    Dim cmd as object
    Dim conn As Object, rs As Object, db As String, sql As String 
    dim iCount as integer


   
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    set cmd = createobject("ADODB.Command")
   
    db = "S:\MACROCOUNTER.mdb"
    sql = "Select COUNT_ITEM_1, COUNT_ITEM_2 from MACRO_USE;"
    
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db & ";User Id=admin;Password=;"
    rs.Open sql, conn
    cmd.ActiveConnection = conn


    
    sql="insert into Macro_Use (Count_Item_1, Count_Item_2) values ('" & MyThing1 & "', '" & MyThing2 & "')"

    
    
    cmd.CommandText=sql
    cmd.Execute

    
    rs.Close
    conn.Close
    
    Set rs = Nothing
    Set conn = Nothing
    set cmd = nothing


End Sub

 

[SkipVought]

coswa,

There is an inherent problem with your solution.

db = "S:\MACROCOUNTER.mdb"

1. it assumes that user x has access to the server that you have mapped to the S Drive.

2. it assumes that user x, having access to the that server, also has that server mapped to the S Drive. Might be better to...

db = "[b]\\SomeServer\ServerShare[/b]\MACROCOUNTER.mdb"

Other than that, it is a workable solution.

BTW, you must have a reference set to the Microsoft ActiveX Data Objects m.n Library

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]