HTML Text with tags to formatted text in an Excel cell

Question

Is there a way to take HTML and import it to excel so that it is formatted as rich text  preferably by using VBA   Basically  when I paste to an Excel cell  I m looking to turn this    lt html gt  lt p gt This is a test  Will this text be  lt b gt bold lt  b gt  or  lt i gt italic lt  i gt  lt  p gt  lt  html gt    into this   This is a test  Will this text be bold or italic

User · Answer

Nice  Very slick   I was disappointed that Excel doesn t let us paste to a merged cell and also pastes results containing a break into successive rows below the  target  cell though  as that meant it simply doesn t work for me  I tried a few tweaks  unmerge remerge  etc   but then Excel dropped anything below a break  so that was a dead end   Ultimately  I came up with a routine that ll handle simple tags and not use the  native  Unicode converter that is causing the issue with merged fields   Hope others find this useful   Public Sub AddHTMLFormattedText rngA As Range  strHTML As String  Optional blnShowBadHTMLWarning As Boolean   False        Adds converts text formatted with basic HTML tags to formatted text in an Excel cell       NOTE  Font Sizes not handled perfectly per HTML standard  but I find this method more useful       Dim strActualText As String  intSrcPos As Integer  intDestPos As Integer  intDestSrcEquiv   As Integer     Dim varyTags As Variant  varTag As Variant  varEndTag As Variant  blnTagMatch As Boolean     Dim intCtr As Integer     Dim intStartPos As Integer  intEndPos As Integer  intActualStartPos As Integer  intActualEndPos As Integer     Dim intFontSizeStartPos As Integer  intFontSizeEndPos As Integer  intFontSize As Integer      varyTags   Array   lt b gt      lt  b gt      lt i gt      lt  i gt      lt u gt      lt  u gt      lt sub gt      lt  sub gt      lt sup gt      lt  sup gt           Remove unhandled unneeded tags  convert  lt br gt  and  lt p gt  tags to line feeds     strHTML   Trim strHTML      strHTML   Replace strHTML    lt html gt            strHTML   Replace strHTML    lt  html gt            strHTML   Replace strHTML    lt p gt            While LCase Right  strHTML  4       lt  p gt   Or LCase Right  strHTML  4       lt br gt           strHTML   Left  strHTML  Len strHTML  - 4          strHTML   Trim strHTML      Wend     strHTML   Replace strHTML    lt br gt    vbLf      strHTML   Replace strHTML    lt  p gt    vbLf       strHTML   Trim strHTML       ReDim intDestSrcEquiv 1 To Len strHTML       strActualText          intSrcPos   1     intDestPos   1     Do While intSrcPos  lt   Len strHTML          blnTagMatch   False         For Each varTag In varyTags             If LCase Mid  strHTML  intSrcPos  Len varTag      varTag Then                 blnTagMatch   True                 intSrcPos   intSrcPos   Len varTag                  If intSrcPos  gt  Len strHTML  Then Exit Do                 Exit For             End If         Next         If blnTagMatch   False Then             varTag     lt font size              If LCase Mid  strHTML  intSrcPos  Len varTag      varTag Then                 blnTagMatch   True                 intEndPos   InStr intSrcPos  strHTML    gt                    intSrcPos   intEndPos   1                 If intSrcPos  gt  Len strHTML  Then Exit Do             Else                 varTag     lt  font gt                   If LCase Mid  strHTML  intSrcPos  Len varTag      varTag Then                     blnTagMatch   True                     intSrcPos   intSrcPos   Len varTag                      If intSrcPos  gt  Len strHTML  Then Exit Do                 End If             End If         End If         If blnTagMatch   False Then             strActualText   strActualText  amp  Mid  strHTML  intSrcPos  1              intDestSrcEquiv intSrcPos    intDestPos             intDestPos   intDestPos   1             intSrcPos   intSrcPos   1         End If     Loop        Clear any bold underline italic superscript subscript formatting from cell     rngA Font Bold   False     rngA Font Underline   False     rngA Font Italic   False     rngA Font Subscript   False     rngA Font Superscript   False      rngA Value   strActualText        Now start applying Formats         Start with Font Size first     intSrcPos   1     intDestPos   1     Do While intSrcPos  lt   Len strHTML          varTag     lt font size          If LCase Mid  strHTML  intSrcPos  Len varTag      varTag Then             intFontSizeStartPos   InStr intSrcPos  strHTML          1             intFontSizeEndPos   InStr intFontSizeStartPos  strHTML        - 1             If intFontSizeEndPos - intFontSizeStartPos  lt   3 And intFontSizeEndPos - intFontSizeStartPos  gt  0 Then                 Debug Print Mid  strHTML  intFontSizeStartPos  intFontSizeEndPos - intFontSizeStartPos   1                  If Mid  strHTML  intFontSizeStartPos  1        Then                     intFontSizeStartPos   intFontSizeStartPos   1                     intFontSize   11   2   Mid  strHTML  intFontSizeStartPos  intFontSizeEndPos - intFontSizeStartPos   1                  ElseIf Mid  strHTML  intFontSizeStartPos  1     -  Then                     intFontSizeStartPos   intFontSizeStartPos   1                     intFontSize   11 - 2   Mid  strHTML  intFontSizeStartPos  intFontSizeEndPos - intFontSizeStartPos   1                  Else                     intFontSize   Mid  strHTML  intFontSizeStartPos  intFontSizeEndPos - intFontSizeStartPos   1                  End If             Else                   Error                  GoTo HTML Err             End If             intEndPos   InStr intSrcPos  strHTML    gt                intSrcPos   intEndPos   1             intStartPos   intSrcPos             If intSrcPos  gt  Len strHTML  Then Exit Do             While intDestSrcEquiv intStartPos    0 And intStartPos  lt  Len strHTML                  intStartPos   intStartPos   1             Wend             If intStartPos  gt   Len strHTML  Then GoTo HTML Err   HTML is bad              varEndTag     lt  font gt               intEndPos   InStr intSrcPos  LCase strHTML   varEndTag              If intEndPos   0 Then GoTo HTML Err   HTML is bad              While intDestSrcEquiv intEndPos    0 And intEndPos  gt  intSrcPos                 intEndPos   intEndPos - 1             Wend             If intEndPos  gt  intSrcPos Then                 intActualStartPos   intDestSrcEquiv intStartPos                  intActualEndPos   intDestSrcEquiv intEndPos                  rngA Characters intActualStartPos  intActualEndPos - intActualStartPos   1                         Font Size   intFontSize             End If         End If         intSrcPos   intSrcPos   1     Loop       Now do remaining tags     intSrcPos   1     intDestPos   1     Do While intSrcPos  lt   Len strHTML          If intDestSrcEquiv intSrcPos    0 Then               This must be a Tag              For intCtr   0 To UBound varyTags  Step 2                 varTag   varyTags intCtr                  intStartPos   intSrcPos   Len varTag                  While intDestSrcEquiv intStartPos    0 And intStartPos  lt  Len strHTML                      intStartPos   intStartPos   1                 Wend                 If intStartPos  gt   Len strHTML  Then GoTo HTML Err   HTML is bad                  If LCase Mid  strHTML  intSrcPos  Len varTag      varTag Then                     varEndTag   varyTags intCtr   1                      intEndPos   InStr intSrcPos  LCase strHTML   varEndTag                      If intEndPos   0 Then GoTo HTML Err   HTML is bad                      While intDestSrcEquiv intEndPos    0 And intEndPos  gt  intSrcPos                         intEndPos   intEndPos - 1                     Wend                     If intEndPos  gt  intSrcPos Then                         intActualStartPos   intDestSrcEquiv intStartPos                          intActualEndPos   intDestSrcEquiv intEndPos                          With rngA Characters intActualStartPos  intActualEndPos - intActualStartPos   1  Font                             If varTag     lt b gt   Then                                  Bold   True                             ElseIf varTag     lt i gt   Then                                  Italic   True                             ElseIf varTag     lt u gt   Then                                  Underline   True                             ElseIf varTag     lt sup gt   Then                                  Superscript   True                             ElseIf varTag     lt sub gt   Then                                  Subscript   True                             End If                         End With                     End If                     intSrcPos   intSrcPos   Len varTag  - 1                     Exit For                 End If             Next         End If         intSrcPos   intSrcPos   1         intDestPos   intDestPos   1     Loop Exit Sub      Exit Sub HTML Err        There was an error with the Tags  Show warning if requested      If blnShowBadHTMLWarning Then         MsgBox  There was an error with the Tags in the HTML file  Could not apply formatting       End If End Sub   Note this doesn t care about tag nesting  instead only requiring a close tag for every open tag  and assuming the close tag nearest the opening tag applies to the opening tag  Properly nested tags will work fine  while improperly nested tags will not be rejected and may or may not work

