Writing macro in Excel to remove HTML TAG

Question

I am trying to write to a macro which remove html tags from excel data. I just want search for <*> this pattern and replace them with blank. Also need to remove special characters like '“'and tags like if(typeof(dstb)!= "undefined"){ dstb();}.

Code I have written till now requires me to hardcode files name in macro , which I do not want.

code :

 Sub UnescapeCharacters()
 sheetname = "2011 Publications" 'file name goes here

Dim sheet As Worksheet
Set sheet = Me.Worksheets(sheetname)

For Row = 1 To sheet.UsedRange.Rows.Count
    For Column = 1 To sheet.UsedRange.Columns.Count
        Dim cell As Range
        Set cell = sheet.Cells(Row, Column)


        ReplaceCharacter cell, "&quot;", """" 
        ReplaceCharacter cell, "&#44;", ""
        ReplaceCharacter cell, "&nbsp;", ""
        ReplaceCharacter cell, "&bull;", ""

        ReplaceCharacter cell, "</ul>", ""
        ReplaceCharacter cell, "<ul>", ""

        ReplaceCharacter cell, "<b>", ""
        ReplaceCharacter cell, "</b>", ""

        ReplaceCharacter cell, "<i>", ""
        ReplaceCharacter cell, "</i>", ""

        ReplaceCharacter cell, "</li>", ""
        ReplaceCharacter cell, "<li>", ""

        ReplaceCharacter cell, "</br>", ""
        ReplaceCharacter cell, "<br />", ""

        ReplaceCharacter cell, "</p>", ""
        ReplaceCharacter cell, "<p>", ""

    Next Column
Next Row

End Sub

Sub ReplaceCharacter(ByRef cell As Range, ByVal find As String, ByVal replacement As     String)

Dim result As String
cell.Value = Replace(cell.Text, find, replacement, 1, -1)

End Sub

Can someone please help?

How about tags with attributes, like `
` - And is "It is < 5 and > 3" an HTML tag? — Mike Christensen, Dec 11 '12 at 17:04
`Code I have written till now requires me to hardcode files name in macro , which I do not want.` -> what is it that you do want? To chose file at run-time? A loop through specific filenames? — Scott Holtzman, Dec 11 '12 at 17:10
It should take name of the file on which it is currently running. — apgp88, Dec 11 '12 at 17:12
There is the possibility of using RegExp in VBA which would greatly help though I am not knowledgeable enough in that matter to know if it is possible to only scan for HTML tags and not fall into the pitfall @MikeChristensen described. — ApplePie, Dec 11 '12 at 18:00
http://www.learnexcelmacro.com/2011/12/strip-html-how-to-remove-html-tags-from-a-string-in-vba/ — SeanC, Dec 11 '12 at 19:21
Mentioning regex + HTML inevitably leads us to this answer: http://stackoverflow.com/a/1732454/53614 — barrowc, Dec 11 '12 at 22:48

score 1 · Accepted Answer · answered Dec 12 '12 at 07:48

I prefer doing it in this approach. If the HTML is NOT a real website, you can save the HTML as a file, then IE.navigate that filePath.

   Sub testing()
    Dim IE As Object
    Dim stringWithOutTags As String
    Set IE = CreateObject("InternetExplorer.Application")
        ' HardCode the URL address in
        IE.navigate "http://stackoverflow.com/questions/13824872/writing-macro-in-excel-to-remove-html-code"
        Do While IE.Busy
        Loop
        Do While IE.readyState <> 4
        Loop


        stringWithOutTags = IE.document.DocumentElement.innerText

        IE.Quit
    End Sub

Writing macro in Excel to remove HTML TAG

1 Answers1