Creating SEPA Files

In this Excel Model you can create an SEPA XML file using the details you enter on the SEPA Debit tab. I used Chilkat XML when working with XML, I have also used MS XML but find Chilkat better. Chilkat XML is free and you can download the program from their website.

Sepa Setup
SEPA Setup

To start using the model you will need to complete the setup details tab. The cells with a pink background are formula driven and while the model isn’t protected these should not be overwritten.

Adding Customers
SEPA Customers

On the customer tab enter the customers details , the IBAN Check will validate that the IBAN is technically valid. The customer code is used as your unique identifier for identifier with your bank, a good practice is to use the code from your accounting system. The date of signature defines when the customer signed your direct debit mandate. This will be passed to the bank in the XML file, this cannot be a date in the future.

Sepa XML File Generation
Entering the direct debit details

The customer code cell is a drop-down linked to the customer tab. select a customer from the drop-down and the other details will populate. Only customers with a value will be generated on the excel file.

Before a file will be generated the system will ensure that all the mandatory fields are completed. This is shown on the screen as an error count.

In the next version of this file I will add an archive function to copy the data on the SEPA Debit tab to the archive tab. I am working on a video to demonstrate this model. I also have an SEPA Access Database which I will post once the code is updated to the current Chilkat Release.

You can download the current model from this link and make any changes to suit your own environment.

If you have any issues drop me an email on support@anythingaccess.com.

