NACHA Files Part 1

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.

Option Explicit

Public EmployeeID As Long
Public EmployeeName As String
Public SSN As String
Public Account1 As String
Public Routing1 As String
Public Type1 As String
Public Amount1 As String
Public Account2 As String
Public Routing2 As String
Public Type2 As String

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

Option Explicit

Private mlEmployeeID As Long
Private msEmployeeName As String
Private msSSN As String
Private msAccount1 As String
Private msRouting1 As String
Private msType1 As String
Private msAmount1 As String
Private msAccount2 As String
Private msRouting2 As String
Private msType2 As String
Private mlParentPtr As Long
Private Declare Sub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” _
    (dest As Any, Source As Any, ByVal bytes As Long)

Public Property Get Type2() As String: Type2 = msType2: End Property
Public Property Let Type2(ByVal sType2 As String): msType2 = sType2: End Property
Public Property Get Routing2() As String: Routing2 = msRouting2: End Property
Public Property Let Routing2(ByVal sRouting2 As String): msRouting2 = sRouting2: End Property
Public Property Get Account2() As String: Account2 = msAccount2: End Property
Public Property Let Account2(ByVal sAccount2 As String): msAccount2 = sAccount2: End Property
Public Property Get Amount1() As String: Amount1 = msAmount1: End Property
Public Property Let Amount1(ByVal sAmount1 As String): msAmount1 = sAmount1: End Property
Public Property Get Type1() As String: Type1 = msType1: End Property
Public Property Let Type1(ByVal sType1 As String): msType1 = sType1: End Property
Public Property Get Routing1() As String: Routing1 = msRouting1: End Property
Public Property Let Routing1(ByVal sRouting1 As String): msRouting1 = sRouting1: End Property
Public Property Get Account1() As String: Account1 = msAccount1: End Property
Public Property Let Account1(ByVal sAccount1 As String): msAccount1 = sAccount1: End Property
Public Property Get SSN() As String: SSN = msSSN: End Property
Public Property Let SSN(ByVal sSSN As String): msSSN = sSSN: End Property
Public Property Get EmployeeName() As String: EmployeeName = msEmployeeName: End Property
Public Property Let EmployeeName(ByVal sEmployeeName As String): msEmployeeName = sEmployeeName: End Property
Public Property Get EmployeeID() As Long: EmployeeID = mlEmployeeID: End Property
Public Property Let EmployeeID(ByVal lEmployeeID As Long): mlEmployeeID = lEmployeeID: End Property

Public Property Get Parent() As CEmployees: Set Parent = ObjFromPtr(mlParentPtr): End Property
Public Property Set Parent(obj As CEmployees): mlParentPtr = ObjPtr(obj): End Property

Private Function ObjFromPtr(ByVal pObj As Long) As Object
    Dim obj As Object
    CopyMemory obj, pObj, 4
    Set ObjFromPtr = obj
    ‘ manually destroy the temporary object variable
   ‘ (if you omit this step you’ll get a GPF!)
   CopyMemory obj, 0&, 4
End Function

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

Posted in Uncategorized

One thought on “NACHA Files Part 1

Leave a Reply

Your email address will not be published. Required fields are marked *