Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert HTML into plain text

    The data for the table comes from an OCDB database out of our control.
    Need code to strip the HTML code from the field Course so that in our query it will appear as plain text.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    PA,

    Could you post a sample of what needs to be stripped?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    You can do it using one line of VBA code with the Web Browser Control.

    To get the plain text from a web browser control you can use:

    Code:
    .Document.Body.Innertext
    What version of Access are you using?
    Last edited by HiTechCoach; 2012-02-21 at 11:45. Reason: Add example
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  4. #4
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sample data that is in the field

    <p>Twist, fold and loop wire to create an Alexander Cader inspired animal out of clay and wire. Explore various techniques for bending and manipulating wire while having fun! Registration in a Camp Cavs Summer Camp course requires completion of the Camp CAVS Summer Camp Release and Waiver of Liability. This signed form must be turned in with registration form. If registering online, you need to print, sign, and fax to (318) 678-6406. Registration is course is not complete until the waiver has been submitted! <a target="_blank" href="http://www.bpcc.edu/workforcedevelopment/documents/campcavswaiver.pdf"><b><font color="#921f30">Click here for Camp CAVS Summer Camp Release and Waiver of Liability</font></b></a></p>

  5. #5
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What version of Access are you using? 2010

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    PA,

    In case HTC's solution doesn't work {I'm a bit leery since the definition of the control is to display a webpage in a form. But I've been wrong before! } here is a UDF that will accept a string and return it w/o the HTML. It does assume that there are NOT any HTML errors like unmatched "<>".

    Below is a sample of running the function from the VBA Immediate window
    ?striphtml("<p>Testing Testing<Href=''> This is a test</p>")
    Testing Testing This is a test


    Here's the code:
    Code:
    Public Function StripHTML(zDataIn As String) As String
    
      Dim iStart As Integer
      Dim iEnd   As Integer
      Dim iLen   As Integer
    
      Do While InStr(zDataIn, "<") > 0
        iStart = InStr(zDataIn, "<")
        iEnd = InStr(iStart, zDataIn, ">")
        iLen = Len(zDataIn)
        If iStart = 1 Then
          zDataIn = Right(zDataIn, iLen - iEnd)
        Else
          If iLen = iEnd Then
            zDataIn = Left(zDataIn, iStart - 1)
          Else
            zDataIn = Mid(zDataIn, 1, iStart - 1) & _
                      Right(zDataIn, iLen - iEnd)
          End If
        End If
      Loop
      
      StripHTML = zDataIn
      
    End Function
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    When the text contains stuff like this:

    <a target="_blank" href="http://www.bpcc.edu/workforcedevelopment/documents/campcavswaiver.pdf"><b><font color="#921f30">Click here for Camp CAVS Summer Camp Release and Waiver of Liability</font></b></a>

    The web address is actually part of the HTML and so will be removed by any method (included RG's function) that strips out the HTML. Are you happy for that to be lost?
    Regards
    John



  8. #8
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    intersting that it works really well.
    <a target="_blank" href="http://www.bpcc.edu/workforcedevelopment/documents/campcavswaiver.pdf"><b><font color="#921f30">Click here for Camp CAVS Summer Camp Release and Waiver of Liability</font></b></a>
    however, the text "Click here for Camp CAVS Summer Camp Release and Waiver of Liability" shows up. maybe they need to list the website as actaul text so someone could go to the site. The purpose here is to use the informationin printed text.

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    PA,

    It shows up because is not between <> delimiters. You could modify the VBA to first search for <a & </a> tags and get rid of everything in between then go back and do what has already been coded. A bit trickier but doable.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    RG,

    I would update your code to convert tags like <br> to the equivalent in plan text. This would also help preserve some of the formatting.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  11. #11
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    on eof those embarissing moments . I had the code working in a teat database. however, i cannot get it work onthe client machine. I created the module in VB.
    Public Function StripHTML(zDataIn As String) As String changing zDataIn to the field name [couDesc] which is what they call it. Then in a query I cal the function by using expre1: StripHTML([couDesc]) and it does not work. Maybe it is just friday. any thoughts?

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    PA,

    Change it back to zDataIn. This is a local variable that receives the value you pass it in the call {StripHTML([couDesc])}. Then the function passes back to the query the stripped text. This allows the function to work with any value passed not just a single field name.

    Also make sure the function is placed in a Module not a DB object.
    Last edited by RetiredGeek; 2012-02-24 at 13:50.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    so thankful for your previous help..
    Another issue with &nbsp; everywhere..
    STUDENTS&nbsp;MUST&nbsp;BRING&nbsp;SEWING&nbsp;MAC HINE. Textbook and/or Supplies Required.Click here for Book/Supply List&nbsp;

    paul

  14. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Paul,

    It's just a one line addition.
    Code:
    Public Function StripHTML(zDataIn As String) As String
    
      Dim iStart As Integer
      Dim iEnd   As Integer
      Dim iLen   As Integer
    
      Do While InStr(zDataIn, "<") > 0
        iStart = InStr(zDataIn, "<")
        iEnd = InStr(iStart, zDataIn, ">")
        iLen = Len(zDataIn)
        If iStart = 1 Then
          zDataIn = Right(zDataIn, iLen - iEnd)
        Else
          If iLen = iEnd Then
            zDataIn = Left(zDataIn, iStart - 1)
          Else
            zDataIn = Mid(zDataIn, 1, iStart - 1) & _
                      Right(zDataIn, iLen - iEnd)
          End If
        End If
      Loop
      
      zDataIn = Replace(zDataIn,"&nbsp;","")
    
      StripHTML = zDataIn
      
    End Function
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #15
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    &nbsp; really needs to be replaced with a space.

    Try:

    Code:
    zDataIn = Replace(zDataIn,"&nbsp;"," ")
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •