I trying to remove the html tags using the code below but it does not do anything. I have tried running it again but to no use. Could someone please advise what could be the issue. Attaching a snapshot of the file with html tags. For info, the data comes from the MS Access and the MS Access links to Sharepoint lists.
Sub Import_AccessData()
Dim strtKeyMsgRange As Range
Dim KeyMsgRange As Range
Dim KeyMsgRangeCell As Range
Dim endKeyMsgRangeCell As Range
Set strtKeyMsgRange = Range("B2")
Set endKeyMsgRange = Range("AC13")
Set KeyMsgRange = Range(strtKeyMsgRange, endKeyMsgRange)
For Each KeyMsgRangeCell In KeyMsgRange
a = StripHTML(KeyMsgRangeCell)
KeyMsgRangeCell.Value = a
Next KeyMsgRangeCell
End Sub
Public 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.Value
With RegEx
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = "<[^>]+>" 'Regular Expression for HTML Tags.
.Pattern = " "
.Pattern = "&"
End With
sOut = RegEx.Replace(sInput, "")
StripHTML = sOut
Set RegEx = Nothing
End Function