Creating SEPA File – vba Function
Function MakeXML()
On Error GoTo Errorhandler
Dim tmpRunNo, tmpBank
tmpRunNo = Range("Payment_Number")
tmpBank = Range("BankName")
Dim success As Long
Dim xml As New ChilkatXml
Dim DDInst As ChilkatXml
Dim GroupHeader As ChilkatXml
Dim PaymentInformation As ChilkatXml
Dim DDTransactionInformation As ChilkatXml
'Group Header Fields
Dim MessageID As ChilkatXml
Dim MessageDateTime As ChilkatXml
Dim TotalNumberofPayments As ChilkatXml
Dim TotalValue As ChilkatXml
Dim InitiatingParty As ChilkatXml
Dim InitiatingPartyID As ChilkatXml
Dim InitiatingPartyOrgID As ChilkatXml
Dim InitiatingPartyOthr As ChilkatXml
Dim InitiatingPartyOthrId As ChilkatXml
Dim tmpLoop, tmpBIC, tmpIban, tmpBankName, tmpSignDate
'Payment Fields
Dim PaymentID As ChilkatXml
Dim PaymentMethod As ChilkatXml
Dim BatchBook As ChilkatXml
Dim NoofTrans  As ChilkatXml
Dim TransValue As ChilkatXml
Dim PaymentTypeInformation As ChilkatXml
Dim ServiceLevel As ChilkatXml
Dim ServiceLevelCode As ChilkatXml
Dim LocalInstrument As ChilkatXml
Dim LocalInstrumentCode As ChilkatXml
Dim SequenceTP As ChilkatXml
Dim RequestedCollectionDate As ChilkatXml
Dim Creditor As ChilkatXml
Dim CreditorName As ChilkatXml
Dim CreditorAccount As ChilkatXml
Dim CreditorID As ChilkatXml
Dim CreditorIBAN As ChilkatXml
Dim CreditorAgentBIC As ChilkatXml
Dim FinInstnId As ChilkatXml
Dim CreditorBIC As ChilkatXml
Dim PmtId As ChilkatXml
Dim PaymentEndToEndId As ChilkatXml
Dim InstructionAmount As ChilkatXml
Dim DDMandatetx As ChilkatXml
Dim MndtRltdInf As ChilkatXml
Dim MandateId As ChilkatXml
Dim MandateSignDate As ChilkatXml
Dim CdtrSchmeId As ChilkatXml
Dim CdtrSchmeIdID As ChilkatXml
Dim PrvtId As ChilkatXml
Dim Othr As ChilkatXml
Dim CreditorSEPAID As ChilkatXml
Dim CreditorSchmeNm As ChilkatXml
Dim CreditorPrtry As ChilkatXml
Dim CustomerAgent As ChilkatXml
Dim CustomerFinInstnId As ChilkatXml
Dim CustomerBIC As ChilkatXml
Dim Customer As ChilkatXml
Dim CustomerName As ChilkatXml
Dim CustomerAccount As ChilkatXml
Dim CustomerAccId As ChilkatXml
Dim CustomerIBAN As ChilkatXml
Dim PL_CdtrSchmeId As ChilkatXml
Dim PL_CdtrSchmeIdID As ChilkatXml
Dim PL_PrvtId As ChilkatXml
Dim PL_Othr As ChilkatXml
Dim PL_CreditorSEPAID As ChilkatXml
Dim PL_CreditorSchmeNm As ChilkatXml
Dim PL_CreditorPrtry As ChilkatXml
Dim tmpMandateID As String
xml.Tag = "Document"
xml.addAttribute "xmlns", "urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"
Set DDInst = xml.NewChild("CstmrDrctDbtInitn", "")
'Payment Group Information
Set GroupHeader = DDInst.NewChild("GrpHdr", "")
Set MessageID = GroupHeader.NewChild("MsgId", Format(Now(), "YYYYMMDDhhss") & "-" & tmpRunNo)
Set MessageDateTime = GroupHeader.NewChild("CreDtTm", Format(Date, "YYYY-MM-DD") & "T" & Format(Now(), "HH:mm:SS"))
Set TotalNumberofPayments = GroupHeader.NewChild("NbOfTxs", Range("Number_of_Payments"))
Set TotalValue = GroupHeader.NewChild(" CtrlSum", Format(Range("Total_Payment_Value"), "#.00"))
Set InitiatingParty = GroupHeader.NewChild("InitgPty", "") ' Sepa User ID
Set InitiatingPartyID = InitiatingParty.NewChild("Id", "")
'this may be redundant - when originally written AIB had a different format to the others
If tmpBank = "AIB" Then
Set InitiatingPartyOrgID = InitiatingPartyID.NewChild("OrgId", "")
Set InitiatingPartyOthr = InitiatingPartyOrgID.NewChild("Othr", "")
Set InitiatingPartyOthrId = InitiatingPartyOthr.NewChild("Id", Range("Sepa_User_ID"))
ElseIf tmpBank = "BOI" Then
Set InitiatingPartyOrgID = InitiatingPartyID.NewChild("PrvtId", "")
Set InitiatingPartyOthr = InitiatingPartyOrgID.NewChild("Othr", "")
Set InitiatingPartyOthrId = InitiatingPartyOthr.NewChild("Id", Range("Sepa_User_ID"))
Else
Set InitiatingPartyOrgID = InitiatingPartyID.NewChild("OrgId", "")
Set InitiatingPartyOthr = InitiatingPartyOrgID.NewChild("Othr", "")
Set InitiatingPartyOthrId = InitiatingPartyOthr.NewChild("Id", Range("Sepa_User_ID"))
End If
Set PaymentInformation = DDInst.NewChild("PmtInf", "")
Set PaymentID = PaymentInformation.NewChild("PmtInfId", Right("00000" & tmpRunNo, 5))
Set PaymentMethod = PaymentInformation.NewChild("PmtMtd", "DD")
Set BatchBook = PaymentInformation.NewChild("BtchBookg", "true")
Set NoofTrans = PaymentInformation.NewChild("NbOfTxs", Range("Number_of_Payments"))
Set TransValue = PaymentInformation.NewChild("CtrlSum", Format(Range("Total_Payment_Value"), "#.00"))
Set PaymentTypeInformation = PaymentInformation.NewChild("PmtTpInf", "")
Set ServiceLevel = PaymentTypeInformation.NewChild("SvcLvl", "")
Set ServiceLevelCode = ServiceLevel.NewChild("Cd", "SEPA")
Set LocalInstrument = PaymentTypeInformation.NewChild("LclInstrm", "")
Set LocalInstrumentCode = LocalInstrument.NewChild("Cd", "CORE")
Set SequenceTP = PaymentTypeInformation.NewChild("SeqTp", "RCUR") 'FRST – for First presentation RCUR – for Recurrent presentation OOFF – for Once off presentation FNAL – for Final presenta
Set RequestedCollectionDate = PaymentInformation.NewChild("ReqdColltnDt", Format(Range("Collection_Date"), "YYYY-MM-DD"))
Set Creditor = PaymentInformation.NewChild("Cdtr", "")
Set CreditorName = Creditor.NewChild("Nm", Range("Business_Name"))
Set CreditorAccount = PaymentInformation.NewChild("CdtrAcct", "")
Set CreditorID = CreditorAccount.NewChild("Id", "")
Set CreditorIBAN = CreditorID.NewChild("IBAN", Range("IBAN"))
Set CreditorAgentBIC = PaymentInformation.NewChild("CdtrAgt", "")
Set FinInstnId = CreditorAgentBIC.NewChild("FinInstnId", "")
'Set CreditorBIC = FinInstnId.NewChild("BIC", Range("My_SEPA_BIC"))
'loop on payment
tmpLoop = 1
Sheets("SEPA Debit").Select
Range("Start").Select
Do While ActiveCell.Offset(tmpLoop, 0).Value <> ""
If ActiveCell.Offset(tmpLoop, 3).Value <> 0 Then ' if we have a payment value then proceed
Set DDTransactionInformation = PaymentInformation.NewChild("DrctDbtTxInf", "")
Set PmtId = DDTransactionInformation.NewChild("PmtId", "")
Set PaymentEndToEndId = PmtId.NewChild("EndToEndId", Format(Now(), "YYYYMMDDhhss") & "T" & tmpLoop) ' must be unique
Set InstructionAmount = DDTransactionInformation.NewChild("InstdAmt", Format(Round(ActiveCell.Offset(tmpLoop, 3).Value), "#.00"))
InstructionAmount.addAttribute "Ccy", "EUR"
Set DDMandatetx = DDTransactionInformation.NewChild("DrctDbtTx", "")
Set MndtRltdInf = DDMandatetx.NewChild("MndtRltdInf", "")
Set MandateId = MndtRltdInf.NewChild("MndtId", ActiveCell.Offset(tmpLoop, 0).Value)
tmpSignDate = Format(ActiveCell.Offset(tmpLoop, 5), "YYYY-MM-DD")
Set MandateSignDate = MndtRltdInf.NewChild("DtOfSgntr", tmpSignDate)     
Set PL_CdtrSchmeId = DDMandatetx.NewChild("CdtrSchmeId", "")
Set PL_CdtrSchmeIdID = PL_CdtrSchmeId.NewChild("Id", "")
Set PL_PrvtId = PL_CdtrSchmeIdID.NewChild("PrvtId", "")
Set PL_Othr = PL_PrvtId.NewChild("Othr", "")
Set PL_CreditorSEPAID = PL_Othr.NewChild("Id", Range("Sepa_USer_ID"))
Set PL_CreditorSchmeNm = PL_Othr.NewChild("SchmeNm", "")
Set PL_CreditorPrtry = PL_CreditorSchmeNm.NewChild("Prtry", "SEPA")    
Set CustomerAgent = DDTransactionInformation.NewChild("DbtrAgt", "")
Set CustomerFinInstnId = CustomerAgent.NewChild("FinInstnId", "")
Set Customer = DDTransactionInformation.NewChild("Dbtr", "")
Set CustomerName = Customer.NewChild("Nm", Replace(ActiveCell.Offset(tmpLoop, 1).Value, "&", "+"))
Set CustomerAccount = DDTransactionInformation.NewChild("DbtrAcct", "")
Set CustomerAccId = CustomerAccount.NewChild("Id", "")
tmpIban = ActiveCell.Offset(tmpLoop, 2).Value
Set CustomerIBAN = CustomerAccId.NewChild("IBAN", tmpIban)
'end loop on payment
End If
tmpLoop = tmpLoop + 1
Loop
success = xml.SaveXml(Range("ExportFileName") & "\RunNo_" & tmpRunNo & ".xml")
If (success <> 1) Then
MsgBox xml.LastErrorText
Else
MsgBox "File Created " & Range("ExportFileName") & "\RunNo_" & tmpRunNo & ".xml"
End If
Exit Function
Errorhandler:
MsgBox "An Error Occurred creating the File " & Err.Number & " " & Err.Description
End Function

