错误91 VBscript将html文本解析为excel

Question

我试图从这个论坛模拟VB脚本：使用Excel数据搜索网站以提取结果，然后循环

我在这条线上有一个错误：

URL_Get_ABN_Query = entityRange.Offset(0, 1).Value2

错误为：错误91对象变量或块变量未设置

以下是上述论坛的两部分脚本：

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 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 "Finish"
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

Answer 1

以上评论如下：

Sub Tester()
Debug.Print URL_Get_ABN_Query("44627939854") '>> Discretionary Trading Trust
Debug.Print URL_Get_ABN_Query("XXXX")        '>> Not found

End Sub
Function URL_Get_ABN_Query(strSearch As String) As String

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"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
'when using Find, you should always specify at least the `lookat` argument...
'  xlPart (matches part) or xlWhole (full match)
Set entityRange = Sheet2.UsedRange.Find(what:="Entity type:", lookat:=xlPart)

'check to see if a match was made by Find()
If Not entityRange Is Nothing Then
URL_Get_ABN_Query = entityRange.Offset(0, 1).Value2
Else
URL_Get_ABN_Query = "Not found"
End If
Sheet2.UsedRange.Delete ' Clear Sheet2 for the next run
End Function