How to generate new oauth-2 token of MT cloud to call M3 Rest Apis in VBA

unknown

Hi,

We have a customer who will be entering the Customer orders in M3 through an Excel utility.

How to generate new oauth-2 token of MT cloud to call M3 Rest Apis in VBA for the Excel utility.

If anyone has worked on this please let me know how to do it.

Thank you

Find more posts tagged with

API Usage

Accepted answers

steve mcenany

Here is the VB to get a token - I used this in the past, I have since moved to a scheduled Python job to keep a token active and just reference the file as needed in Excel. That way the ionapi passwords are not redistributed if someone shares the excel file. If you are a Python person and need it, I'll add that here as well..

Function getloggedin() As String
Dim Tenant As String
'________________________________________________________
' Update the Tenant below and in each sURL value update the tenant <Your Tenant>
'________________________________________________________

Dim Http As New MSXML2.XMLHTTP60, HTML As New HTMLDocument, Item As Object
Dim jsonText As String
Dim jsonObject As Object

With Http
.Open "GET", "mingle-sso.inforcloudsuite.com:443/<Your Tenant>/as/token.oauth2", False
.send
HTML.Body.innerHTML = .responseText
End With

Dim ci As String
Dim cs As String
Dim SAAK As String
Dim SASK As String

'________________________________________________________
' Update these values from the ion API file

ci = ""
cs = ""
SAAK = ""
SASK = ""
'________________________________________________________

payload = "grant_type=password&client_id=" + ci + "&client_secret=" + cs + "&username=" + SAAK + "&password=" + SASK + ""

With Http
.Open "POST", "mingle-sso.inforcloudsuite.com:443/<your tenant>/as/token.oauth2", False
.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
.send payload
jsonText = Http.responseText
Set jsonObject = JsonConverter.ParseJson(jsonText)
End With

getloggedin = "Bearer " + jsonObject("access_token")

End Function

Python 3.8 -- This saves a token in a file on your PC.

Put the py file and the ionapi in the same directory and run it. You can create a bat file to run the python file every couple hours to keep a fresh token. Then in Excel you can just use the text from the file as a token. --- This is much faster in Excel and less load on the InforOS server.

import requests,json

with open('ionapi.json') as f:
ionapi = json.load(f)

token_url = ionapi['pu'] + ionapi['ot']

data = {'grant_type': 'password','username': ionapi['saak'], 'password': ionapi['sask']}

access_token_response = requests.post(token_url, data=data, verify=False, allow_redirects=False, auth=(ionapi['ci'], ionapi['cs']))

tokens = json.loads(access_token_response.text)

token = tokens['access_token']

f = open("c:/bearer-token/inforos.token", "w")
f.write(token)
f.close()

All comments

antonio-caria

On Youtube search "infor ion oauth-2"

steve mcenany

With Http
.Open "GET", "mingle-sso.inforcloudsuite.com:443/<Your Tenant>/as/token.oauth2", False
.send
HTML.Body.innerHTML = .responseText
End With

getloggedin = "Bearer " + jsonObject("access_token")

End Function

Python 3.8 -- This saves a token in a file on your PC.

import requests,json

with open('ionapi.json') as f:
ionapi = json.load(f)

token_url = ionapi['pu'] + ionapi['ot']

data = {'grant_type': 'password','username': ionapi['saak'], 'password': ionapi['sask']}

access_token_response = requests.post(token_url, data=data, verify=False, allow_redirects=False, auth=(ionapi['ci'], ionapi['cs']))

tokens = json.loads(access_token_response.text)

token = tokens['access_token']

f = open("c:/bearer-token/inforos.token", "w")
f.write(token)
f.close()

unknown

Hi Steve,

This has worked. Thank you very much.

Now the Excel utility is working as per the requirement of Customer

Denis MARETTE

Hi Shashank,

Removing all references to the end-customer context, can you share a very simple example of an excel file in which there is the VBA script to self-identify and then do some basic actions via M3 API calls.
This would be very helpful for me to take my first steps in this field.
Thanks in advance

Rene Bottern

Hi Steve,
I have done similar thing in Excel as well, and it works great, but I have another requirement.
have you been able to generate Token based on current User/Password Credentials instead (this works great in "On Prem" version of ION API, but NOT in MT Cloud!).

I would very much like to see the API Transactions Inserting/Updating Data in M3 registered with the Current User instead of some Generic User used for exporting the credentials.

steve mcenany

I use the Oauth2 method to get the Bearer Token gaining access to the InforOS cloud tenant and then the API calls into the application typically offer a specific user in their calls as well.

Nothing is stopping you from generating additional credentials in Infor for the Oauth handshake that emulate specific users. Just be aware if that user leaves the company or security roles change you could break the integration.

steve mcenany

Just an example that we can all relate to here-- Note this is using the token generator from The Python example above. You can insert the VBA function for getloggedin() above to use it the other way.

