There is a product that some banks use, like my bank, called Premier ACH. It’s hosted on the bank’s website. It allows you to enter the data for an ACH transactions (Automated Clearing House, think direct deposit) and creates a properly formatted NACHA file for you. You can submit a NACHA file to your bank and they will initiate an ACH transaction for you. It’s a two step process: create a wrk file and turn it into a NACHA file. The wrk file is an XML file.

The goal of this series of posts is to turn Excel data into a properly formatted wrk file. The Excel data is fake for this example, but it’s meant to approximate the format you might get when you export certain reports out of Quickbooks. It isn’t exact because creating relational fake data is a real pain. But it’s close and it will teach you the necessary skills to manipulate the data in…wait for it…custom class modules. Who didn’t see that coming?

A sample of the Excel data:

And a sample of the XML (wrk) file:

Here are the basics steps I took:

1. Identify objects and relationships
2. Create class modules
3. Write code to fill classes
4. Write code to create XML file
5. Augment class modules until it compiles
6. Bonus: Create a payroll review sheet

Lately, I write most of my code in this fashion. Once the basic class modules are created, I write the main procedure. The main procedure usually includes one or two methods of my custom objects and provides the framework for what I want to accomplish. There are a couple of advantages, and one big disadvantage, to coding this way. I’m not advocating it as great method, I’m just saying it’s how I’ve been working lately. So far, I like it.

The first major advantage is the aforementioned framework it provides. By coding from finish to start, I know exactly what my result should look like before I get into the details. The other advantage is I never code anything I don’t need. Like test driven development, this method forces me to only write properties and methods in my classes that I need. A short disclaimer: This example was abstracted from a larger, more complex application, so you may see an occasional unused property.

That big disadvantage? The code doesn’t compile until I’ve written a boat load. Some, like me, have advocated that you should never be more than a few lines from code that compiles. This method could not be more contrary to that advice.

Let’s get on with it. I have Employees, PayrollItems, and Checks. I think everyone knows what employees and checks are, so I won’t belabor those. A PayrollItem is an entity that controls how a check applies to the general ledger. Examples of PayrollItems are Salary, Federal Withholding, and State Unemployment. I need one more object to link Checks and PayrollItems. For example, Salary is a payroll item, but the amount assigned to Salary will certainly vary by employee and may even vary by check. To accommodate that, I create a CheckItem object. The CheckItem will hold the variable data of the PayrollItem for a particular check. As for relationships

• Employee has many Checks
• Check has many CheckItems
• CheckItem has one PayrollItem

Let’s start by building CEmployee. First, we’ll look at the data

I have five employees with a name and social security number. Each employee has one or two direct deposit accounts. I insert a class into my project, name it CEmployee, and enter the columns as public variables.

Then I run my Public to Private and Create Parent macros and get this

I do the same thing for my PayrollItems table. It looks like this

Next time, I’ll set up the CheckItem and Check classes and fill them all. If you can’t wait that long…

You can download NACHA.zip