Log file from Excel COM API program

It’s a good habit to track the data your process has changed. Most programs should have some type of logging mechanism. Sometimes in the haste to roll a tool out the door, simple tracking pieces are left off. I ran across that recently when working with some of our Macro-enabled spreadsheets. Using the COM APIs provided with the software we use for our billing system, we’ve built some tools to process data in a different fashion than the standard jobs from the system.

I noticed that there wasn’t any output from these tools though. That bothered me when updating numerous records that may have needed to been rolled back. Doing some digging online, I made some mods to the VBA coding to allow for a ‘log’ file and track what the code did.

For this entry, here is the setup: An excel spreadsheet with a button that will run some VBA script. There is one tab that has the data points to process and the button to fire off the code. The other tab is for configuration: log output directory, username, etc. This is a rudimentary way to set up where the file goes and how to name it.

xl_output_tabs

There will be two important variables:

  • “Operator” (who is running the process)
  • “FilePath” (where the log file will be written)

xl_output_tabs1

The key with these fields is to name [B1 and B2 in the pic] appropriately. These will be used in the code. In the example above, click on B1, then click on the namebox – Type the name of the cell (like FilePath) and hit Enter. This aliases the cell where you will put the output path of the log.

SET UP

In the code editor (Developer -> Visual Basic), add the following to the code in order to create and write to the log:

xl_output_vbacode1

Using some variables, capture the configuration data before going on to the rest of the code. The vFileName combines the configuration operator value with a date stamp and file name to distinguish the function of the process. It uses the cell aliases from the config tab.

Creating the log file

xl_output_vbacode2

This creates the file using the filepath variable and filename using some built-in VBA code.

Writing to the log

xl_output_vbacode3

In this case, I wrote the data to a variable first then used the VBA API to write to the log using the variable. Use this at any point in the code to log an action on the data. In the example above, I’m logging that I updated one value to another for a particular account.

Output Result:

Based on the config, I can check my output.

xl_output1

Opening on the file I know I just created, I can review the details of the process.

xl_output2

Conclusion

That’s about it! A pretty simple solution to help keep track on changes made to data via the VBA processing.

Thanks for reading! TG

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s