Vba from VB code

vrunda · Jul 6, 2020

I have a vb code which I want to convert into vba to work in excel

Code:

Private Function LedgerMasterText(ByVal intI As Integer) As String Dim strTemp As String

Dim strTxt As String

sbExport.SimpleText = intI & ": " & Trim$(xlWS.Cells(intI, 2)) strTxt = vbNullString

strTxt = _

"<ENVELOPE>" & vbCrLf & _ "<HEADER>" & vbCrLf & _

"<VERSION>1</VERSION>" & vbCrLf & _ "<TALLYREQUEST>Import </TALLYREQUEST>" & vbCrLf & _ "<TYPE>Data</TYPE>" & vbCrLf & _

"<ID>All Masters</ID>" & vbCrLf & _ "</HEADER>" & vbCrLf & _

"<BODY>" & vbCrLf & _ "<DESC>" & _

"<STATICVARIABLES>" & _ "<SVCURRENTCOMPANY>" & _

"##SVCurrentCompany" & _

"</SVCURRENTCOMPANY>" & _

"</STATICVARIABLES>" & _

"</DESC>" & _

"<DATA>" & vbCrLf & _

"<TALLYMESSAGE>" & vbCrLf & _ "<LEDGER>" & vbCrLf & _ "<NAME.LIST>" & vbCrLf & _

"<NAME>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 2))) & "</NAME>" & vbCrLf

If Trim$(xlWS.Cells(intI, 1)) <> vbNullString Then

strTxt = strTxt & "<NAME>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 1))) & "</NAME>" & vbCrLf

End If

strTxt = strTxt & _

"</NAME.LIST>" & vbCrLf & _

"<PARENT>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 3))) & "</PARENT>" & vbCrLf

'-----Optional----------

If Trim$(xlWS.Cells(intI, 4)) <> vbNullString Then

strTxt = strTxt & "<ADDRESS.LIST>" & vbCrLf

strTxt = strTxt &

"<ADDRESS>" & ReplaceXml Text(Trim$(xlWS.Cells(intI, 4))) & "</ADDRESS>" _ & vbCrLf 'Address 1

If Trim$(xlWS.Cells(intI, 5)) <> vbNullString Then

strTxt = strTxt & _

"<ADDRESS>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 5))) &

"</ADDRESS>" & vbCrLf 'Address 2

If Trim$(xlWS.Cells(intI, 6)) <> vbNullString Then

strTxt = strTxt & _

"<ADDRESS>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 6))) & "</ADDRESS>" & vbCrLf 'Address 3

strTxt = strTxt & "</ADDRESS.LIST>" & vbCrLf

End If

If Trim$(xlWS.Cells(intI, 7)) <> vbNullString Then strTxt = strTxt & _

"<STATENAME>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 7))) &

"</STATENAME>"

End If

If Trim$(xlWS.Cells(intI, 8)) <> vbNullString Then strTxt = strTxt &

"<LEDGERPHONE>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 8))) & _ "</LEDGERPHONE>"

End If

If Trim$(xlWS.Cells(intI, 9)) <> vbNullString Then strTxt = strTxt &

"<LEDGERFAX>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 9))) & _ "</LEDGERFAX>"

End If

If Trim$(xlWS.Cells(intI, 10)) <> vbNullString Then strTxt = strTxt &

"<EMAIL>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 10))) & _ "</EMAIL>"

End If

strTemp = ReplaceXmlText(Trim$(xlWS.Cells(intI, 2))) strTxt = strTxt &

"<ADDITIONALNAME>" & Trim$(strTemp) & _ "</ADDITIONALNAME>" & _ vbCrLf

strTxt = strTxt & _ "</LEDGER>" & vbCrLf & _

"</TALLYMESSAGE>" & vbCrLf & _ "</DATA>" & vbCrLf & _

"</BODY>" & vbCrLf & _ "</ENVELOPE>" & vbCrLf

LedgerMasterText = strTxt End Function

vrunda · Jul 6, 2020

This code converts the data in Excel to Tally understandable XML format and sent to the port in which Tally.ERP 9 is running.

Code:

objXml.open "Export", "http://localhost:9000", False

objXml.send XMLToPost

This code sends the above generated XML Data to Tally.ERP 9 which is running at a predefined port

vrunda · Jul 7, 2020

Can we not convert the visual basic code to excel vba code

vletm · Jul 7, 2020

vrunda
Hint: Have You refresh You memory from ...

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

How to get the Best Results at Chandoo.org
For the best/fastest results, Upload a Sample File using the "Upload a File" button at the bottom of the page.
As well as You should show needed result from Your sample data.

vrunda · Jul 8, 2020

Sorry for not posting sample files.
Excel file is attached, xml could not be attached so converted to text file.
Sorry again.

