Probably took on more than my current knowledge. Read a few threads here and did searches and read other sites. Have gotten close, still missing something(s) since haven't been able to get the code fully functioning. I had some help setting up the account with someone from Procore API support, but seems like they don't have experience or examples with VBA and Access. They gave me a curl statement and pointed me to the documentation and recommended to construct the API call using that method, which has examples in Node.js, Python3 Curl and C#.
If I open a windows command prompt, I can paste the curl statement they provided and although it generates an error, it does provide the access_token, which I have deleted from the code below and replaced with the terms in UPPER CASE as place holders.
I then tried to modify it to get the Access token and it returned error message, but did not provide the token unlike the curl statement above which despite the error provided the token.
Since I currently can't seem to get the vba working, I copy the token from the command prompt and can use it in the following code, which I based on one of the sites I found at https://forum.asana.com/t/excel-vba-api-token-authentication/49943/9.
The other issue besides the token is trying to do filters, in this example for a date range. The commented out stUrl line as written, when I tried to use it, doesn't get an error message, it still returns all records.
If I open a windows command prompt, I can paste the curl statement they provided and although it generates an error, it does provide the access_token, which I have deleted from the code below and replaced with the terms in UPPER CASE as place holders.
Code:
H:\>curl -F grant_type=client_credentials \ -F client_id=THIS IS CLIENTID \ -F client_secret=THIS IS SECRET -X POST https://api.procore.com/oauth/token
curl: (3) URL using bad/illegal format or missing URL
curl: (3) URL using bad/illegal format or missing URL
{"access_token":"THIS IS THE RETURN TOKEN","token_type":"Bearer","expires_in":7200,"created_at":1685118709}
H:\>
I then tried to modify it to get the Access token and it returned error message, but did not provide the token unlike the curl statement above which despite the error provided the token.
Code:
Sub GetAuthKey()
'https://forum.asana.com/t/excel-vba-api-token-authentication/49943/9
Dim Request As Object
Dim authKey As String
Dim stUrl As String
Dim response As String
Set Request = CreateObject("MSXML2.XMLHTTP")
stUrl = "https://api.procore.com/oauth/token?client_id=" & gCLIENTID & "client_secret=" & gSECRET
With Request
.Open "POST", stUrl, False
.SetRequestHeader "Content-type", "application/json"
.send
End With
response = Request.responseText
MsgBox response
End Sub
Since I currently can't seem to get the vba working, I copy the token from the command prompt and can use it in the following code, which I based on one of the sites I found at https://forum.asana.com/t/excel-vba-api-token-authentication/49943/9.
Code:
Sub ListRFIS()
'https://forum.asana.com/t/excel-vba-api-token-authentication/49943/9
Dim Request As Object ', Json As Dictionary
'Dim sht As Worksheet
Dim authKey As String
Dim stUrl As String
Dim response As String
authKey = gACCESS_TOKEN 'the token copied from the command prompt
'Set sht = Sheet1
'"https://app.asana.com/api/1.0/users/me"
stUrl = "https://api.procore.com/rest/v1.0/projects/999111/rfis"
' stUrl = "https://api.procore.com/rest/v1.0/projects/999111/rfis?[created_at]=2023-05-15..2023-05-15"
Set Request = CreateObject("MSXML2.XMLHTTP")
With Request
.Open "GET", stUrl, False
.SetRequestHeader "Authorization", "Bearer " & authKey
.send
End With
response = Request.responseText
MsgBox response
End Sub
The other issue besides the token is trying to do filters, in this example for a date range. The commented out stUrl line as written, when I tried to use it, doesn't get an error message, it still returns all records.