Access VBA code to generate a SEPA XML file

[SHOTOKANKATANA]

First time I post a question here so bare with me if I make a mistake and please do tell me if so. Let's start with a big thanks for this forum and all it's contributors, it's been really helpful to me. I'm building a database which has become quite complex over the last few months and I have been able to solve many issues I ran into along the way with posts from here, so thanks!


Okay so here it goes...

I need to be able to generate a SEPA XML file for banking use. It has to be an ISO 20022, PAIN 008, UTF8 file.
The information that needs to go into the SEPA XML file is loaded on a form and I'd like to generate the file with the click of a button. The button is already there and the other things the click event has to do are in place and working, I'm only missing the code to generate the actual file.
Let's say the form is named Form1, it has 2 subforms, Subform1 (single form) and Subform 2 (continuous form)
Subform 1 holds receivers info, and subform 2 holds the actual payment records.
How do I create the XML file with the info on Subform 1 and 2 implemented into it?
My VBA skills are not great, certainly not horrible either but this goes a bit over my head.
I found a post about this on another forum that has some code I think I can use but I'm not sure if it's complete and how I could implement my records into it.
Could I use this code, maybe alter it to my situation a bit, and implement some more code so the actual XML file holds the information from Subform 1 and 2?

Any help on this would be greatly appreciated, Thanks in advance!

Here is the code:

Dim xmlDom As MSXML2.DOMDocument60
Dim xmlspacename As MSXML2.IXMLDOMElement
Dim xmlVersion As MSXML2.IXMLDOMProcessingInstruction
Dim xslStylesheet As MSXML2.IXMLDOMProcessingInstruction
Set xmlDom = New MSXML2.DOMDocument60

Set xmlVersion = xmlDom.createProcessingInstruction("xml", "version='1.0' encoding= 'UTF-8'")
xmlDom.appendChild xmlVersion

Set xmlspacename = xmlDom.createElement("Document")
xmlDom.appendChild xmlspacename
xmlDom.documentElement.setAttribute "xmlns", "urn:iso:std:iso:20022:tech:xsd:pain.001.001.03"
xmlDom.documentElement.setAttribute "xmlns:xsd", "http://www.w3.org/2001/XMLSchema"
xmlDom.documentElement.setAttribute "xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance"

'****RAIZ DEL MENSAJE
'Creando elemento raiz [1..1]_0
Dim xmlrootnode As IXMLDOMElement
Set xmlrootnode = xmlDom.createElement("CstmrCdtTrfInitn")
xmlspacename.appendChild xmlrootnode
'*****CABECERA
' Creando 1_0_cabecera [1..1]_0
Dim cabecera As IXMLDOMElement
Set cabecera = xmlDom.createElement("GrpHdr")
xmlrootnode.appendChild cabecera

MsgBox "Ok"

' Saves XML data to disk.
xmlDom.Save ("c:\temp\andrew.xml")

 

[oleronesoftwares]

Hi,
Are you trying to generate /export into an existing XML file or into a new XML file?

 

[oleronesoftwares]

Below is an example of exporting into a xml file.

Sub ExportCustomerOrderData()
 Dim objOrderInfo As AdditionalData
 Dim objOrderDetailsInfo As AdditionalData
 
 Set objOrderInfo = Application.CreateAdditionalData
 
 ' Add the Orders and Order Details tables to the data to be exported.
 Set objOrderDetailsInfo = objOrderInfo.Add("Orders")
 objOrderDetailsInfo.Add "Order Details"
 
 ' Export the contents of the Customers table. The Orders and Order
 ' Details tables will be included in the XML file.
 Application.ExportXML ObjectType:=acExportTable, DataSource:="Customers", _
 DataTarget:="Customer Orders.xml", _
 AdditionalData:=objOrderInfo
End Sub

i got it from microsoft website link https://docs.microsoft.com/en-us/office/vba/api/access.application.exportxml

 

[SHOTOKANKATANA]

Hi, that's faster than I expected

So I have a form and I'd like to export the records from Subform 1 and 2 into a new XML file.

 

