POST Requests To Epicor API from Excel

Kinetic ERP
, , ,
1

Has anyone had any experiance with Adding REST GET/POST Requests to work from Inside of Excel?

We have a Excel Document that Production is planning to use to update some UD fields on the JobOper Table. There is a pressing need to create a button that after the data as been entered can be clicked and update epicor directly.

What I am looking to do is call the API to either update a BAQ, or update the Job itself via calls to the Business Object. I am just having a hard time getting things conected to Excel

I found this video, but this only Gets data, and it also only uses the V1 version of the API, so there is no API-Key

2

Excel does not support API keys for odata queries. Maybe you could accomplish your goal with VBA code, but that creates a whole new set of problems…

3

Thank you, I will explore that route.

Was hoping it would be a bit more straightforward, but Ive already built a Blazor App using the Epicor API, so I can probobly figure it out.

4

It’s just an HTTP request, no problem. It is technically possible to POST from power query, but, yeah, don’t do that to your poor Epicor instance. :slight_smile:

I’ve done this to death in VBA as recently as a few years ago. Not Epicor-specific but I’ll see if I can scrape up some snippets when I get a minute. The request itself is just a matter of sorting through Epicor’s expectations for syntax and parameters.

VBA gets a bad rep but I’ve always thought that has more to do with being stuck in its awful IDE. The language itself is clunky and awkward sometimes, such is life.

5

Have you looked into using an Updateable BAQ/Dashboard.
Have them query for what they need to see, make the changes in the dashboard and then save.
Keep everything in the ERP.

6

This doesn’t seem like a very good idea, if the spreadsheet is too complex to mirror with an Updateable dashboard, a custom screen in Epicor would still be better.

Of course, I am familiar with being ordered to do something in an unwise manner, so if you have no choice I understand.

7

I have a rest helper for Excel. We do our initial cost adjustment on MTO parts with a crazy costing sheet and I push those into our Std Cost values using REST from Excel VBA. Could load values from a BAQ into excel if you wanted too. I wouldn’t do anything too crazy with it, but it’s very possible. I have a JSON convert module as well so my life wasn’t hell.

8

I copied a whole thing. It’s kinda Trello-specific, converting to Epicor is a matter of rearranging syntax. The relevant references and methods are there except JSON parsing. Pardon the mess, this was part of a mad coding dash in Feb 2000 because reasons and it just needed to work.

BTW GET in power query is great and I do highly recommend that. POST is a whole 'nother can of worms, aka, DIY data integrity because tons of that lives in the client and REST bypasses the client. </you’ve been warned>

Public Function WebRequestBuilder(resource As String, Subresources As String, Parameters As Dictionary) As String
    WebRequestBuilder = baseURL
    WebRequestBuilder = WebRequestBuilder & resource
    If Subresources <> "" Then
        WebRequestBuilder = WebRequestBuilder & Subresources & "/"
    End If
    WebRequestBuilder = WebRequestBuilder & "?"
    If Parameters.count > 0 Then
        Dim v As Variant
        Dim params As String '%2E
        For Each v In Parameters.Keys
            params = Application.WorksheetFunction.EncodeURL(CStr(Parameters(v)))
            params = Replace(params, ".", "%2E")
            WebRequestBuilder = WebRequestBuilder & CStr(v) & "=" & params & "&"
        Next v
    Else
        WebRequestBuilder = WebRequestBuilder & "&"
    End If
    WebRequestBuilder = WebRequestBuilder & "key=" & TrelloKey & "&"
    WebRequestBuilder = WebRequestBuilder & "token=" & TrelloToken
End Function


