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")
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")
.Open "POST", getTokenUrl, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
Do While Http.ReadyState <> 4
Would be great if somebody can complete my code. I can't extract the code from the system to finaly get the token.
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.
You might like this article about CRM for eCommerce
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 .send 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!
You can also have a look at Strategic Use of CRM For eCommerce Start-ups’ Growth