• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Xlsx to XMl Conversion through MAcro Need Help

tanmay2501

New Member
HI All,
Can anyone help me out with the Macro that converts that XLSX to XML through VBA on a button click
There would be Nodes in the Data you can seek the help from example below
(Also look in the blank value generation in the xml with only closing tag )

For eg:-
Sheet 1 contains the following data
Column 1 Column 2 Column 3 Column 4
Row 1 Data\School\Name Data\School\Area Data\Affiliation\Type Data\Affiliation\Code
Row 2 ABC Pune CBSE 001
Row 3 XYZ Blank ICSE 002


XML File after conversion
<Data>
<School>
<Name>ABC</Name>
<Area>Pune</Area>
</School>
<Affiliation>
<Type>CBSE</Type>
<Code>001</Code>
</Affiliation>
</Data>
<Data>
<School>
<Name>XYZ</Name>
</Area> (Notice Here Only the end tag is here as there is no data in the sheet for area in the 2nd row)
</School>
<Affiliation>
<Type>ICSE</Type>
<Code>002</Code>
</Affiliation>
</Data>


Thanks for the Help :)
 
Write the below code user the button click event.

ActiveWorkbook.SaveAs Filename:="FilePath/Filename", FileFormat:=xlXMLSpreadsheet, ReadOnlyRecommended:=False
 

tanmay2501

New Member
by using this code you are saving the whole excel sheet not converting the only sheet 1 data. Dear i just want the desired output nothing else

Sheet 1 data looks like


Column 1 Column 2 Column 3 Column 4
Row 1 Data\School\Name Data\School\Area Data\Affiliation\Type Data\Affiliation\Code
Row 2 ABC Pune CBSE 001
Row 3 XYZ Blank ICSE 002

Output for eg:-

<Data>
<School>
<Name>ABC</Name>
<Area>Pune</Area>
</School>
<Affiliation>
<Type>CBSE</Type>
<Code>001</Code>
</Affiliation>
</Data>
<Data>
<School>
<Name>XYZ</Name>
</Area> (Notice Here Only the end tag is here as there is no data in the sheet for area in the 2nd row)
</School>
<Affiliation>
<Type>ICSE</Type>
<Code>002</Code>
</Affiliation>
</Data>


Tried you above code
ActiveWorkbook.SaveAs Filename:="FilePath/Filename", FileFormat:=xlXMLSpreadsheet, ReadOnlyRecommended:=False

But it will give the below output


61115
 
Top