INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

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.

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close