asked on

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

SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Big Monty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeDiggins

ASKER

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 &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

Nick67

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.