Kindly help me on how to import html table to excel using VBA without using the query capability of excel.
the html table format goes like this
Please Login or Register to view this content.
Kindly help me on how to import html table to excel using VBA without using the query capability of excel.
the html table format goes like this
Please Login or Register to view this content.
Here's one way:
Please Login or Register to view this content.
Thanks, you're generous as always.
Kyle,
I having trouble extracting the table of the following files.
QUOTE_A.zip
For TIMESALES.html
There is error in this line of the code:
For the QOUTES_A.htmlPlease Login or Register to view this content.
How to separate the multiple tables, imported to a sheet same formatting using queries.
Hi,
by affecting the collection getElementsByTagName("TABLE") to a variable,
you can search in debug mode in the Local variables VBE window the right table.
But notice that Element #1 in a collection from that window is the item zero in VBA …
Got it!!! Thanks Marc
When tables are within a main table, easy way is to directly copy main table !
Code to paste in worksheet module (just amend FILE constant) :
PHP Code:
Sub Demo4QUOTE()
Const FILE$ = "D:\Tests4Noobs\QUOTE_A.html"
If Dir(FILE) = "" Then Beep: Exit Sub
With CreateObject("HTMLFile")
.Write CreateObject("Scripting.FileSystemObject").OpenTextFile(FILE).ReadAll
If .frames.clipboardData.setData("Text", .getElementsByTagName("TABLE")(0).outerhtml) Then
Application.ScreenUpdating = False
Cells(1).CurrentRegion.Clear
Me.Paste [B2]
.frames.clipboardData.clearData "Text"
With [B2].CurrentRegion: .WrapText = False: .Columns("G:H").AutoFit: End With
End If
End With
End Sub
Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
Kyle,
I'm getting an error If I set this to variableI am importing several html files (table) to each of the corresponding sheets.Please Login or Register to view this content.
Change:
To:Please Login or Register to view this content.
Please Login or Register to view this content.
Thanks Kyle,
This code works only for the specific sheet.
Question, how can I loop to each sheets based of the values of range at C2:C277)?
Please Login or Register to view this content.
Well how would Excel know what the sheet name is from the value in the cell, presumably the sheet name isn't the same as the file path
The file path goes like this C:\TRANSACTIONS\QUOTES_ALL\AAA.html (this range is located in colum C)
And I named all the sheets to AAA (located in Column A)
Maybe:
PHP Code:
Sub QUOTES_ALL_IMPORT()
Dim oCell As Object, oRow As Object
Dim html As String
Dim x As Long, y As Long
Dim lRow As Long
Dim filePath As String
Dim LISTED_COMP As Variant
Dim C_LISTED_COMP As Range
Dim htm As Object
Set htm = CreateObject("htmlFile")
LISTED_COMP = Sheets("LISTED_COMP").Range("A2:C277").Value
For lRow = LBound(LISTED_COMP) To UBound(LISTED_COMP)
Open LISTED_COMP(lRow, 3) For Input As #1
html = Input(LOF(1), 1)
Close
'STOCK DATA
With htm
x = 1: y = 8
.body.innerhtml = html
For Each oRow In .getelementsbytagname("table")(5).Rows
For Each oCell In oRow.Cells
Sheets(LISTED_COMP(lRow, 1)).Cells(x, y).Value = oCell.innertext
y = y + 1
Next oCell
y = 8
x = x + 1
Next oRow
End With
Next lRow
End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks