Extract data from HTML

[dsmt05]

We receive many HTML, up to 500, files each day from the field and we need to be able to extract this data to analyze it in Excel. I have tried doing this in the Data - From Web option is Excel 2016 without luck. Thoughts and advise? I have examples of the data if you need to look at it.

 

[Fennek]

Hi,

after saving the plain html to a file, the following code extracts the content between and

Sub iRegex()
sFile = "c:\temp\html.txt"
With CreateObject("scripting.filesystemobject")
    fen = .opentextfile(sFile).readall
    Debug.Print Len(fen)
End With

With CreateObject("vbscript.regexp")
   .Global = True
   .MultiLine = True
   .Pattern = "<p.* p"
     Set RR = .Execute(fen)
   Debug.Print RR.Count
   For i = 0 To RR.Count - 1
        Cells(i + 1, 1) = RR(i)
   Next i
End With
End Sub

I deleted some parts of my code, so you might have to debug it.
If you read directly the content of a website to the variable "Fen", it should work as well.

regards

without code-tag


Sub iRegex()
sFile = "c:\temp\html.txt"
With CreateObject("scripting.filesystemobject")
fen = .opentextfile(sFile).readall
Debug.Print Len(fen)
End With

With CreateObject("vbscript.regexp")
.Global = True
.MultiLine = True
.Pattern = " Set RR = .Execute(fen)
Debug.Print RR.Count
For i = 0 To RR.Count - 1
Cells(i + 1, 1) = RR(i)
Next i
End With

</p.*>

 

[John_w]

You could use HTMLDocument and related classes to parse the HTML and extract the data. Search for VBA IE automation or createDocumentFromUrl for example code.