CODEWITHSUNDEEP
htmlvbams-access
Ann Sanderson
Ann Sanderson

Reputation: 201

Stripping HTML From A String

I've tried a number of things but nothing seems to be working properly. I have an Access DB and am writing code in VBA. I have a string of HTML source code that I am interested in stripping all of the HTML code and Tags out of so that I just have plain text string with no html or tags left. What is the best way to do this?

Thanks

Upvotes: 5

Views: 15324

Answers (6)

Francois Muller
Francois Muller

Reputation: 566

I found a really simple solutions to this. I currently run an access database and use excel forms to update the system due to system restrictions and shared drive privileges. when I call the data from Access I use: Plaintext(YourStringHere) this will remove all html parts and only leave the text.

hope this works.

Upvotes: 0

Bob Kennedy
Bob Kennedy

Reputation: 1

An improvement over one of the above... It finds quotes and line feeds and replaces them with the non-HTML equivalents. Also, the original function had a problem with embedded UNC references (ie: <\server\share\folder\file.ext>). It would remove the entire UNC string due to < at the beginning and > at the end. This function fixes that so the UNC gets inserted into the string correctly:

Function StripHTML(strString As String) As String
 Dim RegEx As Object
 Set RegEx = CreateObject("vbscript.regexp")

 Dim sInput As String
 Dim sOut As String
 sInput = Replace(strString, "<\\", "\\")

 With RegEx
   .Global = True
   .IgnoreCase = True
   .MultiLine = True
.Pattern = "<[^>]+>" 'Regular Expression for HTML Tags.
 End With

 sOut = RegEx.Replace(sInput, "")
 StripHTML = Replace(Replace(Replace(sOut, "&nbsp;", vbCrLf, 1, -    1), "&quot;", "'", 1, -1), "\\", "<\\", 1, -1)
 Set RegEx = Nothing
End Function

Upvotes: 0

Zev Spitz
Zev Spitz

Reputation: 15327

Using early binding: 

Public Function GetText(inputHtml As String) As String
With New HTMLDocument
    .Open
    .write "<p>foo <i>bar</i> <u class='farp'>argle </zzzz> hello </p>"
    .Close
   StripHtml = .body.outerText
End With
End Function

Upvotes: 0

SWa
SWa

Reputation: 4363

It depends how complex the html structure is and how much data you're wanting out of it.

Depending on the complexity you might get away with regular expressions, but for complex markup trying to parse data from html with regex is like trying to eat soup with a fork.

You can use the htmFile object to turn the flat file into objects that you can interact with, for example:

Function ParseATable(url As String) As Variant 

    Dim htm As Object, table As Object 
    Dim data() As String, x As Long, y As Long 
    Set htm = CreateObject("HTMLfile") 
    With CreateObject("MSXML2.XMLHTTP") 
        .Open "GET", url, False 
        .send 
        htm.body.innerhtml = .responsetext 
    End With 

    With htm 
        Set table = .getelementsbytagname("table")(0) 
        Redim data(1 To table.Rows.Length, 1 To 10) 
        For x = 0 To table.Rows.Length - 1 
            For y = 0 To table.Rows(x).Cells.Length - 1 
                data(x + 1, y + 1) = table.Rows(x).Cells(y).InnerText 
            Next y 
        Next x 

        ParseATable = data 

    End With 
End Function

Upvotes: 3

Alex K.
Alex K.

Reputation: 175796

One way that's as resilient as possible to bad markup;

with createobject("htmlfile")
    .open
    .write "<p>foo <i>bar</i> <u class='farp'>argle </zzzz> hello </p>"
    .close
    msgbox "text=" & .body.outerText
end with

Upvotes: 7

Lior
Lior

Reputation: 6051

    Function StripHTML(cell As Range) As String  
 Dim RegEx As Object  
 Set RegEx = CreateObject("vbscript.regexp")  

 Dim sInput As String  
 Dim sOut As String  
 sInput = cell.Text  

 With RegEx  
   .Global = True  
   .IgnoreCase = True  
   .MultiLine = True  
.Pattern = "<[^>]+>" 'Regular Expression for HTML Tags.  
 End With  

 sOut = RegEx.Replace(sInput, "")  
 StripHTML = sOut  
 Set RegEx = Nothing  
End Function

This might help you, Good luck.

Upvotes: 6

Related Questions