PDA

View Full Version : How to import XML to excel without the HTML, I think?



bubbapost
07-28-2010, 01:48 PM
Hello,

I have a friend who is recieving a data dump & in a format that is not easily imported into excel. I think it's a mixture of HTML & XML. I would like to create a macro that will extract the XML from the other data.

I also posted on MrExcel MsgBoard:http://www.mrexcel.com/forum/showthread.php?t=484466

Here is a sample:


&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;
&lt;BODY style=&quot;BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px&quot;&gt;&lt;test this is the body of the text/BODY&gt;</BODY>'
'<BODY source_object_id= source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5764" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;.&amp;nbsp;test this is text.&lt;/BODY&gt;</BODY>'
.1,'<BODY source_object_id="194713" source_object_type="">&lt;HEAD&gt;
&lt;META content="MSHTML 6.00.2900.5803" name=GENERATOR&gt;&lt;/HEAD&gt;
&lt;BODY style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px"&gt;


Can this be done?

Thank you.