API Endpoint Login With VBA

[menawollas]

Could anyone help me with setting up a login with the API Endpoint using VBA in Excel??

I can do the normal interactive login using

 ActiveSheet.WebBrowser1.Navigate "https://identitysso.betfair.com/view/login?product=APPKEY&url=https://www.betfair.com"

And then using the following in BeforeNavigate2

        Dim lCount As Long
        Dim lLen As Long
        Dim strPostData As String
        
        lLen = LenB(PostData) ' Use LenB to get the byte count
        
        If lLen > 0 Then    ' If it's a post form, lLen will be > 0
            For lCount = 1 To lLen
                strPostData = strPostData & Chr(AscB(MidB(PostData, lCount, 1))) ' Use MidB to get 1 byte at a time
            Next
        MsgBox strPostData
        End If

However using the API endpoint, I always get a fail with INPUT_VALIDATION_ERROR

I am calling a sendrequest function as

    strURL = "https://identitysso.betfair.com/api/login/"
    strKey = "MYKEY"
           

    Dim xhr
    Set xhr = CreateObject("MSXML2.XMLHTTP")
  
    With xhr
        .Open "POST", strURL, False, "username=USERNAME", "password=PASSWORD"
        .setRequestHeader "X-Application", strKey                
        .setRequestHeader "Accept", "application/json"
    End With
     
    
    xhr.send
    SendRequest = xhr.responseText

But have played around with putting username&password in the data of the send and various other things - all to no avail.

How should I be formatting this ??????

Thanks

[davecon]

Hi menavollas

I did plenty of hair pulling myself with this one in VBNet (Same for VBA)

The Login String is a unique one and needs to have the UserName and Password embedded

So the Endpoint you actually want to achieve is not

strURL = "https://identitysso.betfair.com/api/login"

But the idiotic

strURL = "https://identitysso.betfair.com/api/login/?username=YourUserName&password=YourPassword"

I use a Function to join them like this
'
Private Function LoginString(UserName As String, Password As String) As String
'
Dim strID As String = "?username=" & UserName & "&password=" & Password 'Ball acher!

'Get Full String

Dim strFullLogin_Url As String = strLogin & strID

Return strFullLogin_Url

End Function 'LoginString

Hope this helps

Dave

Could anyone help me with setting up a login with the API Endpoint using VBA in Excel??

I can do the normal interactive login using

 ActiveSheet.WebBrowser1.Navigate "https://identitysso.betfair.com/view/login?product=APPKEY&url=https://www.betfair.com"

And then using the following in BeforeNavigate2

        Dim lCount As Long
        Dim lLen As Long
        Dim strPostData As String
        
        lLen = LenB(PostData) ' Use LenB to get the byte count
        
        If lLen > 0 Then    ' If it's a post form, lLen will be > 0
            For lCount = 1 To lLen
                strPostData = strPostData & Chr(AscB(MidB(PostData, lCount, 1))) ' Use MidB to get 1 byte at a time
            Next
        MsgBox strPostData
        End If

However using the API endpoint, I always get a fail with INPUT_VALIDATION_ERROR

I am calling a sendrequest function as

    strURL = "https://identitysso.betfair.com/api/login/"
    strKey = "MYKEY"
           

    Dim xhr
    Set xhr = CreateObject("MSXML2.XMLHTTP")
  
    With xhr
        .Open "POST", strURL, False, "username=USERNAME", "password=PASSWORD"
        .setRequestHeader "X-Application", strKey                
        .setRequestHeader "Accept", "application/json"
    End With
     
    
    xhr.send
    SendRequest = xhr.responseText

But have played around with putting username&password in the data of the send and various other things - all to no avail.

How should I be formatting this ??????

Thanks

[menawollas]

Thanks A Lot for that - its a lot clearer now.

[menawollas]

Apart from the fact that I now get INVALID_USERNAME_OR_PASSWORD (even though I can see they are correct) and have locked my account....

[davecon]

Hi menawollas
Thats because of the incorrect attempts
I think you only get three!
Same happened to me
Get them on Twitter
https://twitter.com/BetfairHelpdesk
They will do it straight away for you
You dont have to include your UserName and Password in the request as well (If that is what you are doig of course)
That is already in the URL now
You dont need the App Key either as its optional

I dont know what you are sending but it has to be an HttpWebRequest and not an HttpRequest
See my vbnet code here and see if that helps
https://forum.bdp.betfair.com/showpo...8&postcount=17
Dave

Apart from the fact that I now get INVALID_USERNAME_OR_PASSWORD (even though I can see they are correct) and have locked my account....

[menawollas]

Thanks for the info, but I am still a bit challenged here.

The request I am passing is via an MSXML2.XMLHTTP object - as used in the sample spreadsheet app provided by betfair.

Its obviously partly working - as it recognises mu username and locks me out !

But it is not accepting the valid password (and it definitely is valid, I changed it to something very simple to ensure this).

so the process is

    strURL = "https://identitysso.betfair.com/api/login/?username=USERNAME&password=PASSWORD1"
    strKey = "MYKEY"
           

    Dim xhr
    Set xhr = CreateObject("MSXML2.XMLHTTP")
  
    With xhr
        .Open "POST", strURL, False     
   .setRequestHeader "X-Application", strKey                
        .setRequestHeader "Accept", "application/json"
    End With
     
    
    xhr.send
    SendRequest = xhr.responseText

Looking at your class, I'm not sure that translates to VBA, unless I am missing some references.

I appreciate you might not be able to help with the way I am doing it, but thanks for your direction so far.

[davecon]

Hi
Yep that should work ok

Don't think I'm being silly but you are using the Username and Password as Strings as In (example vba code)

Dim strUserName as String
Dim strPassword as String
Dim strURL as String

strUserName = "MickyMouse"
strPassword = "SayCheese"

'Note the double & so it is & "&password="

strURL = "https://identitysso.betfair.com/api/login/?username=" & strUserName & "&password=" & strPassword

The rest of your code seems fine
Best I can do I'm afraid

Dave

Thanks for the info, but I am still a bit challenged here.

The request I am passing is via an MSXML2.XMLHTTP object - as used in the sample spreadsheet app provided by betfair.

Its obviously partly working - as it recognises mu username and locks me out !

But it is not accepting the valid password (and it definitely is valid, I changed it to something very simple to ensure this).

so the process is

    strURL = "https://identitysso.betfair.com/api/login/?username=USERNAME&password=PASSWORD1"
    strKey = "MYKEY"
           

    Dim xhr
    Set xhr = CreateObject("MSXML2.XMLHTTP")
  
    With xhr
        .Open "POST", strURL, False     
   .setRequestHeader "X-Application", strKey                
        .setRequestHeader "Accept", "application/json"
    End With
     
    
    xhr.send
    SendRequest = xhr.responseText

Looking at your class, I'm not sure that translates to VBA, unless I am missing some references.

I appreciate you might not be able to help with the way I am doing it, but thanks for your direction so far.

[menawollas]

THANKYOU

It was indeed an errant ampersand

[menawollas]

PS

I wasn't using strings initially, I just put the complete URL in quotes - possible I should have escaped the ampersand.

But I have a token now - so thanks a lot.

[davecon]

Yep its a pain URL
If you wanted to do it all as a string you would need to use Double/Double quotes as an escape character

Dim strURL = "https://identitysso.betfair.com/api/login/?username=""USERNAME"" &password=""PASSWORD1"""

Glad you got sorted - The Login process as gave a lot of us much pain lol

Dave

THANKYOU

It was indeed an errant ampersand