Automating many functions in M3 can be achieved through the use of APIs. APIs are defined in MRS001 and should be tested thoroughly using Lawson's MITest tool before you attempt to use them via Excel.
Note that my documentation below is based on Excel 2000, later versions can have slightly different menu names etc, but the functionality is the same.
Add the API dll to Excel
In Excel open the Visual Basic editor and choose Tools, References. Add a reference to the M3 API dll
In Excel open the Visual Basic editor and choose Tools, References. Add a reference to the M3 API dll
Open a connection to M3
In your VB code add the following:
'Init M3 API variables
Dim Sock As New MvxSockXDim transStr As String
Dim Result As String
Dim rc As Long
'Connect to M3. Note that servername, port, username, password
'and apiprogram are the same values you would have used in MITest
rc = Sock.MvxSockConnect(servername, port, username, password, apiprogram, "")
'Check for connection error
If rc <> 0 Then Sock.MvxSockShowLastError ("Error while Initializing")
Build the API command string
When you used MITest to test the API command you wanted to use you will have noted a fixed-with string was sent to M3. Here we will build this string.
'Obtain the lines from a PDS090 Matrix
transStr = Space(500)
Mid(transStr, 1) = "ListLine"
Mid(transStr, 16) = "100" 'Company
Mid(transStr, 19) = "TEST" 'Matrix
Mid(transStr, 1) = "ListLine"
Mid(transStr, 16) = "100" 'Company
Mid(transStr, 19) = "TEST" 'Matrix
Send the command to M3 and process the result
Here we send the command we built above to M3 and look at the result
'Send API command to M3
rc = Sock.MvxSockTrans(transStr, Result)
'If the command resulted in an error, exist showing that error message
'If the command resulted in an error, exist showing that error message
If rc <> 0 Then
Sock.MvxSockShowLastError ("")
Exit Sub
End If
Sock.MvxSockShowLastError ("")
Exit Sub
End If
Close the connection to M3 once you are done
Note that you should open a single connection per API and use this as many times in your program before you close it.
'Close connection to M3
Sock.MvxSockClose
Complete examples
I tend to have a Parameters tab where the username, password etc are entered.
The standard layout of our mass-update tools looks like this
The Get Data button links to the GetClothMatrixData_Click script below, the Update Data button links to the UpdateClothMatrixData_Click script below. The Add Data button links to a script similar to the Update Data script. Finally the Clear Spreadsheet script simply removes the data in the spreadsheet.
This example opens a Matrix in PDS090 then gets all the information from that Matrix and returns it to Excel.
Private Sub GetClothMatrixData_Click()Application.Goto ("ClothMatrixDataStart")
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
Dim Sock As New MvxSockX
Dim transStr As String
Dim Result As String
Dim rc As Long
rc = Sock.MvxSockConnect(Sheets("Parameters").Range("Computer").Value, Sheets("Parameters").Range("Port").Value, Sheets("Parameters").Range("Username").Value, Sheets("Parameters").Range("Password").Value, "PDS090MI", "")
If rc <> 0 Then Sock.MvxSockShowLastError ("Error while Initializing")
' Set max number of items returned. Default is 100
rc = Sock.MvxSockTrans("SetLstMaxRec 49999", Result)
Application.Goto ("ClothMatrixDataStart")
ActiveCell.Offset(1, 0).Select
transStr = Space(500)
'List the items in the location
Mid(transStr, 1) = "ListLine"
Mid(transStr, 16) = Sheets("Cloth Matrix").Range("ClothMatrixCompany").Value 'Company
Mid(transStr, 19) = Sheets("Cloth Matrix").Range("ClothMatrix").Value 'Matrix
rc = Sock.MvxSockTrans(transStr, Result)
If rc <> 0 Then
Sock.MvxSockShowLastError ("")
Exit Sub
End If
'If we have a response then enter the loop
If Trim(Mid(Result, 1, 3)) = "REP" Then
Do
ActiveCell.Value = Trim(Mid(Result, 34, 15)) 'Cloth
ActiveCell.Offset(0, 1).Value = Trim(Mid(Result, 24, 10)) 'Valid Date
ActiveCell.Offset(0, 2).Value = Trim(Mid(Result, 214, 15)) 'Result
ActiveCell.Offset(1, 0).Select
'Get the next line of the result
Result = Space(500)
rc = Sock.MvxSockReceive(Result)
If rc <> 0 Then
Sock.MvxSockShowLastError ("")
Exit Sub
End If
Loop Until Trim(Mid(Result, 1, 3)) <> "REP"
End If
Application.Goto ("ClothMatrixDataStart")
ActiveCell.Offset(1, 0).Select
Sock.MvxSockClose
End If
End Sub
This example parses through a list in Excel then updates the associated records in a M3 PDS090 Matrix
Private Sub UpdateClothMatrixData_Click()
Application.Goto ("ClothMatrixDataStart")
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value <> "" Then
Dim Sock As New MvxSockX
Dim transStr As String
Dim Result As String
Dim rc As Long
rc = Sock.MvxSockConnect(Sheets("Parameters").Range("Computer").Value, Sheets("Parameters").Range("Port").Value, Sheets("Parameters").Range("Username").Value, Sheets("Parameters").Range("Password").Value, "PDS090MI", "")
If rc <> 0 Then Sock.MvxSockShowLastError ("Error while Initializing")
Do
transStr = Space(500)
Mid(transStr, 1) = "UpdateLine"
Mid(transStr, 16) = Sheets("Cloth Matrix").Range("ClothMatrixCompany").Value 'Company
Mid(transStr, 19) = Sheets("Cloth Matrix").Range("ClothMatrix").Value 'Matrix
Mid(transStr, 24) = ActiveCell.Value 'Cloth
Mid(transStr, 114) = ActiveCell.Offset(0, 1).Value 'Valid from
Mid(transStr, 124) = ActiveCell.Offset(0, 2).Value 'Result
rc = Sock.MvxSockTrans(transStr, Result)
If rc <> 0 Then
Sock.MvxSockShowLastError ("")
Exit Sub
End If
ActiveCell.Offset(0, 4).Value = Result
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""
Application.Goto ("ClothMatrixDataStart")
ActiveCell.Offset(1, 0).Select
Sock.MvxSockClose
End If
End Sub
Using Excel and APIs we are able to manipulate tens of thousands of records quickly and easily while still using the M3 business engine. When APIs don't offer the functionality required you can also make use of Web Services from Excel which I will discuss in my next post.
