I tried to simulate a VB Script from this forum: Search a website with Excel data to extract results and then loop
I got an error on this line:
URL_Get_ABN_Query = entityRange.Offset(0, 1).Value2
the error is: error 91 object variable or with block variable not set
Here is the two-part script of the said forum:
Code:
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
Sorry I'm not really a programmer I'm just looking for solution from an expert in every forum, yes it is a vb6 script for excel. Aren't they have the same syntax?
Unfortunately there are lots of variations of VB, and the naming is definitely not obvious.
VBScript is .vbs files (mainly for web based things), VB6 is an independent program (which can be used to create independent programs), and what you get inside Excel etc is actually VBA (Visual Basic for Applications), which is similar to VB6 but has several differences.
It sounds like you are using VBA in Excel, so I have moved this thread to our "Office Development/VBA" forum.
' 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
most likely cause of the error is that "Finish"" was not found, but that is still only a symptom of some other problem
as we cannot test the returned results from the query table, can not tell you more
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
so there is no "entity type" to find, the cause of your error
the data you are trying to find is in the first column, not the offset
you could do a find on "finish" that should return the data you require in the found cell
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
I did tried to replaced the url with my target url and the "Entity type:" with "finish" but the result is the same, I think I have to get rid off error 91 first
I did also try to replaced the second part of the script suggested by one of the members in that forum but the result is just blank when I Ctrl + G in the VB editor
Code:
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
while the original suggested first part produced Discretionary Trading Trust Not found Discretionary Trading Trust Not found
Code:
Sub Tester()
Debug.Print URL_Get_ABN_Query("44627939854") '>> Discretionary Trading Trust
Debug.Print URL_Get_ABN_Query("XXXX") '>> Not found
End Sub
do you have any suggested syntax as overhaul on this script?
i am confused here, your code all indicates you are looking up an ABN, but the post #8 suggests you are looking for some information about some hardware items, on which i based my previous reply, but in the test it again indicates you are searching an ABN, but the attached workbook also indicates that the hardware search was your target
this works for your hardware finish, but i have no idea if it is actually what you want, if you want something different you should be more clear
Code:
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;https://www.hardwareresources.com/307fu.html?item_overall_length=", _
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("finish")
' Then return the value of the cell to its' right
URL_Get_ABN_Query = entityRange
' Clear Sheet2 for the next run
Sheet2.UsedRange.Delete
End Function
BTW the link you have given is invalid.
i am hardly surprised, i did test the link when i posted and again now and it works ok for me, but hardly relevant now
while the original suggested first part produced Discretionary Trading Trust Not found Discretionary Trading Trust Not found
i also tested your code directly as in post #10 (copy and paste) it appeared to work correctly returning Discretionary Trading Trust for the first example, which i believe is what you want, so i am not sure about your remark above
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Sorry for the confusion, I tried to simulate first the scripts I've found from Stackoverflow forum then I plan to just replace the elements like the URL and the Find(value) so I didn't mind the URL_Get_ABN_Query as function name.
here's really my objective:
parse from the https://www.hardwareresources.com the finish type of any given hardware item no. from Sheet1 to Sheet2.
Example:
307FU (from Sheet1!A1)
the script should use the link instead:
Code:
With Sheet2.QueryTables.Add( _
Connection:="URL;https://www.hardwareresources.com/catalogsearch/result/?q=" & strSearch & "&safe=active",
_
instead of "Entity type" I'm trying to find the "finish" word in the resulting webpage:
Code:
' Find the Range that has "Entity Type:"
Set entityRange = Sheet2.UsedRange.Find("finish:")
if you scroll down from the webpage result you will see:
FEATURES
3/4" side spacing
Lubricated steel ball bearings
Non-handed trigger disconnect release
54 mm profile height
2.2 mm x 2.0 mm x 2.0 mm cold rolled steel material Clear zinc electroplated finish
6.3 mm euro screw or #8 x 7/16" truss head screw is recommended
the parsing result in Sheet 2!A:A should be:
Clear zinc electroplated
the I want the next cells below (A2), (A3), (A4)... (A1000)
P.S. I have attached my test spreadsheet for your simulation, kindly find it. Thank you!
the function code i posted in #11 should do what you want, with your original sub as posted in #1
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Hi, it took me forever (>30 mins.) and still not able to see the result for one item only (209FU20) so I ended the process. My status bar just keep me looping in this state:
CONNECTING TO WEB...>RETRIEVING DATA FROM WEB... > COPYING WEB DATA TO SHEET...
Can you give me the screenshot of your result, how long did it take you to get parsed the text?