mardi 9 juillet 2019

Avoid crashing VBA when running IE web scraping multiple times

My excel crashes when I scrape a website for information multiple times, and insert it into cell

I already included in my code set IE = Nothing and IE Quit, but it doesn't change the fact that the code returns an error after multiple iterations

My code consists of a loop-part and the actual scraping. Here is the loop:

Public Sub LooperForMMDescription()
Dim currentValue As String
Dim dataList As Variant
Dim i As Integer
Dim n As Integer
Dim FirstRow As Integer
Dim IE As Object
    n = 1
    Set dataList = Range("Table6")
    FirstRow = Range("Table6").Row - 1
    'On Error Resume Next
    Set IE = Nothing

    For i = 1 To UBound(dataList.Value)
        If IsEmpty(dataList.Value) Then
            Exit Sub
        Else
            currentValue = dataList(i, 1).Text
            If Len(currentValue) = 0 Then
            GoTo ByPass
            End If
            Call MM_description(currentValue, n, FirstRow, IE)
ByPass:
            n = n + 1
        End If
    Next i
    Sheets("Input").Range("F7").Select
End Sub


And this is the actual scraping:

Public Sub MM_description(currentValue As String, n As Integer, FirstRow As Integer, IE As Object)

Dim html As HTMLDocument
Dim codeLine As String
Dim startPos As Long
Dim endPost As Long

Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False

IE.Navigate2 (currentValue)
Do While IE.Busy
   Application.Wait DateAdd("s", 1, Now)
Loop

mes = IE.document.body.innerHTML
startPos = InStr(mes, "Description") + 61
endPos = InStr(mes, "Address")

If startPos = 0 Then
    Sheets("Input").Range("F" & FirstRow + n).Value = "Not Found"
Else
    codeLine = Mid(mes, startPos, endPos - startPos - 229)
    Sheets("Input").Range("F" & FirstRow + n).Value = codeLine

End If
IE.Quit
Set IE = Nothing

End Sub

The code runs fine for 80-90 iterations, but then it returns an error




Aucun commentaire:

Enregistrer un commentaire