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 *