none
Convert contents of a formatted excel cell to HTML format RRS feed

  • Question

  • Hi All,

    Background: I am writing a script that uploads some test cases written in excel into Quality Center. For those who are familiar with the QC Excel Addin, this script would do a pretty similar job except for reducing the steps involved. I'm using Office 2007 running on Win 7 Pro.

    Need: I have a situation where the contents of a formatted excel cell need to be converted to HTML tags representing the same formattin in the excel cell. For example a cell containg the text: "Verify if help topics in Macro section are hyperlinked" should be converted to

    <html><b>Verify</b> if help topics in <u>Macro section</u> are <i>hyperlinked</i></html> 

    Question: Is there an inbuilt function in Excel/VBAn accomplish this? Or is a macro required? Any other ideas to accomplish this?

    Note: Whatever used for converting (an inbuilt function or a macro) should support new line characters and colors.

    Any help or redirection to solutions is appreciated.

    Thanks, John.


    --Thanks, John Jacob Tharakan

    Thursday, May 31, 2012 5:33 AM

Answers

  • Here is the function I wrote. This handles the conversion character by character.

    Function fnConvert2HTML(myCell As Range) As String Dim bldTagOn, itlTagOn, ulnTagOn, colTagOn As Boolean Dim i, chrCount As Integer Dim chrCol, chrLastCol, htmlTxt As String bldTagOn = False itlTagOn = False ulnTagOn = False colTagOn = False chrCol = "NONE" htmlTxt = "<html>" chrCount = myCell.Characters.Count For i = 1 To chrCount With myCell.Characters(i, 1) If (.Font.Color) Then chrCol = fnGetCol(.Font.Color) If Not colTagOn Then htmlTxt = htmlTxt & "<font color=#" & chrCol & ">" colTagOn = True Else If chrCol <> chrLastCol Then htmlTxt = htmlTxt & "</font><font color=#" & chrCol & ">" End If Else chrCol = "NONE" If colTagOn Then htmlTxt = htmlTxt & "</font>" colTagOn = False End If End If chrLastCol = chrCol If .Font.Bold = True Then If Not bldTagOn Then htmlTxt = htmlTxt & "<b>" bldTagOn = True End If Else If bldTagOn Then htmlTxt = htmlTxt & "</b>" bldTagOn = False End If End If If .Font.Italic = True Then If Not itlTagOn Then htmlTxt = htmlTxt & "<i>" itlTagOn = True End If Else If itlTagOn Then htmlTxt = htmlTxt & "</i>" itlTagOn = False End If End If If .Font.Underline > 0 Then If Not ulnTagOn Then htmlTxt = htmlTxt & "<u>" ulnTagOn = True End If Else If ulnTagOn Then htmlTxt = htmlTxt & "</u>" ulnTagOn = False End If End If If (Asc(.Text) = 10) Then htmlTxt = htmlTxt & "<br>" Else htmlTxt = htmlTxt & .Text End If End With Next If colTagOn Then htmlTxt = htmlTxt & "</font>" colTagOn = False End If If bldTagOn Then htmlTxt = htmlTxt & "</b>" bldTagOn = False End If If itlTagOn Then htmlTxt = htmlTxt & "</i>" itlTagOn = False End If If ulnTagOn Then htmlTxt = htmlTxt & "</u>" ulnTagOn = False End If htmlTxt = htmlTxt & "</html>" fnConvert2HTML = htmlTxt End Function

    Function fnGetCol(strCol As String) As String Dim rVal, gVal, bVal As String strCol = Right("000000" & Hex(strCol), 6) bVal = Left(strCol, 2) gVal = Mid(strCol, 3, 2) rVal = Right(strCol, 2) fnGetCol = rVal & gVal & bVal End Function



    --Thanks, John Jacob Tharakan



    Tuesday, June 5, 2012 7:28 PM