User · Answer

To put HTML Word in an Excel Shape and locate it on an Excel Cell    Write my HTML to a temp file  Open temp file via Word Interop  Copy it from Word to clipboard  Open Excel via Interop  Set and Select a cell to a range  PasteSpecial as a  Microsoft Word Document Object  Adjust the excel row to the Shape height    In this way  even HTML with tables and other stuff does not get split over multiple cells       private void btnPutHTMLIntoExcelShape Click object sender  EventArgs e                var fFile   new FileInfo   C  Temp temp html            StreamWriter SW   fFile CreateText            SW Write hecNote DocumentHtml           SW Close             Word Application wrdApplication          Word Document wrdDocument          wrdApplication   new Word Application            wrdApplication Visible   true           wrdDocument   wrdApplication Documents Add   C  Temp temp html            wrdDocument ActiveWindow Selection WholeStory            wrdDocument ActiveWindow Selection Copy             Excel Application excApplication          Excel Workbook excWorkbook          Excel  Worksheet excWorksheet          Excel Range excRange   null           excApplication   new Excel Application            excApplication Visible   true          excWorkbook   excApplication Workbooks Add Type Missing           excWorksheet    Excel Worksheet excWorkbook Worksheets get Item 1           excWorksheet Name    Work           excRange   excWorksheet get Range  A1            excRange Select             excWorksheet PasteSpecial  Microsoft Word Document Object             Excel Shape O   excWorksheet Shapes Item 1            this Text      O Height  x  O Width              Excel Range excWorksheet Rows 1  Type Missing   RowHeight   O Height

