Excel VBA html Table import – Export Web Table to Worksheet
Export HTML Table to Excel – When Web query does not work
To Import a HTML table in a web-page we can use,
- Web Query option in Excel to import it to Excel or
- From IE browser, right click on the Web Page Table & choose option “Export to Microsoft Excel“.
With some website this web query option will not work properly.
In that case, You can use this code to export HTML Table to Excel using VBA.
Excel VBA HTML Table Import – Step By Step
Extract the HTML Table content of the web page. The article in this page explains about how to import the HTML content.
- In my Previous Article, the web page content is imported to Excel sheet as text content. But in this article, it is assigned to a HTMLFile Object which has more options to retrieve the HTML Tags.
- Once you have the web page content, it will have the Table related Tags like , and .
- Now the last step is to process each row & cell in that table and transfer it to the Excel Sheet.
VBA To Export HTML Table – Pull Website Table Data to Excel
Copy paste this code to Excel VB editor. Type web page URL that you want to scrape in cell A1 of worksheet1. Then
Sub Export_HTML_Table_To_Excel() Dim htm As Object Dim Tr As Object Dim Td As Object Dim Tab1 As Object 'Replace the URL of the webpage that you want to download Web_URL = VBA.Trim(Sheets(1).Cells(1, 1)) 'Create HTMLFile Object Set HTML_Content = CreateObject("htmlfile") 'Get the WebPage Content to HTMLFile Object With CreateObject("msxml2.xmlhttp") .Open "GET", Web_URL, False .send HTML_Content.Body.Innerhtml = .responseText End With Column_Num_To_Start = 1 iRow = 2 iCol = Column_Num_To_Start iTable = 0 'Loop Through Each Table and Download it to Excel in Proper Format For Each Tab1 In HTML_Content.getElementsByTagName("table") With HTML_Content.getElementsByTagName("table")(iTable) For Each Tr In .Rows For Each Td In Tr.Cells Sheets(1).Cells(iRow, iCol).Select Sheets(1).Cells(iRow, iCol) = Td.innerText iCol = iCol + 1 Next Td iCol = Column_Num_To_Start iRow = iRow + 1 Next Tr End With iTable = iTable + 1 iCol = Column_Num_To_Start iRow = iRow + 1 Next Tab1 MsgBox "Process Completed" End Sub Press F5 to execute this code.
Excel will pull HTML table & align the content in the Excel sheet in proper format.
Limitations of Parsing HTML
In many of the Website, even including Facebook, Twitter, the webpage will look like tables.
But they are embedded inside its HTML DIV & SPAN tags and not the tag.
It is better to use Facebook, Twitter API rather than just relying on HTML tags.
Vba table from html
This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.
Answered by:
Question
I’m not an experienced VBA programmer. I’m trying to get a table from a page on Yahoo Finance and parse it into a worksheet. The table that I want to pull is the only table on this particular page with class=»yfnc_tabledata1″. (The table that I actually want is the table within this table, but I figured the easiest way to extract is to use that class name).
I’ve added references to the HTML Object Library and Internet Controls. Whatever way I try to do it, I end up either with «Object doesn’t support this property or method» or «Type mismatch» (see comments in code below). Once I’ve got the table I’d like to put it into a worksheet cell by cell, eliminating colspans. I’ll try to figure that out once I’ve got the table. Here is my code. Can anyone please help?
Sub trypage()
Dim IE As InternetExplorer Dim HTMLDoc As HTMLDocument Dim Table As HTMLTable
Dim Row As HTMLTableRow
Dim ticker As String
Dim prefix As String
Dim page As String
ticker = «IBM»
prefix = «http://finance.yahoo.com/q/is?s=»
page = prefix & ticker & «&» & annual
Set IE = New InternetExplorer
With IE
.navigate page
.Visible = False ‘you can put True if you want to see the page
‘Wait for page to load
While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
Set HTMLdoc = .document ‘This line above is left over from a previous attempt and doesn’t generate any error message
Set Table = .getElementsByClassName(«yfnc_tabledata1»)
‘Object doesn’t support this property or method
End With
Set Table = HTMLdoc.getElementsByClassName(«yfnc_tabledata1»)
‘get a type mismatch on this
Импорт таблицы из html в excel
Здравствуйте!
Как можно перенести таблицу с web-страницы в excel? Вариант с / Данные- Получение данных из внешних источников- Из интернета результата не приносят. Сайт мой отличается от ниже написанного, но прошу помочь с импортом таблицы номер 1
Пример Сайт: Vawilon.ru/statistika-smertnosti/
Импорт таблицы HTML в Word
Преобразовать Таблица (HTML) -> Таблица (Selection) с элементами форматирования (italics, bold.
Импорт таблицы html из тела письма Outlook
Привет всем! Возник немного нестандартный вопрос по использованию VBA outlook. Вопрос мягко.
Импорт таблицы из текстового файла в Excel
Всем здрасти, не подскажите как табличку(данные) из текстого файла (.txt) перекинуть в exsel с.
Импорт определенных строк из таблицы Word в Excel
Имеется документ Word с таблицей данных. Необходимо строки, в которых присутствует определенное.
Сообщение от schapovalov1991 
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
table class="table table-responsive table-bordered table-hover" style="height: 128px; width: 600px;"> tbody> tr> td style="width: 72px;">span style="font-size: 16px;">Япония/span>/td> td style="width: 109px;">span style="font-size: 16px;">Финляндия/span>/td> td style="width: 108px;"> p style="text-align: center;">span style="font-size: 16px;">Южная /span>/p> p style="text-align: center;">span style="font-size: 16px;">Корея/span>/p> /td> td style="width: 81px;">span style="font-size: 16px;">Израиль/span>/td> td style="width: 86px;">span style="font-size: 16px;">Беларусь/span>/td> td style="width: 61px;">span style="font-size: 16px;">Литва/span>/td> td style="width: 51px;">span style="font-size: 16px;">Куба/span>/td> td style="width: 48px;">span style="font-size: 16px;">США/span>/td> td style="width: 54px;">span style="font-size: 16px;">Кипр/span>/td> td style="width: 10px;">span style="font-size: 16px;">Китай/span>/td> /tr> tr> td style="width: 72px;">2/td> td style="width: 109px;">2,5/td> td style="width: 108px;">3/td> td style="width: 81px;">3,5/td> td style="width: 86px;">3,6/td> td style="width: 61px;">3,8/td> td style="width: 51px;">4,5/td> td style="width: 48px;">5,8/td> td style="width: 54px;">8,1/td> td style="width: 10px;">12,2/td> /tr> /tbody> /table>
Сообщение было отмечено schapovalov1991 как решение
Решение
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
Sub uuu() Dim url$, htm$ Dim tb, rw, cl Dim a() Dim i&, j& '------------------ url = "http://vawilon.ru/statistika-smertnosti/" With CreateObject("msxml2.xmlhttp") .Open "GET", url, False .send Do: DoEvents: Loop Until .ReadyState = 4 htm = .responsetext End With With CreateObject("HtmlFile") .Body.innerHTML = htm For Each tb In .GetElementsByTagName("table") If tb.ClassName = "table table-responsive table-bordered table-hover" Then ReDim a(1 To tb.Rows.Length, 1 To tb.Rows(0).Cells.Length) For Each rw In tb.Rows i = i + 1 j = 0 For Each cl In rw.Cells j = j + 1 a(i, j) = cl.InnerText Next Next Exit For End If Beep Next End With Cells(1, 1).Resize(UBound(a), UBound(a, 2)) = a Beep End Sub