VBA Runtime Error which works with breakpoint

user_1450871 0
Viewed 69

I am trying to extract data from a website and parse it using VBA. The code is working absolutely fine when stepping through the code however, when running it gives an 'Object Required' run-time error. Here is the code:

Function AuthToken()

Dim XMLHTTP
Dim result As String
Dim csrf As String
Dim token As Object
Dim objHTML As Object

Set objHTML = CreateObject("htmlfile")

Set XMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0")

With XMLHTTP
    .Open "GET", "some website", False
    .setRequestHeader "Cookie", cf
    .SEND
    result = .responseText
End With

With objHTML
    .Open
    .Write result
    .Close
    Set token = .getElementsByName("csrf-token")
End With

AuthToken = token(0).Content
AuthToken = Replace(Replace(Replace(AuthToken, "+", "%2B"), "/", "%2F"), "=", "%3D")

Set XMLHTTP = Nothing

End Function

The error is triggered by this line in the code:

AuthToken = token(0).Content

If I just click debug and resume, it works fine so I am really struggling to debug this. Can anyone help?

vba html
1 Answers

Please, instead of

    result = .responseText

use

    If .status = 200 Then
        result = .responseText
    End If

Your code needs to be sure that URL has been loaded completely...4

If you place the next debugging line:

   Debug.Print token Is Nothing: Stop

immediately after

   Set token = .getElementsByName("csrf-token")

Edited:

If not a matter of .status not "OK", you can try to wait a little before reaching the problematic code line. Please call the next Sub, just before that line:

  1. copy the next API on top of the module keeping the code (in the declarations area):
 Private Declare PtrSafe Function GetTickCount Lib “kernel32.dll” () As Long
  1. Add the next waiting Sub:
Sub WaitALittle(Finish As Long) 
    Dim NowTick As Long,EndTick As Long
 
    EndTick = GetTickCount + (Finish * 1000)
     
    Do 
        NowTick = GetTickCount
        DoEvents 
    Loop Until NowTick >= EndTick 
End Sub

It can be simple called as WaitALittle 2. You may play a little with that 2 (time to wait)...

user_22333080
Related