vletm · Jul 8, 2020

vrunda
You wrote I have a vb code which I want to convert into vba to work in excel

a) where is that Your vb code?
b) where is Your sample data?
c) where is needed result from Your sample data?

vrunda · Jul 9, 2020

vrunda said:

I have a vb code which I want to convert into vba to work in excel

Code:

Private Function LedgerMasterText(ByVal intI As Integer) As String Dim strTemp As String

Dim strTxt As String

sbExport.SimpleText = intI & ": " & Trim$(xlWS.Cells(intI, 2)) strTxt = vbNullString

strTxt = _

"<ENVELOPE>" & vbCrLf & _ "<HEADER>" & vbCrLf & _

"<VERSION>1</VERSION>" & vbCrLf & _ "<TALLYREQUEST>Import </TALLYREQUEST>" & vbCrLf & _ "<TYPE>Data</TYPE>" & vbCrLf & _

"<ID>All Masters</ID>" & vbCrLf & _ "</HEADER>" & vbCrLf & _

"<BODY>" & vbCrLf & _ "<DESC>" & _

"<STATICVARIABLES>" & _ "<SVCURRENTCOMPANY>" & _

"##SVCurrentCompany" & _

"</SVCURRENTCOMPANY>" & _

"</STATICVARIABLES>" & _

"</DESC>" & _

"<DATA>" & vbCrLf & _

"<TALLYMESSAGE>" & vbCrLf & _ "<LEDGER>" & vbCrLf & _ "<NAME.LIST>" & vbCrLf & _

"<NAME>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 2))) & "</NAME>" & vbCrLf

If Trim$(xlWS.Cells(intI, 1)) <> vbNullString Then

strTxt = strTxt & "<NAME>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 1))) & "</NAME>" & vbCrLf

End If

strTxt = strTxt & _

"</NAME.LIST>" & vbCrLf & _

"<PARENT>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 3))) & "</PARENT>" & vbCrLf

'-----Optional----------

If Trim$(xlWS.Cells(intI, 4)) <> vbNullString Then

strTxt = strTxt & "<ADDRESS.LIST>" & vbCrLf

strTxt = strTxt &

"<ADDRESS>" & ReplaceXml Text(Trim$(xlWS.Cells(intI, 4))) & "</ADDRESS>" _ & vbCrLf 'Address 1

If Trim$(xlWS.Cells(intI, 5)) <> vbNullString Then

strTxt = strTxt & _

"<ADDRESS>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 5))) &

"</ADDRESS>" & vbCrLf 'Address 2

If Trim$(xlWS.Cells(intI, 6)) <> vbNullString Then

strTxt = strTxt & _

"<ADDRESS>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 6))) & "</ADDRESS>" & vbCrLf 'Address 3

strTxt = strTxt & "</ADDRESS.LIST>" & vbCrLf

End If

If Trim$(xlWS.Cells(intI, 7)) <> vbNullString Then strTxt = strTxt & _

"<STATENAME>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 7))) &

"</STATENAME>"

End If

If Trim$(xlWS.Cells(intI, 8)) <> vbNullString Then strTxt = strTxt &

"<LEDGERPHONE>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 8))) & _ "</LEDGERPHONE>"

End If

If Trim$(xlWS.Cells(intI, 9)) <> vbNullString Then strTxt = strTxt &

"<LEDGERFAX>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 9))) & _ "</LEDGERFAX>"

End If

If Trim$(xlWS.Cells(intI, 10)) <> vbNullString Then strTxt = strTxt &

"<EMAIL>" & ReplaceXmlText(Trim$(xlWS.Cells(intI, 10))) & _ "</EMAIL>"

End If

strTemp = ReplaceXmlText(Trim$(xlWS.Cells(intI, 2))) strTxt = strTxt &

"<ADDITIONALNAME>" & Trim$(strTemp) & _ "</ADDITIONALNAME>" & _ vbCrLf

strTxt = strTxt & _ "</LEDGER>" & vbCrLf & _

"</TALLYMESSAGE>" & vbCrLf & _ "</DATA>" & vbCrLf & _

"</BODY>" & vbCrLf & _ "</ENVELOPE>" & vbCrLf

LedgerMasterText = strTxt End Function

This is the code!! And excel files and xml files are attached in previous post

vletm · Jul 9, 2020

vrunda
Did You notice - I tried to ask three different questions?
Your answers to questions b & c are missing.

vrunda · Jul 9, 2020

Sir answer to b is excel file & answer to c is xml file
The code generates excel file data to xml
Hope this helps
Thank you!!

vletm · Jul 9, 2020

vrunda
Okay ...
Testing ...
eg Excel-file's cell A2 has text Ramesh - okay
Where is same text in Your given xml-file? ... do it need to notice at all?
Can You confirm that from Ledger.xls has created Master-file?
If so ... then there would be some challenges?

