Well, I finally done it. I’ve gone “paperless” in the AP department. Those quotes are because I haven’t gone totally paperless, I’m just done filing paid invoices. Instead I’m scanning them. I bought this happy little customer.

And it’s proving to be quite a good scanner. The worst part about scanning paid invoices is renaming the file. There are some sophisticated accounting packages out there than handle this better, but the one that I have access to has it’s problems, not the least of which is that it’s a subscription. The scanner automatically names the file with some string of number representing the date and time. I want the file name to be VendorName_CheckNumber_Date.pdf, but typing that for every scan would be a pain. So I automated it. All scans are manually renamed as CheckNumber.pdf for paper check and eAmount.pdf for electronic transfers. Then I run the below code.

Public Sub RenameScans()

Dim clsPaids As CPaids
Dim clsPaid As CPaid
Dim sFile As String

Const sPATH As String = "\\Server\Company\Accounting\AP\Paid\"
Const sFTYPE As String = "*.pdf"

'Get a list of checks/paid invoices from accounting database
Set clsPaids = New CPaids
clsPaids.Fill

'Look for pdf files in the scan location
sFile = Dir(sPATH & sFTYPE)

Do While Not Len(sFile) = 0
'Find the check information for this scan
Set clsPaid = Nothing
Set clsPaid = clsPaids.PaidByRefNumber(sFile)

If Not clsPaid Is Nothing Then
'Record the old location and flag it to be moved
clsPaid.ToMove = True
clsPaid.OldLocation = sPATH & sFile
End If

'Get the next PDF file in the scan location
sFile = Dir
Loop

'If I move the file in the middle of the Dir loop, the Dir gets all jacked up
'so I flag it above, and actually move it when I've been through all the file
For Each clsPaid In clsPaids
If clsPaid.ToMove Then
Name clsPaid.OldLocation As clsPaid.NewLocation(sPATH)
clsPaid.ToMove = False
End If
Next clsPaid

End Sub

Here are the basics: Get a list of paid invoices from the accounting database. Try to match them up with scanned document. If there’s a match, flag it to be moved. Move and rename all the flagged documents to their proper folder. First, I fill a bunch of CPaid class instances with data from the accounting software. In CPaids:

Public Sub Fill()

Dim adConn As ADODB.Connection
Dim adRs As ADODB.Recordset
Dim clsPaid As CPaid
Dim sSql As String

Const sCONN As String = "Some connection string"

sSql = "SELECT TxnDate, RefNumber, PayeeEntityRef_FullName, Amount FROM billpaymentcheck WHERE TxnDate>=#" & Format$(Date - 90, "m/d/yyyy") & "#;"

Set adConn = New ADODB.Connection
adConn.Open sCONN
Set adRs = adConn.Execute(sSql)

If Not adRs.EOF And Not adRs.BOF Then
adRs.MoveFirst
Do While Not adRs.EOF
Set clsPaid = New CPaid
With clsPaid
.TxnDate = adRs.Fields(0).Value
.RefNumber = adRs.Fields(1).Value
.Payee = adRs.Fields(2).Value
.Amount = adRs.Fields(3).Value
End With
Me.Add clsPaid
adRs.MoveNext
Loop
End If

adRs.Close
Set adRs = Nothing
adConn.Close
Set adConn = Nothing

End Sub

Nothing too fancy here. Get a recordset, loop through it, fill the class. I go back 90 days, which is overkill, but you never know. That hardcoded 90 should really be an argument. Once the classes are all filled, I loop through the PDF files in the designated folder. For every file I find, I try to match it up.

Public Property Get PaidByRefNumber(sRefNumber As String) As CPaid

Dim clsReturn As CPaid
Dim clsPaid As CPaid

For Each clsPaid In Me
If UCase(sRefNumber) Like UCase(clsPaid.ScanFile) Then
Set clsReturn = clsPaid
Exit For
End If
Next clsPaid

Set PaidByRefNumber = clsReturn

End Property

