creating xml in VBA

[tomas]

Hi

I need to create xml file for accounting software to create invoices. I created just very simple example and hope someone can point me to right direction and I can expand from there..

Excel file contains two columns with invoice number and date which are variable inputs into xml. there is a picture how xml should look like.

if anything is unclear pls let me know thx

 

[Chihiro]

Something like below? Change oPath as needed.

Const head = "<?xml version ""1.0"" encoding=""Windows-1250""?>"
Const head2 = "    <inv:invoiceHeader xmlns:rsp=""http://www.stormware.cz/schma/version_2/response.xsd"">"
Const symVar = "        <inv:symVar>%%</inv:symVar>"
Const idate = "        <inv:date>%%</inv:date>"
Const foot2 = "    </inv:invoiceHeader>"
Sub Demo()
Dim a
Dim xml As String, i As Long
Dim oPath As String: oPath = "C:\Test\test.txt"
Dim intFF As Integer: intFF = FreeFile()
a = Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
xml = head & vbNewLine
For i = 1 To UBound(a)
    xml = xml & head2 & vbNewLine & vbNewLine & Replace(symVar, "%%", a(i, 1)) & _
            vbNewLine & Replace(idate, "%%", a(i, 2)) & vbNewLine & foot2 & vbNewLine & vbNewLine
Next
Open oPath For Output As #intFF
Print #intFF, xml
Close #intFF
End Sub

 

[tomas]

Yes thank you !

 

[Deepak]

If you will map correct schema with xl then no need vba. Just SaveAs xl file to xml.

 

[Chihiro]

I should learn xml mapping process a bit more I always forget that it exists.

Though I've used it in SharePoint programming, it's been ages.