用 Excel 数据搜索网站以提取结果然后循环 [英] Search a website with Excel data to extract results and then loop

问题描述

我在 Excel 电子表格中有 8000 个值.

我需要搜索一个网站，然后将来自该网站的特定行数据记录到 Excel 电子表格中.

我找到了搜索数据的代码

我只想要实体类型"数据行.

我找不到如何扩展代码以仅抓取此行并输入到相应的单元格.即ABN(b2)搜索，找到输入实体类型"并粘贴到公司类型(c2)中.

或者，我试图找到如何垂直而不是水平填充信息.我可以删除不需要的列.我认为这可能更简单.

我试图与开发人员一起录制宏.

我还需要循环到下一个 ABN 并填充相应的字段等等(B3>C3、B4>C4 等).

解决方案

这绝对有可能.你有我经常发现的最困难的部分，从另一个平台获取信息.为了完成这项工作，我会将它分开一点，为简单起见，使用 2 张工作表(Sheet1 包含您的已知数据，Sheet2 用于网络数据).

遍历您的约 8000 家企业表.我们可以从 UsedRange 行数中识别出这一点.我们知道 ABN 在第 2 列(也称为 B)中，因此我们将其复制到变量中以传递给函数.该函数会将实体类型:"返回到同一行的第 3 (C) 列.

Sub LoopThroughBusinesses()Dim i 作为整数将 ABN 调暗为字符串对于 i = 2 到 Sheet1.UsedRange.Rows.CountABN = Sheet1.Cells(i, 2)Sheet1.Cells(i, 3) = URL_Get_ABN_Query(ABN)接下来我结束子

将您创建的子程序更改为函数，以便它返回您所追求的实体类型.该函数将数据保存到 Sheet2 中，然后只返回我们所追求的实体数据.

Function URL_Get_ABN_Query(strSearch As String) As String ' 将其从 Sub 更改为返回所需字符串的函数' strSearch = Range("a1") ' 现在作为参数传递给函数Dim entityRange 作为范围使用 Sheet2.QueryTables.Add( _连接:="URL;http://www.abr.business.gov.au/SearchByABN.aspx?SearchText="&strSearch &"&safe=active", _Destination:=Sheet2.Range("A1")) ' 将此目的地更改为 Sheet2.BackgroundQuery = True.TablesOnlyFromHTML = True.Refresh BackgroundQuery:=False.SaveData = 真结束于' 找到具有实体类型:"的范围Set entityRange = Sheet2.UsedRange.Find("实体类型:")'然后将单元格的值返回到它的'右边URL_Get_ABN_Query = entityRange.Offset(0, 1).Value2' 为下一次运行清除 Sheet2Sheet2.UsedRange.Delete结束函数

I have 8000 values in an Excel spreadsheet.

I need to search a website and then record a specific line of data from the website to in the Excel spreadsheet.

I found code which searches for data excel macro to search a website and extract results

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
    'enter code here
End Sub

It collects the data from the website like this.

I only want the 'entity type' data line.

I can not find how to extend the code to only grab this line and input to the corresponding cell. i.e. ABN(b2)search, find input 'entity type' and paste into Company Type(c2).

Alternatively, I tried to find how to fill the information vertically instead of horizontally. I could delete the columns that are not needed. I thought this may be simpler.

I tried to record the macro with developer.

I also need to loop to the next ABN and populate the corresponding field and so on (B3>C3, B4>C4, etc.).

解决方案

This is absolutely possible. You've got what I often find the hardest part, sourcing the information from another platform. To make this work I would separate it out a little bit and for simplicity use 2 sheets (Sheet1 with your known data and Sheet2 for the web data).

Loop through your table of ~8000 businesses. We can identify this from the UsedRange number of Rows. We know that the ABN is in column 2 (also known as B) so we copy that into the variable to pass to the function. The function will return the "Entity type:" to column 3 (C) of the same row.

Sub LoopThroughBusinesses() 
    Dim i As Integer
    Dim ABN As String
    For i = 2 To Sheet1.UsedRange.Rows.Count
        ABN = Sheet1.Cells(i, 2)
        Sheet1.Cells(i, 3) = URL_Get_ABN_Query(ABN)
    Next i
End Sub

Change the subroutine you created to a Function so it returns the entity type you are after. The function will save the data into Sheet2 and then return just the Entity data that we are after.

Function URL_Get_ABN_Query(strSearch As String) As String   ' Change it from a Sub to a Function that returns the desired string
    ' strSearch = Range("a1") ' This is now passed as a parameter into the Function
    Dim entityRange As Range
    With Sheet2.QueryTables.Add( _
            Connection:="URL;http://www.abr.business.gov.au/SearchByABN.aspx?SearchText=" & strSearch & "&safe=active", _
            Destination:=Sheet2.Range("A1"))   ' Change this destination to Sheet2

        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With

    ' Find the Range that has "Entity Type:"
    Set entityRange = Sheet2.UsedRange.Find("Entity type:")

    ' Then return the value of the cell to its' right
    URL_Get_ABN_Query = entityRange.Offset(0, 1).Value2

    ' Clear Sheet2 for the next run
    Sheet2.UsedRange.Delete

End Function

这篇关于用 Excel 数据搜索网站以提取结果然后循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！