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.

39 thoughts on “HTML in Cells II

  1. Stephen Bullen

    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. Juan Pablo Gonzilez

    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 ?

  3. Juan Pablo Gonzilez

    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.

  4. Stephen Bullen

    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…

  5. Jake Marx

    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?

  6. Stephen Bullen

    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?

  7. Randy Harmelink

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

  8. Amogh

    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

  9. Dave Braden

    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.

  10. Dick Kusleika

    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.

  11. Dick Kusleika

    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.

  12. Tushar Mehta

    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.

  13. doco

    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

  14. ritesh

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

  15. Larry

    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

  16. Dick Kusleika

    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

  17. Ben

    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!

  18. Ben

    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.

  19. GR

    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.

  20. Wannes

    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

  21. Dick Kusleika
    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.

  22. Ciprian

    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

  23. tom

    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.

  24. Gunnar

    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

  25. Naho

    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!

  26. Jamie

    Hi – I tried converting this to an arbitrary subroutine:

    Public Sub ConvertHtmlToFormattedExcel(target As Range)
        Dim objData As DataObject
        Dim sHTML As String
        Dim sSelAdd As String
        Dim ws As Worksheet
       
        Set ws = target.Worksheet
       
        'Application.EnableEvents = false
       
        If target.Cells.Count = 1 Then
            Set objData = New DataObject
               
            sHTML = target.Text
               
            sHTML = Replace(sHTML, "", "br{mso-data-placement:same-cell;}")
               
            objData.SetText sHTML
            objData.PutInClipboard
               
            sSelAdd = Selection.Address
            target.Select
            ws.PasteSpecial "Unicode Text"
            ws.Range(sSelAdd).Select
               
        End If
       
        'Application.EnableEvents = True
    End Sub

    but it doesn’t seem to work. The paste just pastes back the original HTML. Any ideas?

  27. Ken

    On multi-line issue:

    Rich text formatted cells in MS Access use DIV tags, but when these field values are sent to Excel using the methodology described above, each DIV tag ends up in its own cell in Excel no matter what I try. I can’t remove the DIV tags because MS Access embeds some styling instructions in the tag (e.g. “align=center”). How can I get the code above to put all this DIV data into one cell in Excel?

  28. Dick Kusleika Post author

    Ken: I can’t figure out a way to fix that short of rebuilding the string. Maybe try replacing the

    <DIV

    with

    <P

    and

    </DIV

    with

    </P

    . I would try it, but I can’t figure out how to get the html out of access.

  29. Amit M

    Guys were you able to solve the problem of putting the parsed html text into one single cell?

  30. smoof

    hello guys, you got me completly lost, please help me

    i have programm that can generate csv lists of my music. i make one csv per column (like artist or year) then i paste them into excel, do a nice page layout which i save as html. so far so good.

    now i made 3119 screenshots of waveforms that i would like to include to the page. i can easily manipulate the script that makes the csv to give them any form or syntax i want, so i went with:

    but when i save the sheet as html my browser shows me this exact same text instead of loading the image in that spot.
    this is what the editor tells me:

    320
    <img src="file:\\W:\wf\Yoetc & Zombeats – Hold Your Breath.jpg"
    alt="waveform" width="200" height="20">
    W:\AAATTTGGG\X-Z\Yoetc
    & Zombeats – Hold Your Breath.mp3

    W:\AAATTTGGG\X-Z\Yoetc & Zombeats – Hold Your
    Breath.mp3

    is there an easy solution and can somebody explain it to me so i understand it? if not i might try to write the script in a way that it generates the whole html “oneshot” but i would miss out on conditional formating, probably never get the layout right, plus it’s a lot of work ;)…

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax