How to Create xml file when accounting invoice with inventory stored in excel

Question

sir, i want to create xml file  when user enter data in vb module form and  data stored in separated sheet and should  auto create invoice template,  i find it user module form with help of https://www.exceltrainingvideos.com/automate-invoice-creation-with-vba/  and how to Generate above data in xml from separated sheet.

i could not write complex macro code for account invoice with inventory, because  unique invoice with date and list of inventory data multiple Row,

i below attached jpeg file - saw, i round on picture,  invoice and date , list of inventory data, that is single invoice with date and also included list of list inventory, and some column also zero,  see IGST in first column and CGST AND SGST in second column, 

Please refer any book or sample code for Accounting invoice xml from Excel, not using excel Mapping , it is not working already i tried.

Answer 1

Why XML?

invoice xml import to other software

yes, excel invoice data convert to xml and import to My software, i Expected Macro code, not doing mail merge, i already know row based excel data export to xml but invoice with inventory data like Many to one concept i donot know, how to be done by excel Macro.

i need excel Macro  code not Mail merge concept

Answer 2

If the data was sorted so that the Invoice Total was in the last row for each Invoice ID, as shown below

invoice_id	invoice_date	shipping	invoice_total	product_name	product_price	quantity	total_cost
2246	10092016	0	0	Samsung Fast	89	1	89
2246	10092016	10	349	Samsung Galaxy	250	1	250
2247	10092016	0	0	Samsung Fa- B	250	1	250
2247	10092016	0	0	Samsung Ga -c	30	1	30
2247	10092016	30	0	Samsung Fast -A	50	1	50
2247	10092016	0	0	Samsung Galaxy -B	40	1	40
2247	10092016	10	0	Samsung Fast -A	60	1	60
2247	10092016	0	530	Samsung Ga -A	100	1	100
2248	10092016	0	50	Samsung Fast -A	20	1	20
2249	10092016	80	90	Samsung Fast -A	90	1	90

and the data was used as the data source for a Many to One type Mail Merge making use of that facility on my Merge Tools Add-in with the mail merge main document set up as shown below

<?xml version="1.0" encoding="ISO-8859-2"?>	<Invoices>	<invoice_id>«invoice_id»</invoice_id>	<invoice_date>«invoice_date»</invoice_date>	<invoice_total>«invoice_total»</invoice_total>
<product> <product_name>«product_name»</product_name>	<product_price>«product_price»</product_price>	<quantity>«quantity»</quantity>	<shipping>«shipping»</shipping>	<total_cost>«total_cost»</total_cost>
</product>	</Invoice>

when the Many to One Merge is executed using the Merge to Word Document destination, the file that is produced would be

<?xml version="1.0" encoding="ISO-8859-2"?>	<Invoices>	<invoice_id>2246</invoice_id>	<invoice_date>10092016</invoice_date>	<invoice_total>349</invoice_total>
<product> <product_name>Samsung Fast</product_name>	<product_price>89.00</product_price>	<quantity>1</quantity>	<shipping>0</shipping>	<total_cost>89.00</total_cost>
<product> <product_name>Samsung Galaxy</product_name>	<product_price>250.00</product_price>	<quantity>1</quantity>	<shipping>10</shipping>	<total_cost>250.00</total_cost>
</product>	</Invoice>

If the Convert Table to Text facility in Word is then used to convert the table to text the result will be

<?xml version="1.0" encoding="ISO-8859-2"?> <Invoices> <invoice_id>2246</invoice_id> <invoice_date>10092016</invoice_date> <invoice_total>349</invoice_total>

<product> <product_name>Samsung Fast</product_name> <product_price>89.00</product_price> <quantity>1</quantity> <shipping>0</shipping> <total_cost>89.00</total_cost>

<product> <product_name>Samsung Galaxy</product_name> <product_price>250.00</product_price> <quantity>1</quantity> <shipping>10</shipping> <total_cost>250.00</total_cost>

</product> </Invoice>   

and if Find and replace is used to replace the carriage returns with a space, the final result would be

<?xml version="1.0" encoding="ISO-8859-2"?> <Invoices> <invoice_id>2246</invoice_id> <invoice_date>10092016</invoice_date> <invoice_total>349</invoice_total> <product> <product_name>Samsung Fast</product_name> <product_price>89.00</product_price> <quantity>1</quantity> <shipping>0</shipping> <total_cost>89.00</total_cost> <product> <product_name>Samsung Galaxy</product_name> <product_price>250.00</product_price> <quantity>1</quantity> <shipping>10</shipping> <total_cost>250.00</total_cost> </product> </Invoice>    

and likewise for the other invoices

