excel宏搜索网站并提取结果 [英] excel macro to search a website and extract results

问题描述

我在工作表1，A1中有一个值.它可以是公司名称，也可以是与其相关的公司编号(因为网站是按编号或名称搜索的).输入需要详细信息的公司的公司名称(或编号)后，我希望能够单击搜索"按钮，并将搜索结果显示在具有2列的表格中(例如工作表1 ，A5:B9)，标签在表格的左列，结果在右侧. 我需要搜索的站点是 http://www.abr.business.gov.au/ 例如.如果我搜索公司号码31701562618，这些就是我得到的结果(以及如何在excel中显示:

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:

      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

推荐答案

经过一番修补后，我设法使它起作用.这只是在站点中搜索A1中的值，获取结果数据并将其放入从A5开始的单元格中.但是，没有格式很容易在"End With"位之后添加.

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.

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

这篇关于excel宏搜索网站并提取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！