HTML in Cells II

While I was working on this newsgroup post to copy formatted text from Word to Excel while preserving formatting and maintaining line breaks, I stumbled on a way to enter HTML in cells.

InCellHTML1

becomes

InCellHTML2

with this macro

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim objData As DataObject
    Dim sHTML As String
    Dim sSelAdd As String
   
    Application.EnableEvents = False
   
    If Target.Cells.Count = 1 Then
        If LCase(Left(Target.Text, 6)) = “<html>” Then
            Set objData = New DataObject
           
            sHTML = Target.Text
           
            sHTML = Replace(sHTML, “<html>”, “<html><style>br{mso-data-placement:same-cell;}</style>”)
           
            objData.SetText sHTML
            objData.PutInClipboard
           
            sSelAdd = Selection.Address
            Target.Select
            Me.PasteSpecial “Unicode Text”
            Me.Range(sSelAdd).Select
           
        End If
    End If
   
    Application.EnableEvents = True
   
End Sub

I haven’t quite got the line break part worked out here. You may notice that I try to put a style tag in the string before I put it in the clipboard. This doesn’t seem to have any effect. The style tag worked with the aforementioned newsgroup post, but didn’t work in this situation. In this case, text after a br tag is moved to the cell below, when I would prefer that it act like an Alt+Enter and create a new line in the same cell. I’m not quite sure why there’s a difference.

I’ve used the PasteSpecial method of the Worksheet object instead of the Range object. It gives me the “format” argument that I need to specify Unicode Text where the Range’s method doesn’t have that argument. That means I had to account for any “move selection after entry” situation. I couldn’t think of a better way to do that, but I’m sure there was.

You may also notice that I, in a most cowardly fashion, limited the event to only work when one cell was being changed. I like to allow Change events to work on multiple cells and I had originally set this up that way. Then I deleted about 20 columns, which means the Change event was checking 1.2 million cells. Ugh. I may change it to loop though the cells using the Find method instead of checking with the Left function. That would solve the problem I had deleting a bunch of columns, but who knows what other problems it would create.

Now I need to figure out a way to do the reverse. I suppose a lengthy SaveAs HTML will be in order for that.

