While Infor has made tremendous progress over the last decade in making M3 more user-friendly and opening up access to interfaces and app development through the use of APIs (Application Programming Interfaces), pretty much every M3 user ends up doing analytical work with data in Excel.
Then, the challenge becomes how to get that data back into M3.
Sure, there is limited ability to perform mass updates in CRS800, but often users come up with a process where they get information out of M3, manipulate it however they like in Excel, and then run into a roadblock trying to get the data back into the system. Nobody wants to manually key in hundreds or even thousands of records, but waiting for IT to develop a robust interface can take months.
In my personal experience, I have tackled this issue at pretty much every ERP installation I’ve worked with. It could be as simple as “we need to change the buyer assigned to this list of suppliers” to “we need to add X weeks to the lead times on parts from Supplier Y for a few months, then change it back later on”.
So what to do?
Excel VBA + Infor M3 API to the rescue
Excel is easily extendable by using VBA (Visual Basic for Applications) code behind the scenes, creating macros that call M3 APIs to get data out of the system or load it back in. One can even perform many common business process tasks by stringing multiple API calls together.
The benefit there is that you are using ERP functions to process your data just as if a user was working within the system, so all of your business logic is still being respected. You aren’t simply slamming records into a database, you are following established procedures, but with the ability to do so in an automated way.
So how to connect Excel to M3? In the past, you would download the M3 API Toolkit from Infor, and install software on each computer which would connect to M3, which was a hassle. Installing .dll files and registering them within Windows could be a real pain if not done correctly. And especially for newer, multi-tenant CloudSuite implementations of M3, the old toolkit really isn’t an option anyway since you’re not able to create socket connections to the M3 server.
Using REST API With Infor M3
Thankfully, we can use REST APIs to make those connections instead. With a REST API call, the communication functions much like your web browser following a link – you sent a string to a server in a URL, and get back information in return.
There is no additional software to install, and when the user makes their first API call they will be prompted to provide a user ID and password, just as if they were logging into M3. For a primer on calling M3 APIs using REST, check out this blog post over at M3 Ideas.
The idea here is that within Excel you will create a URL that is used to make the call to M3. The URL is a web address just like you see at the top of your browser, but it will refer to the M3 environment and API program you are accessing, and include any parameters you need to pass.
For example, if you want to get the item master information for item number ABC123, your URL might look like this:
https://[server]:[port]/m3api-rest/execute/MMS200MI/Get?ITNO=ABC123
The [server]:[port] portion will relate to your individual implementation (each environment will have its own values there), and you can see where the API program (MMS200MI) and transaction (Get) fit into the mix. From there, you append parameters to the end of the URL, such as “&CONO=123” for adding Company Number = 123 as parameter.
Calling REST API from Excel VBA
OK, so we know we have to create a REST API call against M3, but how do we do that from Excel using VBA? First off, you’ll want to make sure you add the Microsoft XML v6.0 library to your VBA references, like so. This gives you the necessary objects, methods and properties to make the REST API call and deal with the XML you will get back from M3:
For an example of how the VBA code looks when making the API call, take a look at the following. At first we set up the objects for the API call (objRequest) and the response we receive back from M3 (xdoc), and then make the call in the line that begins with “.Open”. The variable strURL is a string with the URL defined as we’ve noted above.
Set objRequest = CreateObject("MSXML2.XMLHTTP")
Set xdoc = CreateObject("MSXML2.DOMDocument")
xdoc.async = False
xdoc.validateOnParse = False
blnAsync = True
With objRequest
.Open "GET", strURL, blnAsync
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "DateTime", Now
.setRequestHeader "Cache-control", "no-cache, no-store"
.setRequestHeader "pragma", "no-cache"
.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
.Send
While objRequest.readyState <> 4
DoEvents
Wend
xdoc.LoadXML (.ResponseText)
End With
What you can then work with in Excel VBA is the xdoc object, which contains the XML response from M3.
That’s enough to get you started, I will follow up with more posts covering how to deal with the output received from M3, along with a few tips and tricks I’ve learned along the way while creating these Excel-based tools that interact with M3.
Did you enjoy this post? Go ahead and connect with me on LinkedIn or email me (dirkhoag at gmail), making sure to include a message so I know how you found me! If you could use some assistance with Infor M3 (functional or technical), I am available for remote, part-time consulting. Just send a message for further details!