This is designed to query the data lake with the V1 of the data lake API. Note I never went back and added the loop for the more than 25000 rows to it. So if you feed it a big query response you will need to handle the pages of data that the API will allow you to loop through and return.

For less then 25000 rows it will work as it's written, using the Python 3.8 code above I'm generating a token to reuse in a directory on my PC. The code looks to sheet "Config" cell B2 for the file path to the token and "Config" B1 for the Tenant -- similar to TENANT_TST

On The config Sheet you can put the query in cell B3

I added a button to run the marco.. You can just go to the developer tab and run it as well.. It will dynamically add the column names and results on a sheet named Query.

Function getloggedin() As String
Dim uncpath As String
Dim token As String

uncpath = Sheets("Config").Cells(2, 2).Value

Open uncpath For Input As #1
Do Until EOF(1)
Line Input #1, textline
token = token & textline
Loop
Close #1
getloggedin = "Bearer " + token
End Function

Sub SendQuerytoLake()
Dim access As String
access = getloggedin()
Dim sURL As String, sHTML As String, sAllPosts As String
Dim oHttp As Object
Dim Body As String
Dim Item As Object
Dim jsonText1 As String
Dim jsonObject1 As Object
Dim jsonObject2 As Object
Dim jsonObject3 As Object
Dim restartrow As String
Dim queryID As String
Dim Count As Integer
restartrow = "1"
Dim tempsql As Variant

Body = Worksheets("Config").Range("B3").Value
'Body = "Select prod, mfgprod from icsp where substring(prod,1,3)='VV '"
'MsgBox (Body)
Set oHttp = CreateObject("MSXML2.XMLHTTP")
sURL = "">mingle-ionapi.inforcloudsuite.com/" + Sheets("Config").Cells(1, 2).Value + "/IONSERVICES/datalakeapi/v1/compass/jobs?resultFormat=application/x-ndjson"
'MsgBox (sURL)
oHttp.Open "POST", sURL, False
oHttp.setRequestHeader "Content-type", "text/plain"
oHttp.setRequestHeader "Authorization", access
oHttp.setRequestHeader "Accept", "application/json"
oHttp.send Body
sHTML = oHttp.responseText
'MsgBox oHttp.responseText
jsonText1 = oHttp.responseText
Set jsonObject1 = JsonConverter.ParseJson(jsonText1)
'MsgBox jsonText1
If (jsonObject1("NoRecords") = "No Records Found") Then
restartrow = ""
End If
queryID = jsonObject1("queryId")
Location = jsonObject1("location")
Status = jsonObject1("status")
'MsgBox (queryID)
Count = 0
While Status = "RUNNING" Or Status = "TRANSFORMING"

Body = ""
Set oHttp = CreateObject("MSXML2.XMLHTTP")
sURL = "">mingle-ionapi.inforcloudsuite.com" + Location
'MsgBox (sURL)
oHttp.Open "GET", sURL, False
oHttp.setRequestHeader "Authorization", access
oHttp.setRequestHeader "Accept", "application/json"
oHttp.send Body
sHTML = oHttp.responseText
jsonText1 = oHttp.responseText
Set jsonObject2 = JsonConverter.ParseJson(jsonText1)
'MsgBox (jsonText1)
Status = jsonObject2("status")
If Status = "FAILED" Then MsgBox ("Error Hint: Don't copy with formating from Compass, put in notepad first to remove the special formatting.")

Application.Wait (Now + TimeValue("0:00:02"))
Count = Count + 1

Wend
Location = jsonObject2("location")
'MsgBox (jsonObject1("columns")(1)("name"))
Set ws = Worksheets("Query")
Sheets("Query").Cells.Clear
Dim i As Integer
For Each Item In jsonObject2("columns")
i = i + 1
ws.Cells(1, i) = Item("name")
Next

Set oHttp = CreateObject("MSXML2.XMLHTTP")
sURL = "">mingle-ionapi.inforcloudsuite.com" + Location
'MsgBox (sURL)
oHttp.Open "GET", sURL, False
oHttp.setRequestHeader "Authorization", access
oHttp.setRequestHeader "Accept", "application/x-ndjson"
oHttp.send Body
sHTML = oHttp.responseText
jsonText1 = "{""data"":[" + oHttp.responseText + "]}"
Set jsonObject3 = JsonConverter.ParseJson(jsonText1)
'MsgBox (jsonText1)
'Status = jsonObject1("status")

Dim j As Long
i = 0
j = 1
For Each Row In jsonObject3("data")
j = j + 1
i = 0
For Each Item In jsonObject2("columns")
i = i + 1
ws.Cells(j, i) = Row(Item("name"))
Next
Next

End Sub

0904080939293831.xls

Rene Bottern

Where do you see "the API calls into the application typically offer a specific user in their calls"?
If this was the case it would resolve my issue, but there is nowhere to be found as far as I can tell?

If you can provide an example of that, it would be MUCH appreciated.

Copyright © 2025 Infor. All rights reserved.