[SHOTOKANKATANA]

Okay, yeah I have seen that before. But how would I make it so that it includes the ISO 20022, PAIN 008, UTF8 norm so the bank would accept the file for processing?

 

[oleronesoftwares]

look at below link for clarrification, if still not clear let me know, so i might explain better

Exporting data to an externally defined XML Schema

social.msdn.microsoft.com

it explains dealing with banks acceptable formats

 

[Gasman]

@SHOTOKANKATANA
Do not post the link, but just the text. Surround it with code tags if need be.
At least you are trying to do the right thing. Well done.

 

[SHOTOKANKATANA]

Thanks, I'll check it out, I'm messing with the code also. See what it does. I'll post back here...

 

[oleronesoftwares]

Do not post the link, but just the text. Surround it with code tags if need be.
At least you are trying to do the right thing. Well done.

Noted,thank you for the correction.

 

[SHOTOKANKATANA]

@SHOTOKANKATANA
Do not post the link, but just the text. Surround it with code tags if need be.
At least you are trying to do the right thing. Well done.

Yeah that's actually what I'm trying but it doesn't work for some reason, same with the link. I just click insert a link and provide the url, then it gives me a big "OOPS something went wrong" If I paste only the text and surround it with code tags it's the same thing. (check the img)
I noticed the same thing when just replying to the first answer which contained code, same error.
It's weird cause it shows the preview perfectly fine.
I must be something wrong probably.

 

[oleronesoftwares]

Yeah that's actually what I'm trying but it doesn't work for some reason, same with the link. I just click insert a link and provide the url, then it gives me a big "OOPS something went wrong" If I paste only the text and surround it with code tags it's the same thing. (check the img)
I noticed the same thing when just replying to the first answer which contained code, same error.
It's weird cause it shows the preview perfectly fine.
I must be something wrong probably.

Please administrator help us look into possible cause of the error message.

 

[Gasman]

Please administrator help us look into possible cause of the error message.

Until OP has 10 posts, unlkley to be able to post links. Standard in most forums?
@SHOTOKANKATANA use [c o d e] and [/c o d e] around the link. Remove the spaces in those brackets that I had to put in, so the site did not think they were real code tags.

 

[SHOTOKANKATANA]

Until OP has 10 posts, unlkley to be able to post links. Standard in most forums?
@SHOTOKANKATANA use [c o d e] and [/c o d e] around the link. Remove the spaces in those brackets that I had to put in, so the site did not think they were real code tags.

Tried it, didn't work... Same error. Tried it both with and without the spaces that you had to leave in there.
I think maybe it's for the same reason you mentioned, 10 posts?
I'll edit this post as soon as it lets me though.

Thanks for the reply.

Edit: It works now, it must have been the 10 posts indeed

 

[SHOTOKANKATANA]

look at below link for clarrification, if still not clear let me know, so i might explain better

Exporting data to an externally defined XML Schema

social.msdn.microsoft.com

it explains dealing with banks acceptable formats

That's actually really helpful, can't believe I haven't seen that before after so many hours of looking and scrolling. It's a big load of rather complex code though. This will probably require some studying.... Curious to see if I can figure this out

 

[SHOTOKANKATANA]

look at below link for clarrification, if still not clear let me know, so i might explain better

Exporting data to an externally defined XML Schema

social.msdn.microsoft.com

it explains dealing with banks acceptable formats

After 8 hours of studying and reading code I decided I give up on this.
It's to complex to get it implemented and working in my DB.
I found a couple other posts scattered around the web but most of them are old and outdated and not solved or answered either.

It's kinda funky I think cause SEPA payments are a standard on the EU continent it should be way easier to implement the code to generate the XML files into any form of database. Unless you have serious coding skills this is near to impossible to achieve.

Anyways, I found an Excel file that actually does generate the XML file in the desired way so my best bet probably is to use that.
I know how to send access info to excel so I'll write the code to export the needed info into the excel file and generate the XML from there.

To bad I need to use a work around like this because I know for a fact access can do it too.

In any case thanks for the help up to this point!