Convert cURL command that executes REST API to MS Access VBA

unknown

I was able to get the REST API command to work using cURL within PowerShell, but we want to save the results in an MS Access Database, which means if we can execute the REST API command within MS Access VBA it'll be easier to save into the Database.  The online documentation shows how to execute the REST API commands in several languages, but not in VBA. Please don't just point me to googled VBA pages.  I've spent many hours googling and reading and been unable to get any examples to work.  If you can, please provide the VBA code that will work.

The REST API commands were taken from: https://developer.atlassian.com/cloud/confluence/rest/v1/api-group-group/#api-wiki-rest-api-group-groupname-get 

Below are the REST API commands I am able to execute within PowerShell using cURL:

cURL.exe --request GET --Url 'https://xxxxxxxx.atlassian.net/wiki/rest/api/group/member?name=confluence-users&shouldReturnTotalSize=true' --user 'myemailaddress:mytoken' --header 'Accept: application/json'

cURL.exe --request GET --Url 'https://xxxxxxx.atlassian.net/wiki/rest/api/group/member?next=true&name=confluence-users&limit=200&start=200&shouldReturnTotalSize=true' --user 'myemailaddress:mytoken' --header 'Accept: application/json'

cURL.exe --request DELETE --url 'https://xxxxxxx.atlassian.net/wiki/rest/api/group/user?accountId=theusersaccountid&name=confluence-users' --user 'myemailaddress:mytoken' --header 'Accept: application/json'

I have tried the following VBA code, but the status it returns is: 403, Forbidden, "Current user not permitted to use Confluence"

Dim objHTTP As Object
Dim sUrl As String
Dim sResponseText As String
Dim sStatus As String
Dim sStatusText As String

sUrl = "https://xxxxxxx.atlassian.net/wiki/rest/api/group/member?name=confluence-users&shouldReturnTotalSize=true"

'Set objHTTP = CreateObject("MSXML2.XMLHTTP")

'Set objHTTP = New MSXML2.XMLHTTP60
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")

With objHTTP
    .Open "GET", sUrl, False
    .SetCredentials "myemailaddress", "mytoken", 0
    .setRequestHeader "Accept", "application/json"
    .send
End With

sResponseText = objHTTP.responseText
sStatus = objHTTP.status
sStatusText = objHTTP.statusText

unknown

Vinicius Carvalho21/Jul/23 03:07 AM
Dear Roy,

I hope this message finds you well. My name is Vinicius, and I am the engineer assigned to assist you with this ticket.

We understand that extensive guidance can sometimes be overwhelming , and our goal here at the support team is to provide efficient and effective solutions to your concerns. I apologize if you have had any negative experiences in the past.

Upon examining your case, I sought to better understand the issue with your endpoint request. I'd like to first note that VBA is not a component of our product. However, I am more than willing to go the extra mile and help you integrate this third-party application with our API.

After reviewing the documentation and observing that the request response resulted in a 403 error, I replicated your request in a local test instance and encountered the same issue. Then I checked the authentication and according to the Basic Authentication specifications in HTTP RFC7617, the user/password must be encoded in base64.

Although I initially attempted to utilize an external library for encoding, it did not work well with VBA(the long size of the token can be the reason for not encoding the base64 properly). Instead, I created a custom function to perform this task. You can find the complete script below, which can be tailored to your specific requirements. Please note that the request output populates the first three rows of the Excel document; you may need to adjust this according to your desired logic.

Sub MakeGETRequestWithAuthorization()
Dim objHTTP As Object
Dim sUrl As String
Dim sResponseText As String
Dim sStatus As String
Dim sStatusText As String
sUrl = "https://xxxxxxxxxxxxx.atlassian.net/wiki/rest/api/group"

Dim email As String: email = Base64Encode("youremail" & ":")
Dim token As String: token = Base64Encode("TokenContent")

Dim authHeader As String
authHeader = "Basic " & email & token

Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")

With objHTTP
.Open "GET", sUrl, FALSE
.setRequestHeader "Authorization", authHeader
.setRequestHeader "Accept", "application/json"
.send
End With

sResponseText = objHTTP.responseText
sStatus = objHTTP.Status
sStatusText = objHTTP.StatusText
Range("A1").Value = sResponseText
Range("A2").Value = sStatus
Range("A3").Value = sStatusText
End

SubFunction Base64Encode(sText As String) As String
Const Base64Table As String = |ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"

Dim arrData() As Byte
arrData = StrConv(sText, vbFromUnicode)

Dim i As Long, j As Long
Dim byGroup(3) As Byte
Dim result As String
Dim padding As String

For i = 1 To (UBound(arrData) + 1) Step 3
padding = IIf(i + 2 > UBound(arrData) + 1, IIf(i + 1 > UBound(arrData) + 1, "==", "="), "")
byGroup(0) = (arrData(i - 1) And 252) \ 4
byGroup(1) = (arrData(i - 1) And 3) * 16 + (arrData(i) And 240) \ 16
byGroup(2) = (arrData(i) And 15) * 4 + (arrData(i + 1) And 192) \ 64
byGroup(3) = arrData(i + 1) And 63

For j = 0 To 3
result = result & Mid(Base64Table, byGroup(j) + 1, 1)
Next j

result = result & padding
Next i

Base64Encode = result
End Function
:check_mark: Please note that also the indentation could be a little different from your code, so feel free to adapt locally.

So please, take a look at the function and check if works for you, just to remember, this case isn't a daily basis for us at Atlassian Support , if the solution that I developed don't solve, would be nice to have another perspective from an engineer specialist in the VBA role, considering that the curl command and the other programming languages specified in our API docs converts to base64 natively.

Should you encounter any further issues or have any questions, please do not hesitate to reach out, it will be a pleasure to provide the assistance you require and help you find the solutions you need. :slight_smile:

Best Regards,
Vinicius Carvalho
Senior Support Engineer | Confluence Cloud