Export from Excel to XML VBA Code

aejaz7

New Member
Joined
Nov 6, 2018
Messages
17
Want to generate xml from excel data.VBA code(need improvement) attached herewith.Want to loop data from second row to last row.
Thank you.

DateDebitCreditNo.TypeLedgerBank
01/04/20205000001PaymentTest AccountBank
02/04/20201100002ReceiptTest Account1Bank
03/04/2020150003PaymentTest Account5Bank
04/04/2020100004PaymentTest AccountBank
05/04/2020150005ReceiptTest Account9Bank
06/04/20201000006PaymentTest AccountBank
07/04/20205000007ReceiptTest AccountBank
08/04/20205000008ReceiptTest Account10Bank

VBA Code:
Sub export()
XMLFileName = ThisWorkbook.Path & "\" & "Test" & ".xml"
Open XMLFileName For Output As #1
Print #1, "<ENVELOPE>"
Print #1, "<HEADER>"
Print #1, "<TALLYREQUEST>Import Data</TALLYREQUEST>"
Print #1, "</HEADER>"
Print #1, "<BODY>"
Print #1, "<IMPORTDATA>"
Print #1, "<REQUESTDESC>"
Print #1, "<REPORTNAME>Vouchers</REPORTNAME>"
Print #1, "<STATICVARIABLES>"
Print #1, "<SVCURRENTCOMPANY>TEST CO</SVCURRENTCOMPANY>"
Print #1, "</STATICVARIABLES>"
Print #1, "</REQUESTDESC>"
Print #1, "<REQUESTDATA>"
Print #1, "<TALLYMESSAGE xmlns:UDF=""TallyUDF"">"
Print #1, "<VOUCHER REMOTEID="""" VCHKEY="""" VCHTYPE=""Payment"" ACTION=""Create"" OBJVIEW=""Accounting Voucher View"">"
Print #1, "<OLDAUDITENTRYIDS.LIST TYPE=""Number"">"
Print #1, "<OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS>"
Print #1, "</OLDAUDITENTRYIDS.LIST>"
Print #1, "<DATE>20200407</DATE>"
Print #1, "<VOUCHERTYPENAME>Payment</VOUCHERTYPENAME>"
Print #1, "<VOUCHERNUMBER>5</VOUCHERNUMBER>"
Print #1, "<LEDGERNAME>Suspense Account</LEDGERNAME>"
Print #1, "<AMOUNT>-1500000.00</AMOUNT>"
Print #1, "<LEDGERNAME>Bank Account</LEDGERNAME>"
Print #1, "<AMOUNT>1500000.00</AMOUNT>"
Print #1, "</VOUCHER>"
Print #1, "</TALLYMESSAGE>"
Print #1, "</REQUESTDATA>"
Print #1, "</IMPORTDATA>"
Print #1, "</BODY>"
Print #1, "</ENVELOPE>"
Close #1
End Sub
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,132
Loop through the rows like this, though I'm not sure if the XML structure created by this code is correct because your code shows 1 VOUCHER containing 1 DATE and 2 LEDGERNAMEs and 2 AMOUNTs.
VBA Code:
    Dim r As Long
    With ActiveSheet
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            Print #1, "<DATE>" & Format(.Cells(r, "A").Value, "YYYYMMDD") & "</DATE>"
            Print #1, "<VOUCHERTYPENAME>" & .Cells(r, "E").Value & "</VOUCHERTYPENAME>"
            Print #1, "<VOUCHERNUMBER>" & .Cells(r, "D").Value & "</VOUCHERNUMBER>"
            Print #1, "<LEDGERNAME>" & .Cells(r, "F").Value & "</LEDGERNAME>"
            If IsEmpty(.Cells(r, "B").Value) Then
                Print #1, "<AMOUNT>" & .Cells(r, "C").Value & "</AMOUNT>"
            Else
                Print #1, "<AMOUNT>-" & .Cells(r, "B").Value & "</AMOUNT>"
            End If
        Next
    End With
 

aejaz7

New Member
Joined
Nov 6, 2018
Messages
17
XML structure i will manage but still issue in Loop. This code generate only A2 value only not to last row.
Thank you.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,132
The For Next is looping through the rows, but your code is creating "A.xml" for every row, overwriting the previous file.

I don't know the expected layout of the output file(s), but I suspect the Open XMLFileName For Output As #1 should be outside the loop so that only 1 file is created.

Also the VOUCHER line should be:
VBA Code:
Print #1, "<VOUCHER REMOTEID="""" VCHKEY="""" VCHTYPE=""" & .Cells(r, "J").Value & """ ACTION=""Create"" OBJVIEW=""Accounting Voucher View"">"
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,482
Messages
5,770,349
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top