I’m trying to issue a simple GET request via code. I kept getting the error about “The data necessary to complete this operation is not yet available” when I was trying to return CustCnt data, so I tried it against the Currencies table because we currently only work with the US dollar and therefore only have 1 record. I still get that error.
I’m working in VBA (Outlook), have added a reference for …
Microsoft Scripting Runtime
Microsoft WinHTTP Services, version 5.1
… and have tried using both request types ‘ServerXMLHTTP’ and WinHttpRequest. Still to no avail.
Dim objRequest As Object
Set objRequest = CreateObject("MSXML2.ServerXMLHTTP")
’ Dim objRequest As New WinHttpRequest
With objRequest
.Open "GET", strURL, blnAsync
(Note: I have tried with and without these next three lines)
’ .setRequestHeader “Authorization”, “Basic dGt1ZXBwZXJzOmVwaXBhc3MwMiE=”
’ .setRequestHeader “Content-Type”, “application/json”
’ .setRequestHeader “Authorization”, "Bearer " & strToken
.Send
End With
strResponse = objRequest.responseText
Before doing the calls in VBA, I would test them from the API Help Page and/or in the program Postman. That way you can see the expected results before trying to do it with code.
In my experience, I used MSXML2.XMLHTTP to do the VBA calls. Very similar syntax to that MSXML2.ServerXMLHTTP you have shown. I’m not sure of the difference between the two, but I’ve only ever used the MSXML2.XMLHTTP
If I’m ever just reading data from Epicor with REST calls, I generally just create a BAQ and then call that with REST rather than mess with other endpoints.
Thanks for the feedback. I’ll try calling the BAQ. I already have the JSON parser installed. I didn’t include it in what I listed above, but next line of code would be:
Set JsonObj = JsonConverter.ParseJson(strResponse)
It’s easier to tie the code to a folder in Outlook. I suppose I could start there and see about moving it to the online environment when I finish. I’m not totally happy with the limitations of Information Worker for logging (only) outbound emails sent from Outlook, ignoring the fact that at least 50% of emails sent by our sales guys are from their phones, and logs 0% of responses from recipients.
As you probably know, VBA is on the way out. For better or worse, add-ons are now JavaScript. Well, for better I guess because REST calls are much easier.
Here’s a rough outline of the syntax I’ve used to call a baq, I use rest v2, should be similar to v1, you just will have a slightly different URL and you don’t need api keys with v1:
Sub restTest()
'REST Variables
Dim objRequest As Object
Dim baseUrl As String
Dim blnAsync As Boolean
Dim apiKey As String
Dim restResponse As String
'set defaults for REST calls
Set objRequest = CreateObject(“MSXML2.XMLHTTP”)
blnAsync = False
apiKey = “xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”
baseUrl = “https://epiServer.local/Test/api/v2/odata/11111/”
partNum = “example part num, use in URL for BAQ parameters”
Dim strUrl As String
strUrl = baseUrl & “BaqSvc/BAQName/Data?partNum='” & partNum & “'”
With objRequest
.Open "GET", strUrl, blnAsync
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Authorization", "Basic " & EncodeBase64("user:pass")
.setRequestHeader "x-api-key", apiKey
.Send
'spin wheels whilst waiting for response
While objRequest.readyState <> 4
DoEvents
Wend
restResponse = .responseText
End With
End Sub
I had a function to do the base64encoding that isn’t included.
Function EncodeBase64(text)
Dim b
With CreateObject("ADODB.Stream")
.Open: .Type = 2: .Charset = "utf-8"
.WriteText text: .Position = 0: .Type = 1: b = .Read
With CreateObject("Microsoft.XMLDOM").createElement("b64")
.DataType = "bin.base64": .nodeTypedValue = b
EncodeBase64 = Replace(Mid(.text, 5), vbLf, "")
End With
.Close
End With
End Function
MsgBox EncodeBase64("Hello")
