I'm trying to connect to the MS Graph with VBA. I've managed to do so with PowerShell with the exact same App API but when trying with VBA it doesn't seem to want to work.
This is the code:
Function GetToken()
Dim tokenReq As New MSXML2.XMLHTTP60
Dim userReq As New MSXML2.XMLHTTP60
Dim urltoken As String
Dim accessToken As String
Dim Json As Object
Dim Json2 As Object
'Token URL
urltoken = "https://login.microsoftonline.com/<MyTenantID>/oauth2/v2.0/token"
'Getting Access Token
tokenReq.Open "POST", urltoken, False
tokenReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
tokenReq.send ("scope=https://graph.microsoft.com/.default&grant_type=client_credentials&client_id=<MyClientID>&client_secret=<MyClientSecret>")
Set Json = JsonConverter.ParseJson(tokenReq.responseText)
accessToken = Json("access_token")
'Graph API Request
userReq.Open "GET", "https://graph.microsoft.com/v1.0/users?", "False"
userReq.setRequestHeader "Authorization", "Bearer <accessToken>" 'This line is Causing Issues
userReq.setRequestHeader "Content-type", "application/json"
userReq.send
MsgBox (userReq.responseText)
End Function
When I run this, I get the message box saying:
{"error":{"code":"InvalidAuthenticationToken","message":"CompactToken parsing failed with error code: 80049217","innerError":{"date" etc...
I've tried to find how to fix this error but I couldn't find anything and tried what I could from other forums and other people running into the same issue with other languages.
From what I've found, I think it's the Authorization Header of the actual API request as I do get the Access Token from the first request but I've tried anything to structure that header correctly, I've attempted to use the token_type from the original request, showing up with the same error.
I'm not that experienced with VBA so any help would be appreciated.
Edit: Probably best to clear that I'm using the App Registration in Azure AD and trying to run this request in Excel.
