John Nurick’s Code Page

This site contains various “library” functions or scripts I have found useful over the years. Most of them have previously appeared in newsgroup postings.

You are welcome to use this code in your applications or to help deal with your data, but in doing so you take responsibility for its behaviour. In short: use it at your own risk.

VB/VBA functions

These functions were written for VBA but can mostly be used with little or no modification in classic Visual Basic.

• ConvertTabSepToCSV() Convert a tab-separated (tab-delimited) text file to CSV (comma-separated).
• FileContents() Retrieve contents of a file as a string in a VBA variable.
• WriteToFile() Write the contents of a variable to a text file.
• TransposeTextFile() Transpose rows and columns of a delimited text file.
• TrimFileHeader() Trim header lines from text file.
• TrimFileHeaderAndFooter() Trim header and/or footer lines.
• Miscellaneous string functions.

VBA functions using regular expressions

These versatile VBA functions use the VBScript regular expression object to do powerful pattern matching in VBA and classic Visual Basic.

• rgxExtract() Extract substrings from a string, or parse a complex field into individual chunks of data.
• rgxReplace() Souped-up Replace() function.
• rgxValidate() Data validation with regular expressions.
• ParseAddress36: sample function using pattern matching to parse a street address
• More about regular expressions

VBScripts

• TallToWIde.vbs Converts a text file with one field per line and a fixed number of lines per record into an ordinary tab-delimited file.
• DAO_Execute.vbs Script to execute queries or SQL statements against an MDB database.
• TEMPLATE.vbs Template script showing how to process a text file line by line and write the result to a new file.

Perl and Perl scripts

Perl is a programming and scripting language with immensely powerful built-in text-handling functions. Some people call it the Swiss Army chainsaw of the programming world. Others complain, not unfairly, that Perl code looks like line noise interpreted by a modem. I use it for mostly munging text files (which it does far more elegantly than any dialect of Visual Basic), but it can do OLE Automation and therefore automate Access/Jet and the other Office applications.

VBA functions inspired by Perl

These are translations into Classic VB/VBA of a handful of useful functions found in Perl and some other high level scripting languages.

Importing data from Microsoft Works databases

Access doesn't come with any way of importing data from a Microsoft Works database (.wdb file).

The best approach is to use Works to export the data into a format that Access can handle (e.g. CSV). If you don't have access to Works, Excel can open some old Works files. Or you can try this sample database, which contains VBA code that takes advantage of Word's ability to use a Works database as a mailmerge data source. You're welcome to use the code it contains, but any debugging it needs you'll have to do for yourself. It requires Office 2000 or later and probably won't work with all versions of Works or all Works databases - but it's better than nothing.