Excel VBA: Create Text File Logging All Errors in Shared Network Environment 1

[kjv1611]

Does anyone have experience creating a log file to centrally capture all error messages that users may see in a shared/network environment?

We have a somewhat complex, somewhat convoluted (I'd say) "system" here that is basically a series of MS Excel workbooks that mostly use the same central code, though some still are 100% on their own. Each of them when opened, create a new copy of themselves (so a template without actually starting as a template file - something I hope to one day change - have them actually start out as template files).

All sorts of odd issues can pop up due to varied circumstances, and it's difficult to remember/notate/track down everything unless users come to me directly when an issue occurs.

Currently, there's no error logging of any sort. Some procedures have error handling, others have none at all. It doesn't have to be fancy, but I'd like to capture several pieces of information whenever an error does occur and save those to a folder of text files or text file that is shared amongst all worksheets.

One thing I am concerned about is file locking. Say 2 users at different locations get errors at the same time, and try to write to the error log at the same time. Is there any good way to prevent or work around this issue?

I imagine the best way to go about doing this in VBA is to use the "freefile" methods of creating/writing to text files, but I've only done anything writing to text files probably no more than 2 or 3 times other than exporting data.

Thanks in advance for any ideas, references, etc.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[DjangMan]

The log filename could contain the user's network login id which should help with simultaneous errors being logged.

Create a module for your logging code so you can easily copy/paste any updates to your logging code. Update your procedural error logging to include generic information like workbook name, date/time, user, sub-routine and module and then, as required, add in specific code to send the parameters sent to each sub.

I'd look at using MZTools since it provides an easy way to add in custom error trapping code and insert it using the context menu. You can also easily set line numbers that restart at the module level or the procedure level (or project level if you're crazy) and then you can include the line numbers in your log.

 

[kjv1611]

Thanks for the suggestions, DjangMan! Yeah, I used MZTools off and on when it was free. It seems it's been highly improved for the paid version, though when I did download a trial, I ended up not using it during that time - go figure. Anyway, I've been considering purchasing, and may still.

I like your idea for logging the code. I may have to make that a little different in how some work is done here. For some things, they have a designated login that the lab folks share at different times. So it's theoretically possible that the same login could attempt to access a file at the same time, though it's much less likely. The good part? this setup of Workbooks prompts every user to "login", so I could also use that login info from the workbook directly in the error logging file. Or I could even go so far as to combine the Windows user and the Excel user. I think I'd rather include both, since the "login" piece, for now, is way far from foolproof - there is no error checking or checking against a list or anything, but rather it's just a way to make sure the correct name goes on the lab analysis forms.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57