从多个网页中提取数据到 Excel - 使用 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15168459/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extract Data from a Multiple Web Pages into Excel - using VBA
提问by Surya Sudarsi
I am new to macros and trying to extract the data from the following webpage into excel. The data from the below link spans to 7 pages (appr 3500 rows). I need this data into my excel work workbook.
我是宏的新手，并试图将以下网页中的数据提取到 excel 中。以下链接中的数据跨越 7 页（约 3500 行）。我需要将此数据放入我的 excel 工作簿中。
I have tried web data import feature in excel. But, unable to do that due to unknown reason. Please someone help.....
我在excel中尝试过网络数据导入功能。但是，由于未知原因无法做到这一点。请人帮忙.....
回答by dee
The following code worked for me. It should get the pages 1-7 from web site and paste each to separate excel sheet.
以下代码对我有用。它应该从网站获取第 1-7 页并将每个页面粘贴到单独的 Excel 表中。
Private Const URL_TEMPLATE As String = "URL;http://www.moneycontrol.com/stocks/hist_stock_result.php?sc_id=RI&pno={0}&hdn=daily&fdt=2000-01-01&todt=2013-03-01"
Private Const NUMBER_OF_PAGES As Byte = 7
Sub test()
Dim page As Byte
Dim queryTableObject As QueryTable
Dim url As String
For page = 1 To NUMBER_OF_PAGES
url = VBA.Strings.Replace(URL_TEMPLATE, "{0}", page)
Set queryTableObject = ActiveSheet.QueryTables.Add(Connection:=url, Destination:=ThisWorkbook.Worksheets.Add.[a1])
queryTableObject.WebSelectionType = xlSpecifiedTables
queryTableObject.WebTables = "3"
queryTableObject.Refresh
Next page
End Sub