All replies

  • I think macro required and long one.Because if cell can be formatted in 10 way then all 10 need to be checked and converted to html.Can you tell what type of formatting you need to change ?

    Or can try pasting in some HTML editor.The editor must convert to appropriate html code on the basis of source formatting.

    Friday, June 1, 2012 7:07 AM
    Answerer
  • Hi. Pasting to HTML editor and looking at its code works. But that is not feasible for the scenario I have at hand for because it will create dependency on an external program and I guess it would be quite a big task to automate such an action from VBA. Thanks for the tip though.

    If it requires a macro, a character by character analysis of formatting might be required, right? Is it possible thru VBA?


    --Thanks, John Jacob Tharakan

    Friday, June 1, 2012 8:33 AM
  • In excel lot of formatting can be applied in Cell but limited set of formatting is to be cheked  if formatted charecter by charecter.

    Excel allows UnderLine/Bold/Italic/Font name/font size and color formatting on a charecter.

    The macro will have to check each char for the 6 formatting.

    I will shortly provide you a macro which will take a cell and give the output in beside cell.

    Friday, June 1, 2012 9:26 AM
    Answerer
  • Appreciate it.

    I may be able to use the code and modify it to accomodate Font Background, Carriage Returns (line breaks), Indenting (tab characters) and bulletting as well.


    --Thanks, John Jacob Tharakan

    Friday, June 1, 2012 1:17 PM
  • I have given a function which you can utilize.

    Function TextToHtml(Rng As Range)
        Dim sResult As String
        
        Dim sFront As String
        Dim sBehind As String
        
        Dim lLoop As Long
        
        Dim lPrevPos As Long
        Dim lNextPos As Long
        
        'Only one cell is one time
        If Rng.Count > 1 Then Exit Function
        
        lPrevPos = 0
        lNextPos = 0
        
        Do
        
            lNextPos = InStr(lPrevPos + 1, Rng.Value, " ")
            
            With Rng.Characters(lPrevPos + 1, lNextPos - lPrevPos - 1).Font
                If .Italic Then
                    sFront = "<i>" & sFront
                    sBehind = sBehind & "</i>"
                End If
                
                If .Underline <> xlUnderlineStyleNone Then
                    sFront = "<u>" & sFront
                    sBehind = sBehind & "</u>"
                End If
                
                If .Bold Then
                    sFront = "<b>" & sFront
                    sBehind = sBehind & "</b>"
                End If
                
                If .Superscript Then
                    sFront = "<sup>" & sFront
                    sBehind = sBehind & "</sup>"
                End If
                
                If .Subscript Then
                    sFront = "<sub>" & sFront
                    sBehind = sBehind & "</sub>"
                End If
                
                sFront = "<Font size=" & .Size & _
                " family=" & Chr(34) & .Name & Chr(34) & ">" & sFront
                sBehind = sBehind & "</font>"
                
            End With
            
            sResult = sResult & sFront & Rng.Characters(lPrevPos + 1, lNextPos - lPrevPos - 1).Text _
            & " " & sBehind
            
            sFront = ""
            sBehind = ""
            lPrevPos = lNextPos
        
        Loop While lNextPos
        
        TextToHtml = sResult
        
    End Function
    I am not very comfortable with HTML and tried best to accomodate the needs.Anyway I strongly feel that you will get better inputs from VB.Net forum or JavaScript forum.
    Monday, June 4, 2012 8:13 AM
    Answerer
  • Thanks Learning and Learning. I got some cues from where I can start and make one of my own. I'll post the script when I'm done.

    BTW - I just found that only bolding, italics, underline , font color and break character are the only items that need to be considered. These are the formattings supported in both excel cell and QC. For eg: Superscript, Subscript, Font size will be supported in excel cell but not in QC. Font Background color is supported in QC but not supported in excel cell.


    --Thanks, John Jacob Tharakan



    Tuesday, June 5, 2012 3:26 PM
  • Here is the function I wrote. This handles the conversion character by character.

    Function fnConvert2HTML(myCell As Range) As String Dim bldTagOn, itlTagOn, ulnTagOn, colTagOn As Boolean Dim i, chrCount As Integer Dim chrCol, chrLastCol, htmlTxt As String bldTagOn = False itlTagOn = False ulnTagOn = False colTagOn = False chrCol = "NONE" htmlTxt = "<html>" chrCount = myCell.Characters.Count For i = 1 To chrCount With myCell.Characters(i, 1) If (.Font.Color) Then chrCol = fnGetCol(.Font.Color) If Not colTagOn Then htmlTxt = htmlTxt & "<font color=#" & chrCol & ">" colTagOn = True Else If chrCol <> chrLastCol Then htmlTxt = htmlTxt & "</font><font color=#" & chrCol & ">" End If Else chrCol = "NONE" If colTagOn Then htmlTxt = htmlTxt & "</font>" colTagOn = False End If End If chrLastCol = chrCol If .Font.Bold = True Then If Not bldTagOn Then htmlTxt = htmlTxt & "<b>" bldTagOn = True End If Else If bldTagOn Then htmlTxt = htmlTxt & "</b>" bldTagOn = False End If End If If .Font.Italic = True Then If Not itlTagOn Then htmlTxt = htmlTxt & "<i>" itlTagOn = True End If Else If itlTagOn Then htmlTxt = htmlTxt & "</i>" itlTagOn = False End If End If If .Font.Underline > 0 Then If Not ulnTagOn Then htmlTxt = htmlTxt & "<u>" ulnTagOn = True End If Else If ulnTagOn Then htmlTxt = htmlTxt & "</u>" ulnTagOn = False End If End If If (Asc(.Text) = 10) Then htmlTxt = htmlTxt & "<br>" Else htmlTxt = htmlTxt & .Text End If End With Next If colTagOn Then htmlTxt = htmlTxt & "</font>" colTagOn = False End If If bldTagOn Then htmlTxt = htmlTxt & "</b>" bldTagOn = False End If If itlTagOn Then htmlTxt = htmlTxt & "</i>" itlTagOn = False End If If ulnTagOn Then htmlTxt = htmlTxt & "</u>" ulnTagOn = False End If htmlTxt = htmlTxt & "</html>" fnConvert2HTML = htmlTxt End Function

    Function fnGetCol(strCol As String) As String Dim rVal, gVal, bVal As String strCol = Right("000000" & Hex(strCol), 6) bVal = Left(strCol, 2) gVal = Mid(strCol, 3, 2) rVal = Right(strCol, 2) fnGetCol = rVal & gVal & bVal End Function



    --Thanks, John Jacob Tharakan



    Tuesday, June 5, 2012 7:28 PM
  • Nice.I avoided the character wise formatting because ,it will take more time and resource and generally we do not format individual char,we format words.

    All the best.

    Wednesday, June 6, 2012 10:21 AM
    Answerer
  • Thanks a lot John Jacob Tharakan . Works like a charm.... Really made my work easy.. :)
    Tuesday, August 6, 2013 5:52 PM
  • Hi,

    Please can you give me the code to convert text to HTML with UTF-8 encoding?

    The above code works good to convert to HTML without encoding

    Tuesday, April 7, 2015 11:38 AM
  • Hi,

    I am a newbie, so how do I call your function from a macro, so it converts and replaces the content of the active cell?

    Monday, May 23, 2016 9:20 AM
  • thanks for your contribute

    Friday, November 17, 2017 4:04 AM
  • Hi,

    How to convert HTML source code into HTML format. How to run the above code.

    Please help me.


    • Edited by Vivek Sapate Saturday, December 29, 2018 4:49 PM
    Saturday, December 29, 2018 4:48 PM