Call REST api from VBA

Epicor ERP 10
1

I am trying to call the rest pi via a VBA macro in Solidworks but I am having some trouble. I can get the call to work in the Epicor rest web interface (https://SERVER/APPSERVER/api/help/methods/Ice.BO.UD02Svc/index#!/Custom_methods/GetaNewUD02). I can also get it to work in a c# console app. But i am forced to use VBA in the soldiworks macro and that is proving difficult.

Here is my code so far:

    Dim jsonStr As String
    jsonStr = "{""ds"":{ ""UD02"": [] }}"

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = "https://SERVER/APPSERVER/api/v1/Ice.BO.UD02Svc/GetaNewUD02"
    objHTTP.SetOption 2, 13056
    objHTTP.Open "POST", URL, False

    objHTTP.setRequestHeader "Content-type", "application/json"
    objHTTP.setRequestHeader "Accept", "application/json"
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.setRequestHeader "Authorization", "Basic " & "user" & ":" & "password"

    objHTTP.send jsonStr

This gives an error with the message “The input is not a valid Base-64 string as it contains a non-base 64 character, more than two padding char”.

I have looked at plenty of posts online and tried many things but I am not getting anywhere.
Things i have tried

  • using a very simple string of “a”
  • various encoding methods found online to convert to byte array, base64 encoding etc

If anyone has any ideas it would be greatly appreciated…

Brett

2

The authorization header needs to be base 64 encoded… You are passing plain “user”:“password”
It should be
base64Encode(“user”:“password”) //Note not an actual function… just an example. looks like this isn’t built in to VBA… here’s an implementation

1 Like
3

Yep that was it. I was trying to encode the content instead of the auth string… I used one of the encoder functions i had and it worked.

here is the working code to make the rest call

Dim jsonStr As String
jsonStr = "{""ds"":{ ""UD02"": [] }}"

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = "https://SERVER/APPSERVER/api/v1/Ice.BO.UD02Svc/GetaNewUD02"
objHTTP.SetOption 2, 13056  ' To ignore self signed cert errors
objHTTP.Open "POST", URL, False

objHTTP.setRequestHeader "Content-type", "application/json"
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
Dim auth As String
auth = "Basic " & TextBase64Encode("user" & ":" & "password", "ASCII")
objHTTP.setRequestHeader "Authorization", auth


objHTTP.send jsonStr

And the TextBase64Encode from utf 8 - Encoding special chracters to Base64 in Excel VBA - Stack Overflow

Function TextBase64Encode(strText, strCharset)

    Dim arrBytes

    With CreateObject("ADODB.Stream")
        .Type = 2 ' adTypeText
        .Open
        .Charset = strCharset
        .WriteText strText
        .Position = 0
        .Type = 1 ' adTypeBinary
        arrBytes = .Read
        .Close
    End With

    With CreateObject("Microsoft.XMLDOM").createElement("tmp")
        .dataType = "bin.base64"
        .nodeTypedValue = arrBytes
        TextBase64Encode = Replace(Replace(.Text, vbCr, ""), vbLf, "")
    End With

End Function
4 Likes