Converting excel to specific type of XMLformat?

Question

I have an excel file and I need to convert to XMLformat. However, it needs to be in one of these formats according to the french audit department:

My question is: Is it possible to convert excel format into one of these XML enconding formats? Sorry if it is a simple question, but I have no experience with excel formatting. Thank you very much!

Link in french: https://www.impots.gouv.fr/portail/les-comptabilites-informatisees

Link to the formats:

https://www.impots.gouv.fr/portail/files/media/1_metier/2_professionnel/a47a-i-viii-7.xsd

https://www.impots.gouv.fr/portail/files/media/1_metier/2_professionnel/a47a-i-vii-1.xsd

https://www.impots.gouv.fr/portail/files/media/1_metier/2_professionnel/a47a-i-viii-5.xsd

https://www.impots.gouv.fr/portail/files/media/1_metier/2_professionnel/a47a-i-viii-3.xsd

Answer 1

I wrote an abstract code, but you can put your hand in, to suite your needs

Private Sub CommandButton3_Click()


NRows = Sheet1.UsedRange.Rows.Count
NCols = Sheet1.UsedRange.Columns.Count
MsgBox NRows & "-" & NCols
XMLStr = "<Root>" & vbCr
For i = 2 To NRows
    XMLStr = XMLStr & "<Row>" & vbCr
    For j = 1 To NCols
         XMLStr = XMLStr & "<Col>" & Cells(i, j) & "</Col>" & vbCr
    Next
    XMLStr = XMLStr & "</Row>" & vbCr
Next
XMLStr = XMLStr & "</Root>" & vbCr

'writting string to xml file

Dim myFile As String
myFile = Application.DefaultFilePath & "\myXML.xml"
Open myFile For Output As #1
Write #1, XMLStr
Close #1
End Sub

Answer 2

Sounds as if you are doing point-to-point integration using hand-written C#. That's not always wrong, but if you are doing a lot of this type of coding then you may be better off with a good integration engine.

In this case, classic mistakes could include:

• Coupling the integration logic with the exact worksheet names and column names in the current version of the Excel spreadsheet
• Coupling the integration logic with the exact order of occurrence of the worksheets/columns
• Sending a badly-formed XML document (does not conform to the XML spec). Adam Norman's code is likely to do that, because it does not escape the characters that are significant in an XML document.
• Sending an invalid XML document (does not match the XSD).

All of the above can be avoided by careful coding, but a good integration engine would help you to do it right.