Export from Excel to XML VBA Code

A

aejaz7

New Member
Joined
Nov 6, 2018
Messages
20
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
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
 
Upvote 0
XML structure i will manage but still issue in Loop. This code generate only A2 value only not to last row.
Thank you.
 
Upvote 0
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"">"
 
Upvote 0
You must log in or register to reply here.

Similar threads

RAJESH1960
  • Solved
VBA code - resize
Replies
8
Views
434
RAJESH1960
RAJESH1960

Forum statistics

Threads
1,214,833
Messages
6,121,864
Members
449,052
Latest member
Fuddy_Duddy

Share this page

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
Back
Top