Sources

I used the vba code for IBAN Validation from Aswin van Woudenberg

Sample IBAN Codes sourced here for testing

AIB Sepa Documentation

Chilkat XML for the XML file creation

IBAN Validation Code 

' http://en.wikipedia.org/wiki/International_Bank_Account_Number
Private Const IbanCountryLengths As String = "AL28AD24AT20AZ28BH22BE16BA20BR29BG22CR21HR21CY28CZ24DK18DO28EE20FO18" & _
"FI18FR27GE22DE22GI23GR27GL18GT28HU28IS26IE22IL23IT27KZ20KW30LV21LB28" & _
"LI21LT20LU20MK19MT31MR27MU30MC27MD24ME22NL18NO15PK24PS29PL28PT25RO24" & _
"SM27SA24RS22SK24SI19ES24SE24CH21TN24TR26AE23GB22VG24QA29"
Private Function ValidIbanCountryLength(CountryCode As String, IbanLength As Integer) As Boolean
Dim i As Integer
For i = 0 To Len(IbanCountryLengths) / 4 - 1
If Mid(IbanCountryLengths, i * 4 + 1, 2) = CountryCode And _
CInt(Mid(IbanCountryLengths, i * 4 + 3, 2)) = IbanLength Then
ValidIbanCountryLength = True
Exit Function
End If
Next i
ValidIbanCountryLength = False
End Function
Private Function Mod97(Num As String) As Integer
Dim lngTemp As Long
Dim strTemp As String
Do While Val(Num) >= 97
If Len(Num) > 5 Then
strTemp = Left(Num, 5)
Num = Right(Num, Len(Num) - 5)
Else
strTemp = Num
Num = ""
End If
lngTemp = CLng(strTemp)
lngTemp = lngTemp Mod 97
strTemp = CStr(lngTemp)
Num = strTemp & Num
Loop
Mod97 = CInt(Num)
End Function
Public Function ValidIban(IBAN As String) As Boolean
Dim strIban As String
Dim i As Integer
strIban = UCase(IBAN)
' Remove spaces
strIban = Replace(strIban, " ", "")
' Check if IBAN contains only uppercase characters and numbers
For i = 1 To Len(strIban)
If Not ((Asc(Mid(strIban, i, 1)) <= Asc("9") And Asc(Mid(strIban, i, 1)) >= Asc("0")) Or _
(Asc(Mid(strIban, i, 1)) <= Asc("Z") And Asc(Mid(strIban, i, 1)) >= Asc("A"))) Then
ValidIban = False
Exit Function
End If
Next i
' Check if length of IBAN equals expected length for country
If Not ValidIbanCountryLength(Left(strIban, 2), Len(strIban)) Then
ValidIban = False
Exit Function
End If
' Rearrange
strIban = Right(strIban, Len(strIban) - 4) & Left(strIban, 4)
' Replace characters
For i = 0 To 25
strIban = Replace(strIban, Chr(i + Asc("A")), i + 10)
Next i
' Check remainder
ValidIban = Mod97(strIban) = 1
End Function

Leave a Reply

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