Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Runtime error 91

    Hi I have a list of urls in the A column, and I want to cycle through each one and scrape the item specifics table. However i come into Run Time 91 Object Variable or Block not set on the ActiveSheet.Range.("B2"). This is my code
    Code:
    Sub GetData()
    Dim a As New htmldocument, x&, y&, z&
    
    Dim URL As Variant
    Dim Rng As Range
        
        Set Rng = Range("A2")
        
    Do While Not IsEmpty(Rng)
        With CreateObject("WinHTTP.WinHTTPRequest.5.1")
        .Open "GET", URL, False
        .Send
        a.body.innerHTML = .responseText
        End With
        
    
        
        z = 0
        For y = 0 To a.getElementsByClassName("itemAttr")(0).Children(0).Children(1).Children(0).Children.Length - 2
        For x = 1 To 3 Step 2
        ActiveSheet.Range("B2").Offset(, z) = a.getElementsByClassName("itemAttr")(0).Children(0).Children(1).Children(0).Children(y).Children(x).innerText
        z = z + 1
        Next x
        Next y
        
        If a.getElementsByClassName("itemAttr")(0).Children(2) Is Nothing Then
            Rng.Offset(0, 1).Value = "Could not find data."
            GoTo NextURL
        End If
        
    NextURL:
        Set Rng = Rng.Offset(1, 0)
        Loop
        
    
    End Sub
    Any help or pointers are appreciated!

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You haven't set "a" to anything and are then trying to use its value. You also haven't set "URL" to anything.

    When you create a variable it's useful to prefix it with a letter to indicate its type, so the variable "URL" would be "vURL" and "a" (bad naming there) would be "hA".
    Try not to use "goto" if possible, its less logical and can make debugging harder.

    I've tidied up the loop and picked up the values, but it's not tested. Let us know how it goes.

    cheers, Paul

    Code:
    Sub GetData()
    Dim hWebDoc As New htmldocument, x&, y&, z&
    
    Dim vURL As Variant
    Dim rRng As Range
    Dim iCount as Integer
    
    Set rRng = Range("A2")
    
    For iCount = 0 to rRng.Rows.Count
    	vURL = rRng.Offset(0, iCount).Value
    	If vURL <> "" Then
    		Set hWebDoc = CreateObject("WinHTTP.WinHTTPRequest.5.1")
    		With hWebDoc
    		.Open "GET", vURL, False
    		.Send
    		.body.innerHTML = .responseText
    		End With
    
    
    
    		z = 0
    		For y = 0 To hWebDoc.getElementsByClassName("itemAttr")(0).Children(0).Children(1).Children(0).Children.Length - 2
    			For x = 1 To 3 Step 2
    				ActiveSheet.Range("B2").Offset(, z) = hWebDoc.getElementsByClassName("itemAttr")(0).Children(0).Children(1).Children(0).Children(y).Children(x).innerText
    				z = z + 1
    			Next x
    		Next y
    
    		If hWebDoc.getElementsByClassName("itemAttr")(0).Children(2) Is Nothing Then
    			rRng.Offset(0, 1).Value = "Could not find data."
    		End If
    
    		Set rRng = rRng.Offset(1, 0)
    	Endif
    Next 'iCount
    
    
    End Sub

  3. #3
    New Lounger
    Join Date
    Jul 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Paul T,
    Thanks for your help! This looks cleaner, however I get run time error 13 on the "Set hWebDoc = CreateObject("WinHTTP.WinHTTPRequest.5.1") line. Is this because hWebDoc as already been declared as an htmldocument?

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    I missed your early binding statement (Dim hWebDoc As New htmldocument) and then used your existing late binding one (Set hWebDoc = CreateObject("WinHTTP.WinHTTPRequest.5.1")). You can't have both.
    If you Dim as an object you avoid early binding - Dim hWebDoc As Object.

    p.s. I should have used oWebDoc as it's an object - getting rusty.

    cheers, Paul

Tags for this Thread

Posting Permissions

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