Untitled

mail@pastecode.io avatar
unknown
plain_text
2 years ago
705 B
1
Indexable
Never
Sub RefreshAndRemoveHTMTags()

StartTime = Now

Dim cell As Range

''regex to get rid of html characters
With CreateObject("vbscript.regexp")
    .Pattern = "\<.*?\>"
    .Global = True
    
Dim lst As ListObject

ThisWorkbook.Connections("Query - qJobAnalysis").Refresh

Set lst = Application.Range("qJobAnalysis").ListObject
Set rngToLoop = lst.ListColumns("BodyHtml").DataBodyRange

For Each cell In rngToLoop
        cell.Value = .Replace(cell.Value, "")
Next cell
End With

lst.ListColumns("BodyHtml").DataBodyRange.WrapText = False

 'time taken
 TimeTaken = Now - StartTime
 msg = "Done. Data scrapped in: " & Format(TimeTaken, "hh:mm:ss") & "."
 MsgBox msg

End Sub