Skip to Main Content
Excel 2003 Programming: A Developer's Notebook
book

Excel 2003 Programming: A Developer's Notebook

by Jeff Webb
August 2004
Intermediate to advanced content levelIntermediate to advanced
312 pages
8h 30m
English
O'Reilly Media, Inc.
Content preview from Excel 2003 Programming: A Developer's Notebook

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).

Excel does not automatically interpret HTML formatting

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 Sub

How 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 ( ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham
Excel® 2007 VBA Programmer's Reference

Excel® 2007 VBA Programmer's Reference

John Green, Stephen Bullen, Rob Bovey, Michael Alexander
Office 2003 XML

Office 2003 XML

Simon St. Laurent, Mary McRae, Evan Lenz

Publisher Resources

ISBN: 0596007671Supplemental ContentCatalog PageErrata