vrunda · Jul 9, 2020

ok I made it short to notice & generating this much xml file will also do
sending as text beacuse xml not uploading

vletm · Jul 9, 2020

vrunda
Do You really try to write that from Ledger.xls -file should create ledger new-file?
Your ledger new -file and Ledger.xls -file has too many differences.
Those cannot be like input and output!
If You cannot give something real - which has clear logic - then I cannot continue.

vrunda · Jul 9, 2020

Sir this xml is generated by software & is real .
Where you find it unreal i am unable to understand. Software generates a long xml with unwanted data, so I deleted unwanted data , thats it.
I just needed a vba to convert excel to same xml fromat, if possible
Thank you!!

vletm · Jul 9, 2020

vrunda
This is Your Sample input (Ledger.xls) - okay?

You have already give two different outputs from above input.
That is not possible!
There should be clear rules, how that wanted output should create.
As You wrote: That's it.

vrunda · Jul 9, 2020

vrunda said:
ok I made it short to notice & generating this much xml file will also do
sending as text beacuse xml not uploading

Sir This output is correct. It contains all information of excel sheet with some xml tags .
The software generates long xml which is not required with unwanted information ,
this short xml is appropriate requirement . I just deleted the unwanted data in xml

vletm · Jul 9, 2020

vrunda
You wrote: Sir This output is correct
... then You should able to write clear rules, how correct output has done?
Why there are differences with LedgerNames as well as with Cities and States?
... or do Your I just deleted the unwanted data in xml has clear rules too?

Ramesh *4
Suresh *2
Sundry Debtors *2
Bangalore * ZERO
Patna *1
Kamataka * ZERO
Bihar * 1

Chihiro · Jul 9, 2020

@vrunda

There's multiple issues here:

1) There's some missing data/info in your sheet1. We would have no idea how these are filled.

Ex: REPORTNAME, SVCURRENTCOMPANY, COUNTRYNAME, GSTREGISTRATIONTYPE, VATDEALERTYPE, LANGUAGENAME are no where to be found in Sheet1.

2) The xml structure is such that it can't be exported using schema.

TALLYREQUEST and REPORTNAME is only defined once in the document. Meaning structure isn't consistent (normalized) across all elements.

3) I think there's mistake in your xml "ledger new.txt".

LANGUAGENAME.LIST, NAME.LIST's NAME node shows Ramesh at both Line 27 & 46.

Then again, when looking at your "ledger.txt", LANGUAGENAME.LIST, NAME.LIST's NAME node isn't LedgerName in sheet1, and we have no idea how this is defined as well.

To reiterate what @vletm has been trying to explain to you... You must provide us with explanation and reasoning on HOW values are mapped to the xml and WHERE these values comes from. Your Sheet1 does not have enough info for us to help you.

vletm · Jul 9, 2020

Chihiro
This would be one clear reason:
I just deleted the unwanted data in xml

vrunda · Jul 9, 2020

ok Sir ,
I shall generate xml again & get back to you.
Regarding REPORTNAME, SVCURRENTCOMPANY, COUNTRYNAME, GSTREGISTRATIONTYPE, VATDEALERTYPE, LANGUAGENAME
these are constant for every name in excel sheet or every excel sheet .Some tags are common. Please refer vb code in first post.

"LANGUAGENAME.LIST, NAME.LIST's NAME node shows Ramesh at both Line 27 & 46. " Yes sir while deleting i might made mistake.
Now attaching both files with required xml "ledgernew" & with unwanted data generated by software "1Ledgernew".
I am really sorry for inconvinience.
Also see the vb code in first post.
Thank you

Chihiro · Jul 9, 2020

Your VB code is incomplete. I have no idea what "sbExport" object refers to and what library is referenced here.

Also, in the code SVCURRENTCOMPANY value is defined in section below....

Code:

"<SVCURRENTCOMPANY>" & _ "##SVCurrentCompany" & _"</SVCURRENTCOMPANY>"

Yet, your examples show different value than literal string "##SVCurrentCompany". So, there must be something else that's either replacing, or further processing this....

The function must be part of much larger module/subroutine and string generated from the function is passed off into other process to generate final XML to post.

vletm · Jul 9, 2020

vrunda
If You cannot give something clear and valid, then You'll have a challenge.

vrunda · Jul 12, 2020

Due to internet issues I could not come back.
I shall post the code if I get it. Very soon I might get... m searching .
Thank You vletm & chihiro!! Thanks a lot

Vba from VB code

Member

Member

Member

Excel Ninja

Member

Attachments

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Attachments

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Excel Ninja

Excel Ninja

Member

Attachments

Excel Ninja

Excel Ninja

Member