Excel宏可使用Excel数据搜索网站并提取特定结果，然后循环查找下一个值 [英] Excel macro to search a website with excel data and extract specific results and then loop for next value

问题描述

我希望有人可以提供帮助....

I am hoping someone can help....

我在excel电子表格中有8000个值，需要在网站中进行搜索，然后记录该网站中的特定数据行，以将其输入回excel电子表格中.

I have 8000 values in a excel spreadsheet that I need to search in a website and then record a specific line of data from the website to be inputted back into the excel spreadsheet.

我找到了以前的帖子，该帖子搜索我正在查看的数据

I have found a previous post which searches for the data I am looking at 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

但是，当我运行Excel宏时，它会像这样从网站收集所有数据.

However, when I run then Macro in Excel it collects all the data from the website like this.

我只希望输入实体类型"数据行.我到处搜索，似乎无法找到如何扩展代码以仅获取这行信息并将其输入到相应单元格的方法(例如，ABN(b2)搜索，找到输入实体类型"并粘贴到Company Type(c2)中)

I only want the 'entity type' data line to be inputted. I have searched everywhere and can not seem to find how to extend the code to only grab this line of information and input to the corresponding cell (i.e. ABN(b2)search, find input 'entity type' and paste into Company Type(c2).

或者，我也试图找到如何使宏垂直而不是水平地填充信息，因为这样我可以删除不需要的列，我认为这可能是运行此宏的更简单方法，但是我再次找不到帮助.我还尝试与开发人员一起记录宏，但是那也不起作用.

Alternatively, I have also tried to find how to get the macro to fill the information vertically instead of horizontally as then I could delete the columns that are not needed, I thought this may be a simpler way to run this macro but alas again I could not find help to do it. I also tried to record the macro with developer but that did not work either.

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

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

我希望能对您有所帮助，因为我是VBA的初学者，并且认为我想做的事超出了我的技能水平.我试图通过教程，谷歌搜索和帮助页面来理解，但似乎找不到如何或是否可以做到这一点.

I would love some help figuring, I am a beginner in VBA and think what I want to do is beyond my skill level at this time. I am trying to understand through tutorials, google searches and help pages but can not seem to find how or if this can be done.

我的替代方法是针对8000个数据点中的每个手动进行此操作，然后复制每个abn，在网站中进行搜索，然后复制实体类型并将其粘贴到excel中，我确实先尝试了此方法，但过了一会儿开始搜索更好的方法.你能帮忙吗????

My alternative is to do this manually for each of the 8000 data points, copying each abn, searching in the website and then copying the entity type and pasting into excel, I did try this first but after a while started searching for a better way. Can you help????

推荐答案

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

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).

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

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

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

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宏可使用Excel数据搜索网站并提取特定结果，然后循环查找下一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！