User · Answer

Yes it is possible    In fact let Internet Explorer do the dirty work for you    TRIED AND TESTED MY ASSUMPTIONS  I am assuming that the html text is in Cell A1 of Sheet1  You can also use a variable instead  If you have a column full of html values  then simply put the below code in a loop  CODE  See NOTE at the end  Sub Sample       Dim Ie As Object          Set Ie   CreateObject  quot InternetExplorer Application quot            With Ie          Visible   False                   Navigate  quot about blank quot                    document body InnerHTML   Sheets  quot Sheet1 quot   Range  quot A1 quot   Value                   document body createtextrange execCommand  quot Copy quot          ActiveSheet Paste Destination  Sheets  quot Sheet1 quot   Range  quot A1 quot                     Quit     End With End Sub  SNAPSHOT  NOTE  Thanks to  tiQu answer below  The above code will work with new IE if you replace  document body createtextrange execCommand  quot Copy quot  with  ExecWB 17  0   ExecWB 12  2 as suggested by him

User · Answer

I know this thread is ancient  but after assigning the innerHTML  ExecWB worked for me    x000D   x000D   ExecWB 17  0 x000D   Select all contents in browser x000D   ExecWB 12  2 x000D   Copy them x000D   x000D   x000D    And then just paste the contents into Excel  Since these methods are prone to runtime errors  but work fine after one or two tries in debug mode  you might have to tell Excel to try again if it runs into an error  I solved this by adding this error handler to the sub  and it works fine    x000D   x000D  Sub ApplyHTML   x000D    On Error GoTo ErrorHandler x000D          x000D    Exit Sub x000D   x000D  ErrorHandler  x000D      Resume  x000D       I e  re-run the line of code that caused the error x000D  Exit Sub x000D        x000D  End Sub x000D   x000D   x000D

User · Answer

If the IE example doesn t work use this one   Anyway this should be faster than starting up an instance of IE   Here is a complete solution based on http   www dailydoseofexcel com archives 2005 02 23 html-in-cells-ii   Note  if your innerHTML is all numbers eg  12345   HTML formatting  dosen t fully work in excel as it treats number differently  but add a character eg  a trailing space at the end eg  12345     amp  nbsp   formats ok   Sub test       Cells 1  1  Value     lt HTML gt 1 lt font color blue gt a lt  font gt    amp                             23 lt font color red gt 4 lt  font gt  lt  HTML gt       Dim rng As Range     Set rng   ActiveSheet Cells 1  1      Worksheet Change rng  ActiveSheet End Sub   Private Sub Worksheet Change ByVal Target As Range  ByVal sht As Worksheet       Dim objData As DataObject   Set a reference to MS Forms 2 0     Dim sHTML As String     Dim sSelAdd As String      Application EnableEvents   False      If Target Cells Count   1 Then              Set objData   New DataObject             sHTML   Target Text             objData SetText sHTML             objData PutInClipboard             Target Select             sht PasteSpecial Format   Unicode Text      End If      Application EnableEvents   True  End Sub

User · Answer

You can copy the HTML code to the clipboard and paste special it back as Unicode text  Excel will render the HTML in the cell     Check out this post http   www dailydoseofexcel com archives 2005 02 23 html-in-cells-ii   The relevant macro code from the post     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       lt html gt   Then          Set objData   New DataObject           sHTML   Target Text           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

User · Answer

I ran into the same error that BornToCode first identified in the comments of the original solution  Being unfamiliar with Excel and VBA it took me a second to figure out how to implement tiQU s solution  So I m posting it as a  For Dummies  solution below   First enable developer mode in Excel  Link Select the Developer Tab   Visual Basic Click View   Code Paste the code below updating the lines that require cell references to be correct  Click the Green Run Arrow or press F5     Sub Sample       Dim Ie As Object     Set Ie   CreateObject  InternetExplorer Application       With Ie          Visible   False          Navigate  about blank           document body InnerHTML   Sheets  Sheet1   Range  I2   Value               update to the cell that contains HTML you want converted          ExecWB 17  0               Select all contents in browser          ExecWB 12  2               Copy them         ActiveSheet Paste Destination  Sheets  Sheet1   Range  J2                 update to cell you want converted HTML pasted in          Quit     End With End Sub

[vba] HTML Text with tags to formatted text in an Excel cell

Examples related to vba

Examples related to excel

Examples related to html-parsing