33 Comments

  1. Cool idea, Dick. That might be a useful SmartTag – recognise any cell starting with and pop up a smart tag offering to change it to html text. I’ll see if I can knock one up…

  2. (A short while later )
    Yep, maybe the first useful SmartTag I’ve come across. Download the installation file while it’s hot!

  3. Juan Pablo Gonzilez says:

    Stephen,

    It installed correctly, and it does recognize a cell that starts with < html >. However, if I put later in another cell something different, like:

    < html >< b >Testing < i >more< /i > here< /b >

    It puts the “original” text (the first cell that was converted).

    Is there a public variable or something that might be “memorizing” the previous entries ?

  4. Juan Pablo Gonzilez says:

    That html did work there… I put the spaces and everything… oh well, it’s the html representation of that text that I’m using.

  5. This is fantastic.

    Using Paste operations is what I had discovered too…

    If only there were a way to avoid the use of the clipboard.

  6. Hi Juan Pablo

    I didn’t clear the clipboard after using it, so the original text was still in it. I’ve uploaded an update, which also handles the br issue using an after-paste replace. If anyone’s going to use it, I guess it should try to store and restore the clipboard contents…

  7. Jake Marx says:

    Stephen,

    I get the following error when I use your smart tag:

    “Microsoft Office Excel cannot find any data to replace. Check if your search formatting and criteria are defined correctly….blah, blah, blah”

    I don’t know much about smart tags, so maybe I did something wrong?

  8. D’Oh! *!&%!* DisplayAlerts!

    I’ve updated it…

    Regards

    Stephen Bullen

  9. Colo says:

    Stephen,
    How can I use your code? It seems HTML in a cell is recognized by your program but I cannot convert it.
    Please have a look at the screen capture.
    http://puremis.net/excel/SmartTag.avi

    Regards,
    Colo

  10. Colo,
    The ‘action’ taken by the smart tag is exactly the same as Dick started this article with – the dll just wraps that with the Smart Tag infrastructure to recognise the and show the tag in the cell. When you click the ‘Convert it’ item, you’re running Dick’s code. So does Dick’s work in your case?

  11. Colo says:

    Stephen, Thanks for your help. It was my misunderstanding.

  12. Randy Harmelink says:

    How about a single process to convert the selected range? Something like:

    =====================================================
    Private Declare Function RegisterClipboardFormat Lib “user32? _
    Alias “RegisterClipboardFormatA” (ByVal lpString As String) As Long
    Sub ConvertHTMLToText()
    ‘ Adapted from http://www.aboutvb.de/vba/artikel/vbawdhtmltodoc.htm
    nCFHTML = RegisterClipboardFormat(“HTML Format”)
    For Each oCell In Selection
    nClipboardText = “Version:0.9? & vbCrLf
    nClipboardText = nClipboardText & “StartHTML:-1? & vbCrLf
    nClipboardText = nClipboardText & “EndHTML:-1? & vbCrLf
    nClipboardText = nClipboardText & “StartFragment:000081? & vbCrLf
    nClipboardText = nClipboardText & “EndFragment:°°°°°°” & vbCrLf
    nClipboardText = nClipboardText & oCell.Value & vbCrLf
    nClipboardText = Replace(nClipboardText, “°°°°°°”, Format$(Len(nClipboardText), “000000?))
    With New DataObject
    .Clear
    .SetText StrConv(nClipboardText, vbFromUnicode), nCFHTML
    .PutInClipboard
    End With
    ActiveSheet.Paste Destination:=oCell
    Next oCell
    End Sub
    =====================================================

  13. Amogh says:

    Dear Sir,
    I want to do esactly an opposite way I am having data in an excel sheet with different colour fromaty . I need to convert that data into HTML format. HJow can we achieve this. This is an exactly reverrse case that you have explained

  14. Dave Braden says:

    Hi Dick,
    Man, this is incredibly timely for me. Thanks to you and Stephen.

    I might need, still, to use your code above; I can’t compile it for lack of knowing what type DataObject is – where it is described?. In fact, I just don’t see what’s going on at all. What’s with the “br{ms0-…}” line? Why the need for the Select statements?

    And I second Amogh’s request. Please.

  15. Hey Dave: DataObject allows you to place text directly in the clipboard. Set a reference to MS Forms 2.0 to get access to it. If you have a userform in your project, the reference will already be there. MSDN probably has some info on the object, but I think once you see its methods in the Object Browser, you won’t require any further instruction.

    You don’t need the mso- stuff if you just want to format the text. This all came about because I was trying to copy multiple lines into one cell (as opposed to having them distributed one per line). That particular tag came from inspecting MS’s html when a workbook is saved and there’s a control+enter in a cell. If your cell has multiple lines, you may need that tag, but I don’t remember if I ever got that to work properly.

    Re Select: The Unicode argument to PasteSpecial is only avialable from the Worksheet Object, so I had to select the original cell to paste the clipboard text as unicode, then go back and select the cell that was active after the change event fired. I’d love to hear of a better way to do this. I had an opportunity to use this recently, but ended up leaving the formatting for all the characters the same because I didn’t want to introduce Selecting into an already complicated procedure.

  16. Amogh: That would be nice, but I haven’t found an easy way to do it. I can think of two approaches, neither of which I like. I could loop through the characters of the cell, determine their formatting, and build html tags into the value. No matter how thorough I was, there would always be something missing.

    I could also save a copy of the workbook as html, dig through the html file for that cell, and copy the html that Excel creates. There would probably be a lot of extra crap in there and I hate to create temporary documents just for that procedure. Ideas are welcome.

  17. Tushar Mehta says:

    Dick: You might find it easier to save it as a XML spreadsheet file and parse that. It’s a lot easier than dealing with a HTML document.

    In the former, the contents of B4 that is formatted very wierdly look like:
    <Row ss:Index=”4? ss:Height=”15.75?>
    <Cell ss:Index=”2? ss:StyleID=”s21?><ss:Data ss:Type=”String”
    xmlns=”http://www.w3.org/TR/REC-html40?><B><I>h</I></B><Font>e</Font><B>ll</B><Sub>o</Sub><Font> </Font><I><S>t</S></I><S>h</S><U>e</U><Font>r</Font><Sup>e</Sup></ss:Data></Cell>
    </Row>
    Hopefully, the above will come through correctly. ;-)

    The latter (i.e., the HTML) is much more difficult to parse. Each different format combination results in a different style. For example, I had 7 styles such as:
    .font01169
        {color:windowtext;
        font-size:10.0pt;
        font-weight:400;
        font-style:normal;
        text-decoration:none;
        font-family:Arial;
        mso-generic-font-family:auto;
        mso-font-charset:0;}
    .font51169
        {color:windowtext;
        font-size:10.0pt;
        font-weight:700;
        font-style:normal;
        text-decoration:none;
        font-family:Arial, sans-serif;
        mso-font-charset:0;}
    .font61169
        {color:windowtext;
        font-size:10.0pt;
        font-weight:400;
        font-style:normal;
        text-decoration:underline;
        text-underline-style:single;
        font-family:Arial, sans-serif;
        mso-font-charset:0;}

    And, the actual cell contents look like:
    <td height=21 class=xl221169 style=’height:15.75pt’>h<font class=”font01169?>e</font><font
    class=”font51169?>ll</font><font class=”font81169?><sub>o</sub></font><font
    class=”font01169?> </font><font class=”font91169?><s>t</s></font><font
    class=”font101169?><s>h</s></font><font class=”font61169?>e</font><font
    class=”font01169?>r</font><span style=’display:none’><font class=”font71169?><sup>e</sup></font></span></td>

    Also, unlike the XML, the html has no direct indication of what row or column a cell corresponds to. So, one would have to parse the *entire* file to reconstruct the table.

    The downside with the XML is that at least one of the formatting codes is different from the HTML. The HTML for strikthrough is <strike> whereas in the XML is appears as <S> Of course, there might be other differences. And, I don’t know how to find or interpret the schema for the XML spreadsheet.

    Of course, it might be a lot more elegant if one figured out how to use the ExportXML method. I don’t know enough about how XL handles XML to figure it out.

  18. doco says:

    I have searched in vain for tutorials on creation of smart tags / smart documents. (I have found some but usually in C# / .NET) And no mention in any of the books I have. Any sources out there Stephen, that you would recommend?

    Have downloaded the SDK. I have Visual Basic 6.0 as well as Office 2003 Professional SP2.

    What did you use to create the .dll ?

    TIA
    doco

  19. ritesh says:

    hi
    i am getting an error while runningthis macro as VBA cann’tunderstand “DataObject” object.
    Please Help………

  20. Rana says:

    Can i use excel as database in web page?

  21. Larry says:

    I’ve been looking at the main post here and seeing it it solves my issue, but I can’t get it to work. What I would like to do is aving something like “Hello as data in one of my cells and when I do SaveAsWebPage, I want that HTML from the cell to be in the HTML table generated by Excel. It doesn’t work because when I save as webpage, Excel is converting the html data in my cell into “<font colot=blue>
    How can I get it to preserve my html data in my cells when I do the saveaswebpage?

    Thanks so much!
    -Larry

  22. Larry. Try escaping any html tag characters to have it render properly in these comments. When I put <strong>Hello</strong> into A1 and save as a web page, it preserves the tags – that is it shows exactly what’s in the cell rather than Hello

  23. Ben says:

    Heya:

    You wrote:

    > I haven’t quite got the line break part worked out here.

    Never have I! We’ll let’s just say I’m getting a bit anti-microsoft.

    What I tried is as follows:

    Replace the ‘s with Chr(30), special-paste this as “Unicode”
    into a cell.

    Then run the following macro (executes on ActiveCell):
    Sub ReplaceChr30asNewLines()
    For n = 1 To Len(ActiveCell.Text)
    If (Mid(ActiveCell.Text, n, 1) = Chr(30)) Then
    ‘ This simply refuses to work when > 255
    ActiveCell.Characters(n, 1).Text = Chr(10)
    End If
    Next
    End Sub

    The above works if the cell has less than 255 char’s.

    It’s the old “Microsoft sacked the guy who wrote the code and
    nobody here can fix it in 5 years” problem!

    Brr…Microsoft!

  24. Ben says:

    I wrote:

    > Replace the ‘s with Chr(30), special-paste this as
    > “Unicode” into a cell.

    Should read as:
    Replace the newlines as Chr(30), then special-paste this
    as “Unicode” into a cell.

  25. GR says:

    I have an html code like this:
    hello worldthis is a div tag

    Is it possible to paste this code on a single cell in excel? when I try to paste the code I got something like this:
    a hello world
    b this is a div tag

    I need to display this code in a single cell.

  26. Wannes says:

    Hi Dick,

    This looks really helpful for me. Only problem I have with this is that the should create a new line within the cell. I don’t really understand your code, so don’t know how to change it.

    Have you been able to do so by now?

    Kind Regards,

    Wannes

  27. Private Sub Worksheet_Change(ByVal Target As Range)
       
        Dim objData As DataObject
        Dim sHTML As String
        Dim sSelAdd As String
           
        Const sTEMP As String = “||||”
       
        Application.EnableEvents = False
       
        If Target.Cells.Count = 1 Then
            If LCase(Left(Target.Text, 6)) = “<html>” Then
                Set objData = New DataObject
               
                sHTML = Target.Text
           
                sHTML = Replace(sHTML, “<br />”, sTEMP)
               
                objData.SetText sHTML
                objData.PutInClipboard
               
                sSelAdd = Selection.Address
                Target.Select
                Me.PasteSpecial “Unicode Text”
                Me.Range(sSelAdd).Select
                Target.Value = Replace(Target.Value, sTEMP, Chr$(10))
               
            End If
        End If
       
        Application.EnableEvents = True
       
    End Sub

    This seems to work for multi-line stuff.

  28. Ciprian says:

    Hi.

    I have a cell comment. the comment is formated with some bolds and paracgraph/breaklin(exel formatting).

    can you please help me to do two thinks:

    1. copy the formated text to a adiacent cell(i found only to copy text – after copy the text looses the “bold formatting”)
    2. transform excel bold and breakline into html tags.

    thanx a lot.

    Sub ShowCommentsNextCell()
    ‘based on code posted by Dave Peterson 2003-05-16
    Application.ScreenUpdating = False

    Dim commrange As Range
    Dim mycell As Range
    Dim curwks As Worksheet

    Set curwks = ActiveSheet

    On Error Resume Next
    Set commrange = curwks.Cells _
    .SpecialCells(xlCellTypeComments)
    On Error GoTo 0

    If commrange Is Nothing Then
    MsgBox “no comments found”
    Exit Sub
    End If

    For Each mycell In commrange
    If mycell.Offset(0, 1).Value = “” Then
    mycell.Offset(0, 1).Value = mycell.Comment.Text
    End If
    Next mycell

    Application.ScreenUpdating = True

    End Sub

    this code copy the text but i loose formating and i need to transform bold and “line break” to html tags.

    sry for my english

  29. tom says:

    hi all i need some help…

    if i product a table on excel, e.g. a table of months and days for excel, how do i turn that information into html and make the whole html code appear in one cell.. thanx.. i really need to know how to do this thanx.

  30. Cecil Ohrt says:

    Great post man Thanks

  31. Gunnar says:

    Hi,

    (forget my last post. It could not be understood, because tags I wrote were replaced :) )
    nice approach.

    It just seems the

    <html><i>italic</i><br/><b>bold</b></html>

    leads to a display that looks to me like

    <html>>i><b>italic<br/>bold</b></i></html>

    What do I do wrong?

    (have added a line, that replaces just as ,
    but that should not have caused the misbehaviour)

    Gunnar

  32. Gunnar: It worked for me – put italic on one line and bold on the next. Is there something missing from your comment that got interpreted as HTML?

  33. Naho says:

    Hi,

    I need to convert html text into formatted text. Unfortunately it’s a big file with over 500 rows. I need to convert the text in each cell. Because of the huge number of rows it’s not possible to copy it manually. Additionally, the text of each cell should convert into one cell again and not in different cells.

    Has anybody an idea? I really don’t know, how I can solve that problem:-(

    Thanks for all ideas in advance!

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

You must be logged in to post a comment.

Here's how to update your reports of company and nearly any web data: