Interfacing Excel with M3 via APIs

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

Open a connection to M3

In your VB code add the following:

'Init M3 API variables

Dim Sock As New MvxSockX
Dim 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

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 rc <> 0 Then
  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.