Whenever I try to convince someone to try using class modules, I can never seem to make a compelling argument. It’s one of things you have to force yourself to try to see if you prefer it. But for me, this is a good example of why I like classes. This loops through all the child classes and returns a match if it finds one. The good part is the ScanFile property. I like to break down my logic into an absurd number of properties. You can look at this property’s name and usage and understand what it does, even if you don’t know what the heck ScanFile is. You don’t really need to know what it’s matching to understand that it is matching something. I could put all the logic for the match right in this property, but what the hell fun would that be. If you are interested in what is being matched, you simply drill down to ScanFile.

Public Property Get ScanFile() As String

Dim sReturn As String

If Me.IsCheck Then
sReturn = Me.RefNumber & ".pdf"
Else
sReturn = Me.RefNumber & Me.Amount & "*.pdf"
End If

ScanFile = sReturn

End Propert

The logic to determine if this instance is a check or an electronic payment is really simple. You can see it in the next piece of code. And it might seem stupid to obfuscate that away from this procedure. Everything that’s involved in the IsCheck property could be put on the that one If line. Instead, I have a totally separate property and far more lines that necessary. But there are at least two advantages to doing it the way I did. The first is readability. A well named property like IsCheck tells me everything I need to know about why I have a conditional there in the first place: I do something different for checks. The second reason is maintainability. Right now, anything that starts with an “e” is an electronic payment and doesn’t have a proper check number. If that changes, say because I want to start prefixing them with “ach” instead of “e”, I can modify the code in one place. That should be, and is, the only place in the code where I make that determination so it’s the only place to make the modification.

If I was really a good programmer, I wouldn’t be naming properties names like IsCheck. I would name them IsType(ByVal eType as PaidType) and have a PaidType enum. That way if I want to differentiate between ACH and wire transfers (both currently “e” types) I could do it with less modification. There’s a balance though. If you go down that road too far, you end up writing code that writes code instead of doing actual productive work. Here’s the super-simple IsCheck property whose existence some people might consider superfluous.

Public Property Get IsCheck() As Boolean

IsCheck = Me.RefNumber <> "e"

End Property

At this point, the only thing I need to do is determine where the file goes.

Public Property Get NewLocation(ByVal sPATH As String) As String

Dim sReturn As String
Dim lCnt As Long

sReturn = Me.Folder(sPATH) & Me.Filename

Do Until Len(Dir(sReturn)) = 0
lCnt = lCnt + 1
sReturn = Replace(sReturn, ".pdf", "_" & lCnt & ".pdf")
Loop

NewLocation = sReturn

End Property

This property builds the path and filename, then starts appending integers to the end if there are any conflicts.

Public Property Get Folder(ByVal sPATH As String) As String

Dim sReturn As String

sReturn = Me.CleanPayee

If Len(Dir(sPATH & sReturn, vbDirectory)) = 0 Then
sReturn = UCase(Left$(sReturn, 1))
End If

If Right$(sPATH, 1) <> Application.PathSeparator Then sPATH = sPATH & Application.PathSeparator

Folder = sPATH & sReturn & Application.PathSeparator

End Property

I have more than 26 folders in which to store these scanned files: one for every letter of the alphabet and a few for vendors for whom I’ve determined it would be advantageous to have a separate folder. CleanPayee removes anything from the vendor name that’s not a letter or a number. If there’s a folder that matches that vendor’s cleaned name, it will store it there. If not, it stores it in the folder that matches the first letter of the vendor name.

Public Property Get Filename() As String

Const sUS As String = "_"

Filename = Me.CleanPayee & sUS & Me.RefNumber & sUS & Format(Me.TxnDate, "yyyymmdd") & ".pdf"

End Property

Lastly, I build the file name from properties of the class. And that’s it. On the fifty or so scanned files that I tested it on, it ran so fast that I assumed it didn’t work until I checked the folders. I really thought I was going to have performance problems with the Name statement for renaming and moving files, but it doesn’t seem to be the case. As Jon Peltier once said, don’t optimize until you know you have a problem and you know what’s causing the problem.

You can download RenameScans.zip