<?xml version="1.0" encoding="ISO-8859-2"?> <Invoices> <invoice_id>2247</invoice_id> <invoice_date>10092016</invoice_date> <invoice_total>530</invoice_total> <product> <product_name>Samsung Fa- B</product_name> <product_price>250.00</product_price> <quantity>1</quantity> <shipping>0</shipping> <total_cost>250.00</total_cost> <product> <product_name>Samsung Ga -c</product_name> <product_price>30.00</product_price> <quantity>1</quantity> <shipping>0</shipping> <total_cost>30.00</total_cost> <product> <product_name>Samsung Fast -A</product_name> <product_price>50.00</product_price> <quantity>1</quantity> <shipping>30</shipping> <total_cost>50.00</total_cost> <product> <product_name>Samsung Galaxy -B</product_name> <product_price>40.00</product_price> <quantity>1</quantity> <shipping>0</shipping> <total_cost>40.00</total_cost> <product> <product_name>Samsung Fast -A</product_name> <product_price>60.00</product_price> <quantity>1</quantity> <shipping>10</shipping> <total_cost>60.00</total_cost> <product> <product_name>Samsung Ga -A</product_name> <product_price>100.00</product_price> <quantity>1</quantity> <shipping>0</shipping> <total_cost>100.00</total_cost> </product> </Invoice>    

<?xml version="1.0" encoding="ISO-8859-2"?> <Invoices> <invoice_id>2248</invoice_id> <invoice_date>10092016</invoice_date> <invoice_total>50</invoice_total> <product> <product_name>Samsung Fast -A</product_name> <product_price>20.00</product_price> <quantity>1</quantity> <shipping>0</shipping> <total_cost>20.00</total_cost> </product> </Invoice>    

<?xml version="1.0" encoding="ISO-8859-2"?> <Invoices> <invoice_id>2249</invoice_id> <invoice_date>10092016</invoice_date> <invoice_total>90</invoice_total> <product> <product_name>Samsung Fast -A</product_name> <product_price>90.00</product_price> <quantity>1</quantity> <shipping>80</shipping> <total_cost>90.00</total_cost> </product> </Invoice>    

Note that as for Invoice 2247 in your data, two of the records have individual shipping costs, I moved the shipping cost into the <product> </product> area.

For a not unreasonable fee, I could produce a customized version of my Add-in that would eliminate the need for the final two steps (converting the table to text and removing the carriage returns (if the latter is necessary))

If you want to play around with it, my Merge Tools Add-in is contained in the MERGE TOOLS ADD-IN.zip file that you can download from the following page of my One Drive:

http://bit.ly/1hduSCB

Extract the files from the archive and read the:

“READ ME – Setting up and using the Merge Tools Add-in.pdf

to see how to install and use the various tools.  Using those tools, it is possible to perform the following types of merge that cannot be done with Mail Merge “out-of-the-box”:

●    Merge to e-mail messages either with or without attachments, with the documents created by the merge being sent as either Word or PDF attachments or as the body of the e-mail message.

●    Merge to individual documents in either Word or PDF format with the filenames being supplied by the data in one of the fields in the data source

●    Many to One type merges, which can be used for creating documents such as invoices where there are multiple records in the data source that have common data in one of the fields

●    Merging to a document that will include a chart that is unique to each record in the data source

●    Merging a document with Content Controls

●    Merging a document that contains Legacy FormFields

●    Duplex Merges

●    Merging to a printer that will collate and staple the output created from each record in the data source.

The requirements for using the system are:

●    The mail merge main document must be of the Letters type, though that does not mean that the output cannot be sent as an e-mail message where relevant.

●    For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet and that worksheet must be the first sheet in the Excel workbook. If the data is on some other sheet, you can easily move that sheet so that it is the first sheet in the workbook by clicking on the sheet tab and dragging it to the left.  For the Chart Merge utility, see the Mail Merging with Charts document that is included in the Merge Tools Add-in Zip file for additional requirements for the data source for use with that utility

●    For a data source in the form of an Excel worksheet, the field names must be in the first row of the worksheet and there must be a field name in all of the cells in that row that are within the range of columns that contain the data.

●    For both types of data source, the field names must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9). The number of characters in the field names, including spaces, must not be more than 40

.

 i Expected Macro code, not doing mail merge, i already know row based excel data export to xml but invoice with inventory data like Many to one concept i donot know, how to be done by excel Macro.

i need excel Macro  code not Mail merge concept

Answer 3

And you want someone to develop it for you?

Answer 4

And you want someone to develop it for you?

100 % No Need Any one, i asked example only, not need full things, just i asked sample code not full code, one to many vba loop condition. list of list of inventory data

Answer 5

Dim ArrData as Variant

Dim strxml as String

Dim lngInvoice as Long

Dim i as Long

Set ArrData=Sheets([sheet containing the data]).Range("A1").CurrentRegion.Value

strxml = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "ISO-8859-2" & Chr(34) & "?> <Invoices>"

For i = Lbound(ArrData, 1) + 1 to Ubound(ArrData, 1)

NewInvoice:

lngInvoice = ArrData(i, 1)

strxml = strxml & " <invoice_id>" & lngInvoice & "</invoice_id>  <invoice_date>"  & ArrData(i, 2) & "</invoice_date>  ….. <invoice_total> & ArrData(i, 4) & "</invoice_total>"

NewProduct:

strxml = strxml & " <product> <product_name>" & ArrData(i, 5) & "</product_name> …. <total_cost>" & ArrData(i, 8) & "</total_cost> </product>"

If ArrData(i + 1, 1) = lngInvoice then

i = i + 1

GoTo NewProduct

End if

i = i + 1

GoTo NewInvoice

Next i

strxml = strxml & "</invoices>"