Results 1 to 14 of 14

Thread: Error 91 VBscript parsing html text to excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    8

    Question Error 91 VBscript parsing html text to excel

    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

  2. #2

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    8

    Re: Error 91 VBscript parsing html text to excel

    Quote Originally Posted by VB Bibes View Post
    I tried to simulate a VB Script from this forum: Search a website with Excel data to extract results and then loop
    Here is the link: https://stackoverflow.com/questions/...-and-then-loop

  3. #3
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: Error 91 VBscript parsing html text to excel

    Is this vbscript? It looks more like vb6.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    8

    Re: Error 91 VBscript parsing html text to excel

    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?

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Error 91 VBscript parsing html text to excel

    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.

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    8

    Re: Error 91 VBscript parsing html text to excel

    Thanks for the enlightment and kind action. Hope I get the needed solution in short time :-)

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Error 91 VBscript parsing html text to excel

    ' 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

  8. #8

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    8

    Re: Error 91 VBscript parsing html text to excel

    Thanks for the assistance, I did not mention anything about Finishes or declared in the code but you are indeed right, my real target is to parse finish type from this https://hardwareresources.com/307fu....verall_length= with sample item number below: 209FU20 209FU22 209FU24 so for example if you try: https://hardwareresources.com/307fu....length=209FU20 you will learn it is a clear zinc finish.

    do I need to declare or reference something to this line to get rid of the error 91?

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

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Error 91 VBscript parsing html text to excel

    I did not mention anything about Finishes
    you are correct there, but it was in the comment

    this is the result from the suggested querytable
    Attachment 181711

    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

  10. #10

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    8

    Re: Error 91 VBscript parsing html text to excel

    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?

    here is the spreadsheet I'm working on : https://drive.google.com/file/d/1UX4...ew?usp=sharing

    BTW the link you have given is invalid.

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Error 91 VBscript parsing html text to excel

    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

  12. #12

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    8

    Re: Error 91 VBscript parsing html text to excel

    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!
    Attached Files Attached Files

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Error 91 VBscript parsing html text to excel

    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

  14. #14

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    8

    Re: Error 91 VBscript parsing html text to excel

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width