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:
- Identify objects and relationships
- Create class modules
- Write code to fill classes
- Write code to create XML file
- Augment class modules until it compiles
- 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.
1 2 3 4 5 6 7 8 9 10 11 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
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) Private mclsChecks As CChecks 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
Hi
how can a convert the excel template in nacha file ?