Saturday, March 6, 2010

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.

25 comments:

  1. Hello Al,

    I saw your response on the Intentia-L userlist, and read through your related blog posting. I think the solution you provided would be very useful for my company - if I can get it setup.

    I don't see the API "addon" for Excel. We are running an older version of Movex (12.6 RPG). Do you know if this addon is available for our version? If so, should the dll already be loaded somewhere in our environment?

    Thank you for sharing your solution.

    Best Regards,
    Bill Henson
    william.henson@johnsonelectric.com

    ReplyDelete
  2. Hi Bill, sorry I have just spotted your comment.

    If you haven’t already found an answer to your query – yes, as far as I know the APIs are supported on RPG. Paul Grooby (who also posted on that thread) uses RPG v 12 at Resene as he has no difficulty calling the APIs.

    However – you may find that the APIs are a licensed module. Talking to your local Lawson rep is probably the way to go to figure that out.

    Al.

    ReplyDelete
  3. Hi,

    I used M3 API MOS070MI in Excel and it works fine. But i have a problem,after create transactions in Movex, i entered new transactions in Excel to validate some information i use connection ODBC. The connection failed error cwbco1003 communication link failure. I close Excel and reopen it, my connection ODBC is working.

    Do you have this problem ?

    Thanks

    ReplyDelete
  4. Hi Al,

    How's that post comming along where you were going to explain how to make use of Web Services from Excel?

    BR
    Chris

    ReplyDelete
  5. Gidday Chris. I've a new role that had been eating up my free time. I'll try to put one together in the next couple of weeks. Cheers, Al.

    ReplyDelete
  6. After using M3 API function MvxSockClose, i cannot connect to DB2 with ODBC or Db2 native (ibm.data.db2.iseries).

    I have this problem in Excel and in C# applications.

    ReplyDelete
  7. Re the MCXSockClose question -- what you'll find is that the TCP/IP process is a little wierd. So put your code into debug mode and just step through the process. You may find that the code is executing too quick in which case the close to the sock and open of the ODBC are in conflict (even though they use different ports) - if wo insert a wait loop of a few milliseconds using the language of your choice, Cheers, Paul

    ReplyDelete
  8. I also have the problem with ODBC connection after a MvxSockClose. Did anybody have found a solution for that? (I've tried to wait 1 sec before connecting but it doesn't work)

    ReplyDelete
  9. I have never had this problem before. However, why don't you try leaving the API connection open, perform the ODBC call, then close the API connection. Cheers, Al.

    ReplyDelete
  10. Closing the API connection after having performed the ODBC call seams to work better ! Thanks a lot.
    Cheers, S├ębastien.

    ReplyDelete
  11. Thanks a Lot.. Its Really Helpful. :)

    ReplyDelete
  12. Hello, I'm trying to test your solution in my M3 10 version. I followed all your instruction in Excel but when I try to run the code I receive the Error 429: ActiveX Component can't create object. on the instruction "rc = Sock.MvxSockConnect("AS400", 16800, "User", "password", "PDS090MI", "")". Can you figured out what am I doing wrong?
    Thank you for your reply

    ReplyDelete
    Replies
    1. Hello El Kondor, are you solve this problem? I have the same message error with Movex API "rc = Sock.MvxSockConnect("192.168.100.130", "16800", "M3SRVADM", "M3LAWSON", "PPS370MI", "")"

      Thank you for your reply

      Josep

      Delete
    2. You may need to register the dll.
      e.g.

      C:\MvxAPI>regsvr32 MvxSockX_SVR.dll

      Delete
    3. I had the same issue. Trick is that the MvxAPI package has to be installed into C:\Program Files\MvxAPI not to root of C: drive. Once this package is installed in "Program Files" you have to manually register library MvxSockX_SVR.dll via command "regsvr32 C:\Program Files\MvxApi\MvxSockX_SVR.dll" and that´s it.

      Delete
  13. Hi is there a API documentasion for Movex/M3? Where can I find it?

    ReplyDelete
  14. Hello all, my post is litle bit late but all the info about the API's is available when you install the M3 API Tools. I created an Excel Workbook that acts like a shell around the API's. I need around 5 persons to test it in different environments. Here is my site:
    http://adsl429174.wix.com/upload-m3-api

    ReplyDelete
    Replies
    1. I created a similar tool a few years back :-)

      Delete
  15. I have the same problem with OLEDB/ODBC connection error after using the API.
    Commenting the mvxSockClose seems to prevent the error, but is it a best practice ?

    ReplyDelete
  16. HI,
    i am getting 429 activex error-on "sock.MvxSockConnect" the same code working fine if i used 32bit excel.
    but it dont work with 64bit ms office(excel).
    i tried all possible way including register dll file.
    it seems MvxSockX_SVR.dll file does not support 64bit excel while create com and activex component.
    i searched a lot to change 32bit dll files in 64bit but not successes.

    as per my search these dll file written in c language so it is difficult to me write code in c language.

    please help with workaround(except 32bit MSoffice installer).

    appropriate quick response, please help as i require it urgently.

    Thanks

    ReplyDelete
  17. Hi,
    i solved this problem by adding 64bit version of MvxSockX_SVR.dll(name of dll file -MvxSockX_SVRx64.dll,MvxSockNx64).

    ReplyDelete
  18. Hi farhat,
    so do I see it right, that you have managed it to run 64bit Excel with MvxSockX API/VB? Adding means register MvxSockX_SVRx64.dll with regsvr32? Anything else?

    ReplyDelete
    Replies
    1. That and ensuring the dll is registered within Excel as well should be all that is needed to make this work.

      Delete
  19. Hi Farhat,

    We installed MvxAPI in Windows 10 64 bit and we face "429 activex error" issue.
    So we download MvxSockX_SVRx64.dll and MvxSockNx64 from Info Xtreme website.
    During registering the dll file, we face new error message as below

    Command: regsvr32 MvxSockX_SVRx64.dll
    Error message:
    The module "MvxSockX_SVRx64.dll" was loaded but the call to DllRegisterServer failed with error code 0x80070005.

    Can you guide us to fix this problem?

    thank you!

    ReplyDelete