vba 用于搜索网站并提取结果的excel宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13682639/
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
excel macro to search a website and extract results
提问by user1872463
I have a value in Sheet 1, A1. It is either a business name, or its associated business number (as the site searches by number or name). Once I have entered the business name (or number) of the business I need the details on, I want to be able to click a "search" button and have the results of the search displayed in a table with 2 columns (say sheet 1, A5:B9) with the labels in left column of table and the results in the right. the site i need to search is http://www.abr.business.gov.au/eg. If i search for the business number 31701562618 these are the results i get (and how i need displayed in excel:
我在 Sheet 1, A1 中有一个值。它是企业名称或其关联的企业编号（因为站点按编号或名称搜索）。一旦我输入了我需要详细信息的企业名称（或编号），我希望能够单击“搜索”按钮并将搜索结果显示在一个包含 2 列的表格中（比如工作表 1 , A5:B9) 标签在表的左列，结果在右列。我需要搜索的网站是http://www.abr.business.gov.au/例如。如果我搜索业务编号 31701562618，这些就是我得到的结果（以及我需要如何在 excel 中显示：
Column A Column B
5 Entity name: AMBROSE, BENJAMIN STEPHEN
6 ABN status: Active from 05 Apr 2000
7 Entity type: Individual/Sole Trader
8 Goods & Services Tax (GST): Registered from 01 Jul 2000
9 Main business location: QLD 4310
回答by user1872463
After some more tinkering i managed to get this to work. This just searches the site for the value in A1, grabs the results data and puts it into cell starting at A5. NO formatting however that is easy enough to include after the 'End With' bit.
经过更多的修修补补，我设法让它发挥作用。这只是在站点中搜索 A1 中的值，获取结果数据并将其放入从 A5 开始的单元格中。然而，没有格式很容易包含在“结束于”位之后。
Sub URL_Get_ABN_Query()
strSearch = Range("a1")
With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.abr.business.gov.au/SearchByABN.aspx?SearchText=" & strSearch & "&safe=active", _
Destination:=Range("a5"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
