Paste HTML data into Excel with VBA

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
460
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:


Code:
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!
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
7,394
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Forum statistics

Threads
1,187,068
Messages
5,961,395
Members
438,539
Latest member
muimonk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top