How do I render HTML in Excel 2010/2013 while keeping the formatting?

I'm extracting from an SQL database in XML format and looking to format the output in Excel. Some columns were entered as rich text and contain HTML formatting; I would like to retain that formatting.
I could write a parser in VBA - but is there an easier way to transform the HTML so that Excel can understand it? I've tried solutions that use IE in the back ground (not supported as I use IE11)  and the DataObject from MS Forms 2.0 (Paste Special with Unicode text is not supported).
The HTML tags are mostly style - font name, bold, underline, etc - with or without spanning.
TIA
    Mike
MikeDigginsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
This may seem dumb.
Here's a simple html file
<html>
<body>
<table>
<tr>
<td>
<i>
<b> Will Excel See this as bold and italics?</b>
</td>
<td> and this just italic?</i></td>
</tr>
</table>
</body>
</html>

Open in new window


I can open HTML files with Excel
open itAnd the formatting is preserved.
result
From there, go File|Save As and save it as an .xls

Does that work for you?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
how are you pulling the data from the database? are you using a server side language such asp or php? if so, just set the content type to:

Content-Type: application/vnd.ms-excel

an example using ASP:

<%response.ContentType="application/vnd.ms-excel"%>
<html>
<body>
<table>
<tr>
<td>1</td>
<td>2</td>
<td>3</td>
<td>4</td>
</tr>
<tr>
<td>5</td>
<td>6</td>
<td>7</td>
<td>8</td>
</tr>
</table>
</body>
</html>

Open in new window


** edit to give source of example
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeDigginsAuthor Commented:
Thanks, both - and nothing's dumb if its meant to help. I'm using a WCF-based service to run a number of stored procedures for various purposes; in each case the output is an XML document. Each row is represented as an element containing the columns returned by the procedure. The document is then consumed by a front end - Excel in this case. I'm using VBA to consume the document, so I'm looking for something that runs in that environment and can parse the embedded HTML.

Here's a short example, including the header:

<?xml version="1.0" standalone="yes"?>
<rptFindData xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">
            <element p2:type="usp_rptFindDataResult" xmlns:p2="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
                        <FirstName>Attn: Mike</FirstName>
                        <RichTextComment>This is &amp;nbsp;the type of output I need to <b>render</b>.</RichTextComment>
      </element>

I'm not too well up on the configuration of this but the content type approach sounds promising - any suggestions that will help me get out of writing a parser gratefully received.
TIA
    Mike
0
Nick67Commented:
I don't do anything with XML.
I'm an Access guy, so when I pull stuff from SQL Server, I don't do it as XML.
Here's a link to using VBA to apply  an XSL to an XML file
http://archive.oreilly.com/pub/post/transforming_xml_in_microsoft.html
http://www.vbforums.com/showthread.php?615474-Transforming-XML-to-HTML-in-Access-with-VBA
But that still requires the creation XSL file

Perhaps that is easy?
Or is that the parser you are hoping to avoid writing?

Do you control the SQL Server?
You have to configure the stored procedures to return XML like it is noted here to get XML
http://www.sqlusa.com/bestpractices2005/multipleoutput/
Wouldn't it simpler to have the SQL Server output the results in a dataset more to your liking?
Executing sprocs with ADO in VBA and using the resulting recordsets isn't hard.
Because it seems that the XML is the impediment, and not by your design.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTML

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.