Reformat XML Results for Excel
One thing you may notice when you return web service results directly to Excel through an XML Map is that mixed content is not automatically formatted. HTML/XML formatting tags such as < b > and < i > appear as “< b >” and “< i >” rather than as bold and italic (Figure 4-14).

Figure 4-14. Excel does not automatically interpret HTML formatting
How to do it
There’s no simple way to prevent this problem, but you can fix it using the automatic text formatting features of Excel. Excel automatically reformats HTML text pasted from the Clipboard, so all you have to do is place the data in the Clipboard as HTML, then paste that data back into cells on the spreadsheet.
In Excel, you access the Clipboard using the DataObject object, so the following code puts the data from each cell of a worksheet into the Clipboard as HTML, then pastes that data back, causing Excel to correctly interpret HTML formatting:
Sub TestReformat( )
' Call Helper function to interpret HTML formatting codes.
ReformatHTML ActiveSheet.UsedRange
End Sub
Sub ReformatHTML(rng As Range)
Dim clip As New DataObject, cell As Range
For Each cell In rng
clip.SetText "<html>" & cell.Value & "<html>"
clip.PutInClipboard
cell.PasteSpecial
Next
End SubHow it works
After you run TestReformat on a worksheet, Excel interprets the HTML formatting codes as if you cut/pasted them from a web page ( ...