How to implement in VBA a REST(ful) API for a moodle Database?

Question

I am trying to handle moodle data from our schools MS-Access database using VBA-code to post xml.objects that I also successfully used for an API communicating with Telegram (the messenger service).

I tried to implement the following code from an example for using the RESTful-API into my VBA-code:

curl -X POST \ -H "Content-Type: application/x-www-form-urlencoded" \ -H "Accept: application/json" \ -H 'Authorization: {token}' \ -d'options[ids][0]=6' \ "https://localhost/webservice/restful/server.php/core_course_get_courses"

This is how my VBA-code looks like:

Private Sub btnTestMoodleApi_Click()
Dim objRequest As Object
Dim strResult As String
Dim strPostData As String
Dim strURL As String
Dim strToken As String

Set objRequest = CreateObject("MSXML2.XMLHTTP")

strURL = xxx

strToken = xxx

strPostData = "options[ids][0]=432"
With objRequest
  .Open "POST", strURL & "/webservice/restful/server.php?wstoken={" & strToken & "}/core_course_get_courses"
  .setRequestHeader "Content-Type", " application/x-www-form-urlencoded"
  .setRequestHeader "Accept", "application/json"
  .setRequestHeader "Authorization", "{strToken}"
  .Send (strPostData)
strResult = .responseText
Debug.Print strResult
End With
End Sub

The error I get in MS-Access is rather useless to me (also changing some aspects as described below did not change the error message):

" -runtime error 2147483638: The data necessary to complete this operation is not yet available

I suspect the following error sources:

a) I thought that "-H" means header, but the ".setRequestHeader" method just accepts one variable and value. I guessed maybe I can use it several times. But I am not sure if that works or how else I could declare a header in a xml.object.

b) I guess that "-d" means data, I had no idea what to do with it, thus I put into the .send() method. I think that is where the html body goes. I could be utterly wrong...

c) I nested the token for my API into the URL, because I saw it like this in another example. However the original instructions for the moodle-plugin do not have the token in the URL (only in the Authorization Header). I tried both ways, it did not work either way...

I would be really glad if someone with experience in vba could help me, how to implement API instructions into the vba code or at least point me in the right direction. Actually I do not really need this particular core function but the more complex ones to create courses etc. But I thought it was best to start with an easy example as I don't know much about API/ xml/ php/ html etc..

Thanks for reading

Arndt David

Answer 1

If the data is not yet available it seems like a loop to wait for results works

While objRequest.readyState <> 4
DoEvents
Wend

Besides that access to the webservice works from vba. Another problem is the implementation of the Authorization Header which is probably a formatting problem that I will clearify in another post.