VBA OAuth2 to CRM System

  • hi guys, i try to build a connection to my company CRM System. I need to get contact datas out of the CRM to Excel and need to post offers with different positions into the CRM System.

    the authentification is OAuth2. I'm not realy into VBA and need help with the code.
    this is the API Docu: http://docs.bexio.com/oauth/oauth/

    And this is my code until now:

    Public Sub gettoken()

    Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
    myURL = "https://office.bexio.com/oauth/authorize?client_id=7753313026.apps.bexio.com&redirect_uri=https://office.bexio.com/oauth/authorize/end&scope=general contact_show kb_offer_edit&state=xyz"

    If winHttpReq.Open("POST", myURL, False) = S_OK Then
    MsgBox ("Open erfolgreich")
    End If

    Dim getTokenUrl As String
    getTokenUrl = "https://office.bexio.com/oauth/access_token"

    Dim getTokenBody As String
    getTokenBody = "code=" & code & _
    "&redirect_uri=https://office.bexio.com/oauth/authorize/end" & _
    "&client_id=7753313026.apps.bexio.com" & _

    Dim Http As MSXML2.XMLHTTP60
    Set Http = CreateObject("MSXML2.XMLHTTP.6.0")

    With Http
    .Open "POST", getTokenUrl, False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    .send (getTokenBody)
    End With
    Do While Http.ReadyState <> 4

    MsgBox (Http.responseText)

    Debug.Print Http.responseText

    End Sub

    Would be great if somebody can complete my code. I can't extract the code from the system to finaly get the token.


  • administrators

    Hello @Axim, Handling OAuth in VBA is a complicated process. Also, Bexio API doesn't offer any service accounts for authentication so manual login will be required.

    The best way to handle your use case in VBA would be -

    • Manually get an access token from browser.
    • Then make POST requests to API using your access token.
    • Access token is valid only for 1 hour so you have to refresh the token manually.

    This is not a simple process if you are new to coding. You should probably hire a freelancer to get this done. If you are hiring a freelancer then go for a better solution to handle authentication process using a web service.

  • Hello,

    You might like this article about CRM for eCommerce

    Good luck!

  • I don't have any account there so I could not check. However, try this. It should work.

    Sub GetLoggedIn()
        Const Url$ = "https://my.bexio.com/simplesaml/module.php/core/loginuserpassorg.php?client_id=7753313026.apps.bexio.com"
        Dim Http As New XMLHTTP60, HTML As New HTMLDocument
        Dim auth As Object, authval$, payload$
        With Http
            .Open "GET", Url, False
            HTML.body.innerHTML = .responseText
        End With
        Set auth = HTML.querySelector("form[name='f'] input[name='AuthState']")
        authval = WorksheetFunction.EncodeURL(auth.getAttribute("value"))
        'put your username and password in the following line replacing xxxx
        'make sure to replace "@" with "%40", as in [email protected] with something%40gmail.com
        payload = "username=xxxxxxxxx&password=xxxxxxxxx&AuthState=" & authval
        With Http
            .Open "POST", Url, False
            .setRequestHeader "Content-type", "application/x-www-form-urlencoded"
            .send payload
        End With
        MsgBox Http.responseText
    End Sub

  • This post is deleted!

  • Hello,

    You can also have a look at Strategic Use of CRM For eCommerce Start-ups’ Growth

Log in to reply

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.