Jimbo's VBA Automation Toolbox

Background

Spread throughout the site are small snippets of VBA code that one can use to manipulate text files, MS Access™ files and MS Excel™ files. I update this code from time-to-time, but I try to keep a working copy of them all aggregated in a single file that can be downloaded from this site.

History

I started on these tools in 2004 when I was working for a Danish client and one DBA would produce a sloppy extract of all material classifications and characteristics. Previously the client had utilized MS Excel and hours of somebody's time to validate the text-based extracts. I took over and began to manually import each new revision of the text-based extracts and then run a macro with a handful of queries that would produce a list of mismatches and duplicate entries. At first it took hours for me to provide feedback to the DBA so that he could tweak his extract routine to match SAP's expected data format. I couldn't reliably import the text using a macro using a template because the format of the data files changed each time the DBA produced a new extract. Eventually I got the routine down to a half-hour, but I couldn't do anything else during that time because I had to manually import and export files from Access.

Then I wrote two VBA functions: ImportFromText() and ExportToText(). The amount of time to produce feedback dropped from half an hour to two minutes. In fact, I won a bet with the German DBA that I could produce the feedback in two minutes using these functions. After losing the bet, he referred to the validation process thereafter as "Die zwei Minuten" ("The two minutes").

Since then these small snippets of VBA have saved me thousands of hours of mind-numbing manual data transformation and improved my productivity. The accuracy and reliability of the data transformed using these tools cannot be matched by manual processes. These functions can be called from a macro as a simple best practice to make data transformation in MS Access simple and consistent.

Version Tracking

I didn't start properly tracking version numbers and improvements until 2010. The code was tweaked and modified in files all over the place in response to special needs, client requests and peer suggestions. I had planned to develop these tools and share them years ago, but prior to 2010 had never ratified a standard distribution. Well here is the revision history and the link to Jimbo's Access Toolkit.

• 1.01 November, 2010 -- The first revision to be released to the SAPLSMW community in 2010. It included VBA scripts that permit the direct export (and import) of text into (and out of) Access.
• 1.1 February 2011 -- Added functionality to export to simple, single-worksheet Excel spreadsheets.
• 1.11 March 2011 -- Added functionality to export to complex, multi-worksheet Excel spreadsheets. Added de-duplicating function.
• 1.2 June 2011 -- Added functionality to export to UTF-8 coded text files (the SAP standard for Unicode).
• 1.21 June 2016 -- Added functionality to import UTF-8 coded text files. Imports SE16 output (see below).
• 1.3July 2020 -- Added functionality to convert RPT files to both tab-delimited text files and Excel files.

How to get it

Download the most recent version of Jimbo's VBA Automation Toolbox free of charge from this page.

Known issues

The file is already configured to use libraries provided with most revisions of Windows and included with Microsoft's Office product and the absence of a library may cause Access to throw strange errors or refuse to understand the trim() function. Basic functions like trim() are included in the Excel Object Library so it is important that at least one version be selected.

In the VBA editor, click on Tools → References and search the list for missing references. Find the most recent version of the same library and link to that.

The VBA engine is already configured to utilize Microsoft's Data Access Objects (DAO), but the version on your computer might not be the same.

Click on Tools → References and then find your version of Microsoft DAO Object Library.

The ImportFromText() and ImportFromUnicode() functions automatically detect SAP's SE16 copy-pasted data. It is recommended to save the text file as UTF-8; modern multi-national systems will invariably have special characters that will be lost or scrambled if saved as ANSI.