vba 使用宏从网页获取表格数据到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18158928/
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
Get tabledata from webpage into Excel using macro
提问by bhupendra singh
I use a Excel sheet to do some calculation, like assessment of incometax returns. I need to pull data from website into Excel sheet. I successfully did it by using VBA step by step
我使用 Excel 表格进行一些计算，例如所得税申报表的评估。我需要将数据从网站提取到 Excel 工作表中。我通过使用 VBA 一步一步成功地做到了
- Create internet explorer application in VBA
- Navigate to website url and login
- Fill a form automatically by a unique id already in my Excel sheet
- Now submit form and result page has data in table form
- Now by using
getelementsbyid("tableid")I copied and pasted data in excel sheet.
- 在 VBA 中创建 Internet Explorer 应用程序
- 导航到网站网址并登录
- 通过我的 Excel 工作表中已有的唯一 ID 自动填写表单
- 现在提交表单和结果页面有表格形式的数据
- 现在通过使用
getelementsbyid("tableid")我在excel表中复制和粘贴数据。
My question
我的问题
- All tables don't have id or name
- There is lot of tables
- 所有表都没有 id 或 name
- 有很多桌子
Now I want to pull data from a table without id which is third table from top. How to do this? I tried hard. I don't want all tables because rows in these tables always changed so when I copy all tables data.
现在我想从没有 id 的表中提取数据，这是从顶部开始的第三个表。这该怎么做？我很努力。我不想要所有表，因为这些表中的行总是更改，所以当我复制所有表数据时。
回答by V.B.
Start macro recording, go to Data -> From Web, open the required web page in the import dialog, select the required table, set all import options as needed, and import the data. Then stop recording.
开始宏录制，进入数据->从Web，在导入对话框中打开需要的网页，选择需要的表，根据需要设置所有的导入选项，导入数据。然后停止录音。
You will get a boiler plate with auto generated macro code, then it will be trivial to fine turn it to your needs.
您将获得一个带有自动生成的宏代码的样板，然后将其细化到您的需要将是微不足道的。
I did it many times and this is so easy that I haven't stored a snipped to share with you right now.
我做了很多次，这太容易了，我现在还没有存储剪辑与你分享。
Update: this is how to import you question from this page
更新：这是如何从此页面导入您的问题
Sub Macro1()
'
' Macro1 Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://stackoverflow.com/questions/18158928/get-tabledata-from-webpage-into-excel-using-macro/18160278#18160278" _
, Destination:=Range("$A"))
.Name = "18160278#18160278"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells ' adjust this setting to your needs
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1" ' this is the number of the required table on a page
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
