CODEWITHSUNDEEP
excelautomationmonitoringanalysisvba
vlad.lisnyi
vlad.lisnyi

Reputation: 345

VBA Automation/Monitoring/Development

I am working as coordinator of EUCs at my department (more than 50 people and more than 150 MS Excel macros for controls and reporting). In order to keep good track of Macros usage and measuring tool performance, I have added a simple code to each workbook that collects usage data (who and when had opened/closed workbook, how much it takes for a macro to perform certain tasks, etc...). The code is pretty simple, it collects all data in a public variable and before the close of the workbook, it saves the content of a variable to a .txt file in a folder.

Option Explicit

Dim info_data as String

Sub check_point(x as integer)
Select x
 Case 0
 Call WBK_close
 Case 1
 Call WBK_open
 Case 2
 Call WBK_main_start
 Case 3
 Call WBK_main_end
End Select
End Sub

Private Sub WBK_close()
 info_data = info_data & "WBK Close;" & Now()
 Open myFile For Output As #1
 Print #1, info_data
 Close #1
End Sub

Private Sub WBK_open()
 info_data = "WBK Open;" & Now() & vbNewLine
End Sub

Private Sub WBK_main_start()
 info_data = "WBK Main Start;" & Now() & vbNewLine
End Sub

Private Sub WBK_main_end()
 info_data = "WBK Main End;" & Now() & vbNewLine
End Sub

I am using a check_point sub at a certain point in the code to make a record.

Sometimes there happen some errors, especially when other macros also writing something to text files. Or when purely written code exit Sub with use of End statement, erasing all data saved to info_data. Therefore, I am wondering if there is a smarter way to collect MS Excel usage data (other codes, add-in, use of another programming language, alternative ways of data storing (not txt)).

Upvotes: 0

Views: 1387

Answers (2)

davor.geci
davor.geci

Reputation: 91

You can use Azure Application Insights to track usage, performance, error tracking for your VBA projects (Excel & Access) in real-time, online. It uses a little tool called VBA Telemetry.

If you don't have a Microsoft Azure account you can create and use a free one.
A few days ago I create a Udemy course on how to do all that.
It is a quick, short course (30 minutes) and in first 24h there were 2400+ people enrolled. You can watch a preview of this course to see if this is for you.

The link to this course:
VBA Application & Usage Monitoring Online with Azure cloud-Udemy course

Hope this helps,
Davor

Upvotes: 2

Brad
Brad

Reputation: 12255

Is the problem that they have multiple workbooks open at once each with a macro running? And Each of those macros is trying to write to the text file? If that's the case then write to a database (SQL Server of a network located Access DB)? The DB gets rid of the multiple concurrent users issue and also means all your data is already in one place (as opposed to you periodically collecting text file from their computers).

If you write to a DB they of course need to be on your domain. Maybe that is not always possible. But maybe those circumstances in which they are not on the domain it is acceptable to lose the tracking. Alternatively, you could write to a dedicated text file in those circumstances then once domain connectivity is restored, write whatever info you need from there to your DB.

Upvotes: 2

Related Questions