Paste HTML data into Excel with VBA

[hellfire45]

So I tried looking this up online and I found something but what I'm using doesn't work. I am trying to paste invoice data from a .html file to excel so I can parse it. I found the following program and it sort of works:

Sub test21()'Thanks to http://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba
Dim FileNum As Integer, i As Integer
Dim DataLine As String




FileNum = FreeFile()
Open "C:\Users\jjxxx.html" For Input As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum"]#FileNum[/URL] 
i = 1 ' This is where you want to set your `RowCount`.
While Not EOF(FileNum)
    Line Input [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum"]#FileNum[/URL] , DataLine ' read in data 1 line at a time
    Range("IV" & i).Value = DataLine
    i = i + 1
Wend
End Sub

Whereas I expected to get something a long the lines of XML tags with <> <> around everything, what the program above pasted into Excel was like 8 pages of just formatting gibberish. Does anybody understand why this is happening? The actual HTML file shows several tables with invoicing data with product names, prices, etc...So I need that info and not all this background colors and ":Transparent;vertical-align:top;text-align:left;direction:ltr;" etc...

Thanks guys!

 

[alansidman]

Have you tried importing the web information to Power Query? Parsing the data there and then Loading into your Excel Worksheet. Power Query is found on the Ribbon on the Data Tab.