mardi 4 juillet 2017

VBA EXCEL Random error 91 object variable or with block variable not set

Hi I have this weird problem where I get error 91 ("object variable or with block variable not set"). I do not always get the error when I run the script, only now and then (random). I have tried to do as much as I can without success.

I am scraping a site to get the most recent bare-prices of some pc components from specific shops.

The error is not related to a specific part. I get the error randomly on all the parts. Sometimes I can do all the calls without any errors.

I call the script like so:

Call ImportTweakersData("http://ift.tt/2ulktUi", "B1")
Call ImportTweakersData("http://ift.tt/2tMMcjZ", "B2")
...(and so on)...

The script itself:

Sub ImportTweakersData(url As String, inputRange As String)

    Dim ie As InternetExplorer
    Dim html As HTMLDocument
    Dim count As Byte
    count = 1
    Dim varListingIDElement As IHTMLElement
    Dim varShopName As String
    varShopName = ""
    Dim varShop As Variant
    Dim arrayShops As Variant
    arrayShops = Array("Azerty", "Megekko", "Alternate Belgie", "Art & Craft België", "Coolblue.be", "Bol.com Plaza België")
    Dim varPrice As Variant
    Dim boolFound As Boolean
    boolFound = False

    Set ie = New InternetExplorer
    ie.Visible = False
    ie.navigate url

    Do While ie.readyState <> READYSTATE_COMPLETE
        Application.StatusBar = "Trying to go to Tweakers ..."
        DoEvents
    Loop

    Set html = ie.document
    Application.StatusBar = ""
    Worksheets("PriceAndShop").Activate

    Set varListingIDElement = html.getElementById("listing")

    Do
        varShopName = getXPathElement("/table/tbody/tr[" & count & "]/td[1]/p/a", varListingIDElement).innerText

        For Each varShop In arrayShops
            If varShopName = varShop Then
                varPrice = getXPathElement("/table/tbody/tr[" & count & "]/td[4]/p/a", varListingIDElement).innerText
                boolFound = True
                Exit For
            End If
        Next
        teller = teller + 1
    Loop Until boolFound = True

    Range(inputRange).Value = Null
    Range(inputRange).Value = Val(Replace(Replace(varPrice, "€", ""), ",", "."))

    Range(inputRange).Offset(0, 1).Value = Null
    Range(inputRange).Offset(0, 1).Value = varShopName

    Set html = Nothing
    ie.Quit
    Set ie = Nothing
End Sub

I always get the error on:

varShopName = getXPathElement("/table/tbody/tr[" & count & "]/td[1]/p/a", varListingIDElement).innerText

(The error is not occurring inside the 'getXPathElement' function, so no need to show that too. If you do want to take a look at it just ask and I'll post it here in an edit.)

When I debug from start to end with F8 I never get the error. Is it possible that the next call already starts before the previous one isn't finished yet?

Thanks in advance!

(Sorry if there are some dutch words I have overseen.)




Aucun commentaire:

Enregistrer un commentaire