A few days ago I was presented with just over seven thousand PDF files. I had waited six weeks for these files so I was in a horrid mood when they arrived in one big batch with just ID’s in the file name. I had no way of processing them by customer.

I had a spreadsheet that had the billing period, the customer name and the bil number in columns A, B and C respectively so I decided to write up a short script to process these files.

Here’s a cut down version with a little bit removed to protect the various companies involved.


Set FSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open ("D:\Home\Workarea\Invoices_PDF\invoices.xlsx")

' Starting at row 2 because row 1 is used for the column headings.
intRow = 2
strNewParentPath = "ByETB\"
strOldParentPath = "Combined\"
strBasePath = "D:\Home\Workarea\Invoices_PDF\"
Do Until objExcel.Cells(intRow,1).Value = ""
strPeriod = ConvertPeriod(objExcel.Cells(intRow, 1).Value)
strProcessedCompanyName = ConvertCompanyToDirectoryName(objExcel.Cells(intRow, 2).Value)
strFileName = "billno-" & objExcel.Cells(intRow, 3).Value & ".pdf"
' WScript.Echo "Moving " & strOldParentPath & strFileName & " To " & strNewParentPath & strProcessedCompanyName & "\" & strPeriod & "\"

If NOT (FSO.FolderExists(strBasePath & strNewParentPath & strProcessedCompanyName)) Then
WScript.echo "Folder " & strBasePath & strNewParentPath & strProcessedCompanyName & " didn't exist."
FSO.CreateFolder strBasePath & strNewParentPath & strProcessedCompanyName
End If

If NOT (FSO.FolderExists(strBasePath & strNewParentPath & strProcessedCompanyName & "\" & strPeriod)) Then
WScript.echo "Folder " & strBasePath & strNewParentPath & strProcessedCompanyName & "\" & strPeriod & " didn't exist."
FSO.CreateFolder strBasePath & strNewParentPath & strProcessedCompanyName & "\" & strPeriod
End If

WScript.Echo "Copying from: " & strBasePath & strOldParentPath & strFileName & " Copying to: " & strBasePath & strNewParentPath & strProcessedCompanyName & "\" & strPeriod& "\"
FSO.CopyFile strBasePath & strOldParentPath & strFileName, strBasePath & strNewParentPath & strProcessedCompanyName & "\" & strPeriod& "\"
intRow = intRow + 1
Loop
objExcel.Quit

Function ConvertCompanyToDirectoryName (CompanyName)
if CompanyName = "" Then
WScript.Echo "No ETB Name passed to ConvertCompanyToDirectoryName function"
Exit Function
End If

If InStr(1, CompanyName, "DUBLIN") <> 0 Then
CompanyName = "Dublin"
End If

If InStr(1, CompanyName, "LOUTH") <> 0 Then
CompanyName = "Louth"
End If

If InStr(1, CompanyName, "DONEGAL") <> 0 Then
CompanyName = "Donegal"
End If

If InStr(1, CompanyName, "LIMERICK") <> 0 Then
CompanyName = "Limerick"
End If

If InStr(1, CompanyName, "GALWAY") <> 0 Then
CompanyName = "Galway"
End If

If InStr(1, CompanyName, "CORK") <> 0 Then
CompanyName = "Cork"
End If

If InStr(1, CompanyName, "CARLOW") <> 0 Then
CompanyName = "Carlow"
End If

If InStr(1, CompanyName, "KERRY") <> 0 Then
CompanyName = "Kerry"
End If

If InStr(1, CompanyName, "MEATH") <> 0 Then
CompanyName = "WestMeath"
End If

If InStr(1, CompanyName, "KILDARE") <> 0 Then
CompanyName = "Kildare"
End If

If InStr(1, CompanyName, "TIPPERARY") <> 0 Then
CompanyName = "Tipperary"
End If

If InStr(1, CompanyName, "TIPP") <> 0 Then
CompanyName = "Tipperary"
End If

If InStr(1, CompanyName, "LAOIS") <> 0 Then
CompanyName = "Laois"
End If

If InStr(1, CompanyName, "MONAGHAN") <> 0 Then
CompanyName = "Monaghan"
End If

' Replace spaces with under lines.
If InStr(1, CompanyName, " ") <> 0 Then
CompanyName = Replace(CompanyName, " ", "_")
End If

' Replaces / character with nothing.
If InStr(1, CompanyName, "/") <> 0 Then
CompanyName = Replace(CompanyName, "/", "")
End If

ConvertCompanyToDirectoryName = CompanyName
End Function

Function ConvertPeriod (Period)
If Period = "" Then
WScript.Echo "Period passed to ConvertPeriod function is blank."
Exit Function
end If

If Period = "01/01/2016" Then
Period = "Jan2016"
End If

If Period = "01/02/2016" Then
Period = "Feb2016"
End If

If Period = "01/03/2016" Then
Period = "Mar2016"
End If

If Period = "01/04/2016" Then
Period = "Apr2016"
End If
ConvertPeriod = Period
End Function