Public Function WebRequest(HTTPMethod As String, ByRef url As String) As Variant
    'requestLimit
    Sleep 100
    
    Dim result As String
    Dim winHTTPRequest As WinHttp.winHTTPRequest
    Set winHTTPRequest = New winHTTPRequest
    winHTTPRequest.Open HTTPMethod, url, False
    winHTTPRequest.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    '   TODO: implement timeouts with feedback to the user
    winHTTPRequest.Send
    WebRequest = winHTTPRequest.ResponseText
    If winHTTPRequest.status = 503 Then
        MsgBox "503 Error - Webrequest could not connect" & Chr(10) & "Exiting..."
        End
    End If
    If winHTTPRequest.status >= 300 Then
        Dim httpStatus As Long
        httpStatus = winHTTPRequest.status
        Dim httpStatusText As String
        httpStatusText = winHTTPRequest.StatusText
        
        LogHandler.AddLog "WebRequest", "Method=" & HTTPMethod & ";Status=" & CStr(winHTTPRequest.status) & ";StatusText=" & winHTTPRequest.StatusText
        LogHandler.AddLog "", "URL: " & url
        LogHandler.AddLog "", ""
    End If
End Function
9

I got this working for our purposes. This function is called for each row in the master document and satisfied managments requests.

Function UpdateEpicorWithBAQ(row As Integer)

liveKey = "YOUR LIVE KEY"
pilotKey = "YOUR PILOT KEY"


Dim JobNum As String
Dim OPCode As Integer
'Dim DueDate As Date
'Dim StartDate As Date
Dim SUHrs As Integer
'Dim SUStartDate As String
'Dim ProdStart As Date
Dim SchSeq As Integer

SchSeq = ThisWorkbook.Sheets("Schedule").Cells(row, 2)
JobNum = ThisWorkbook.Sheets("Schedule").Cells(row, 5)
OPCode = ThisWorkbook.Sheets("Schedule").Cells(row, 6)
'DueDate = ThisWorkbook.Sheets("Schedule").Cells(row, 9)
SUHrs = ThisWorkbook.Sheets("Schedule").Cells(row, 13)
'StartDate = ThisWorkbook.Sheets("Schedule").Cells(row, 16)
SUStart = ThisWorkbook.Sheets("Schedule").Cells(row, 17)
'ProdStart = ThisWorkbook.Sheets("Schedule").Cells(row, 18)


Dim filter As String
filter = "%24filter=JobOper_JobNum%20eq%20%27" & JobNum & "%27%20and%20JobOper_OprSeq%20eq%20" & OPCode

token = GetToken()

Dim req As New MSXML2.XMLHTTP60
Dim ReqUrl As String
Dim reqBody As String
Dim Response As String

ReqUrl = "https://YourPath/BaqSvc/TMC_VBAUpdate/Data?" + filter
req.Open "Get", ReqUrl, False

req.setRequestHeader "X-API-Key", liveKey
req.setRequestHeader "currentCompany", YOUR COMPANY
req.setRequestHeader "Authorization", "Bearer " + token
req.setRequestHeader "Content-Type", "application/json"

req.send

If req.Status <> 200 Then
    MsgBox req.Status & " UpdateBAQ  - " & req.responseText
    Exit Function
End If
'MsgBox req.Status & " UpdateBAQ  - " & req.responseText



' POST REQUEST
Dim postReq As New MSXML2.XMLHTTP60
Dim postReqURL As String
Dim postBody As String


postReqURL = "https://YourPath/BaqSvc/TMC_VBAUpdate/Data"
postReq.Open "Patch", postReqURL, False

postReq.setRequestHeader "X-API-Key", liveKey
postReq.setRequestHeader "currentCompany", YOUR COMPANY
postReq.setRequestHeader "Authorization", "Bearer " + token
postReq.setRequestHeader "Content-Type", "application/json"


' Convert returnObj to ds for post request
    Set postJobObj = JsonConverter.ParseJson(req.responseText)
    
    postJobObj("value")(1)("JobOper_UD_SchSeq_c") = SchSeq

    postJobObj("value")(1)("RowMod") = "U"
    Dim newStr As String

newStr = JsonConverter.ConvertToJson(postJobObj("value")(1))

postReq.send newStr

If postReq.Status <> 200 Then
    
    MsgBox postReq.Status & " PostUPDate Failed  - " & postReq.responseText
    ThisWorkbook.Sheets("Schedule").Cells(row, 1).Interior.Color = RGB(255, 0, 0)
    Exit Function
    
End If
 ThisWorkbook.Sheets("Schedule").Cells(row, 1).Interior.Color = RGB(0, 255, 0)


End Function