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

问题描述

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

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

我找到了搜索数据的代码

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

我找不到如何扩展代码以仅抓住这一行并将其输入到相应的单元格.即ABN(b2)搜索，找到输入的实体类型"并粘贴到Company Type(c2)中.

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

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

我还需要循环到下一个ABN并填充相应的字段，依此类推(例如B3> C3，B4> C4等).

解决方案

这是绝对可能的.您拥有了我经常发现的最困难的部分，即从另一个平台获取信息.为了使这项工作有效，我将它分开一点，为简单起见，请使用2张纸(Sheet1包含您的已知数据，Sheet2包含Web数据).

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

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

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

 函数URL_Get_ABN_Query(strSearch As String)As String'将其从Sub更改为返回所需字符串的Function'strSearch = Range("a1")'现在，将其作为参数传递给函数昏暗的实体范围范围使用Sheet2.QueryTables.Add(_连接:="URL; http://www.abr.business.gov.au/SearchByABN.aspx?SearchText ="&搜索& safe = active"，_Destination:= Sheet2.Range("A1"))'将此目的地更改为Sheet2.BackgroundQuery = True.TablesOnlyFromHTML = True.刷新BackgroundQuery:= False.SaveData = True结束于'查找具有实体类型:"的范围设置entityRange = Sheet2.UsedRange.Find(实体类型:")'然后将单元格的值返回其右侧'URL_Get_ABN_Query = entityRange.Offset(0，1).Value2'清除Sheet2以便下次运行Sheet2.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屋！