Excel & Populating Values in to Multi-Panel Sequences

So, I was wondering if it was possible to read values off an Excel spreadsheet and use those values to create an order and add an order line. In short – but of-course! πŸ™‚

Though Smart Office provides functionality natively through mforms::ExcelUtilities, I thought it would be interesting to use a more traditional approach to illustrate how you can use other external programs without Lawson providing direct functionality – for example, you could use Microsoft Word to record all the values for an item in MMS001 for the purposes of documentation.

Though I haven’t played around with it, I’m assuming that this would be a good time to use automation – but I haven’t read that far through the pdf πŸ˜‰

So the basic premise of what I am going to demonstrate is

  1. From OIS100
  2. Open a Spreadsheet
    1. Read some of the values
  3. Subscribe to the RequestCompleted event
  4. Do a PressKey
  5. The PressKey will fire the RequestCompleted event once we move on the next panel
    1. PressKey until we get to the Order Line (which will generate a new RequestCompleted event)
  6. Write some of the values to the order lines that we extracted from the Spreadsheet

We use the RequestCompleted, as we need to wait until the first event is complete before moving on to the next event. Doing the PressKeys one after the other won’t achieve the results that we want.

The sample spreadsheet which I saved as D:\Test.xlsx, you’ll see that in the A column I have two field names, I have done this purely for the purposes of demonstrating that we can extract the field name and the values from the spreadsheet and it made it easier to muck around using different fields without changing the scripts πŸ™‚

The script below doesn’t add a button, it just runs and creates an order for customer 165, with an order type of D01. We have a panel sequence of EFGHB1, and we have an extra confirm on panel A.

We then set the Item Number and the quantity before entering.

Anyways, lots of comments in the code – hopefully it all makes sense and still works when you deploy it πŸ˜€


import System;
import System.Windows;
import System.Windows.Controls;
import MForms;

import Excel;
import System.Reflection;

package MForms.JScript
{
   class ExcelTest
   {
   		// cache some of the variables
   		var gcontroller;
   		var gdebug;
   		var gcontent;
   		
   		var giStep;

		// where we will store the Item Number
   		var gITNO;
   		// Quantity of the order
   		var gQty;
   		
   		// has the RequestCompleted event been removed?
   		var bRemoved;
   		
      public function Init(element: Object, args: Object, controller : Object, debug : Object)
      {
         var content : Object = controller.RenderEngine.Content;

         gdebug = debug;
         gcontroller = controller;
         gcontent = content;
         
         // subscribe to the RequestCompleted event
         controller.add_RequestCompleted(OnRequestCompleted);
			bRemoved = false;
         try
         {
         		// we want to create an Excel App
	         var exExcel = new ActiveXObject("Excel.Application");
	         if(null != exExcel)
	         {
	         		// for the purposes of demonstrating, we'll show
	         		// Excel - we do get better performance if it is
	         		// hidden
	         		exExcel.Visible = true;
	         		
	         		// load the spreadsheet which we will use to submit
	         		// the information
	         		var wbBook = exExcel.Workbooks.Open("D:\Test.xlsx");
	
	         		if(null != wbBook)
	         		{
	         			// Cell B1 - this is the customer
	         			var strCUNO = wbBook.Worksheets(1).Cells(1,2).Value;
	         			// Cell B2 - this is the order type
	         			var strORTP = wbBook.Worksheets(1).Cells(2,2).Value;

						// Just for the purposes of because we can...
						// we are storing the field names in column A1 and A2
						// CUNO, Customer Number field name
	         			var strFNCUNO = wbBook.Worksheets(1).Cells(1,1).Value;
	         			// ORTP, Order Type field name
	         			var strFNORTP = wbBook.Worksheets(1).Cells(2,1).Value;
	         			
	         			// go out and retrieve the Customer Number TextBox control
	         			var tbCUNO = ScriptUtil.FindChild(content, strFNCUNO);
	         			// go out and retrieve the Order Type TextBox control
	         			var tbORTP = ScriptUtil.FindChild(content, strFNORTP);
	         			
	         			if(null != tbCUNO)
	         			{
	         				// set the TextBox Customer Number value
	         				tbCUNO.Text = strCUNO;
	         			}
	         			
	         			if(null != tbORTP)
	         			{
	         				// set the order type text box
	         				tbORTP.Text = strORTP;
	         			}
	         			
	         			// we're also going to demonstrate adding an order line, only one so
	         			// we'll cache it globally
	         			// get the Item Number
	         			gITNO = wbBook.Worksheets(1).Cells(4,1).Value;
	         			// get the Quantity
	         			gQty = wbBook.Worksheets(1).Cells(4,2).Value;
	         			
	         			// now we are going to press enter
	         			controller.PressKey("ENTER");
	         			giStep = 0;

	         			// close the Workbook
	         			wbBook.Close();
	         		}
	         		// Quit Excel
	         		exExcel.Quit();
	         }
	      }
         catch(ex)
         {
         }
      }
      
      public function OnRequestCompleted(sender: Object, e: RequestEventArgs)
      {
      		if((e.CommandType == MNEProtocol.CommandTypeKey) && (e.CommandValue == MNEProtocol.KeyEnter))
      		{
      			// we already know the panel sequence, so we won't bother checking it
      			// if it is likely to change you should look it up on the A panel
      			
				if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("A"))
				{
					// if panel a, then we just want to enter through it, we have nothing useful to add
					gcontroller.PressKey("ENTER");
				}
      			else if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("E"))
      			{
      				// if panel E, then we just want to enter through it, we have nothing useful to add
      				gcontroller.PressKey("ENTER");
      			}
      			else if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("F"))
      			{
      				// if panel F, then we just want to enter through it, we have nothing useful to add
      				gcontroller.PressKey("ENTER");
      			}
      			else if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("G"))
      			{
      				// if panel G, then we just want to enter through it, we have nothing useful to add
      				gcontroller.PressKey("ENTER");
      			}
      			else if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("H"))
      			{
      				// if panel H, then we just want to enter through it, we have nothing useful to add
      				gcontroller.PressKey("ENTER");
      			}
      			else if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("B1"))
      			{
      				// if panel B1, this where we enter the order lines
      				if(giStep == 0)	// we'll just make sure we only execute it once
      				{
      					// from the order line, we will enter the Item Number and Quantity
      					// so we retrieve the TextBoxes from the panel
	         			var tbITNO = ScriptUtil.FindChild(gcontent, "WBITNO");
	         			var tbQty = ScriptUtil.FindChild(gcontent, "WBORQA");
	         			
	         			// Fill in the Item Number TextBox
	         			tbITNO.Text = gITNO;
	         			// Fill in the Quantity TextBox
	         			tbQty.Text = gQty;
	         			
	         			// Press Enter to add the line
	         			gcontroller.PressKey("ENTER");
	         			// we change the giStep so we don't loop through this
	         			giStep = 1;
         			}
         			// now we'll remove the RequestCompleted Event
         			gcontroller.remove_RequestCompleted(OnRequestCompleted);
         			bRemoved = true;
      			}
      		}
      		if(true != bRemoved)
      		{
      			gcontroller.remove_RequestCompleted(OnRequestCompleted);
      		}
      }
   }
}


Have fun!

This entry was posted in Development, M3 / MoveX. Bookmark the permalink.

3 Responses to Excel & Populating Values in to Multi-Panel Sequences

  1. Paul Grooby says:

    Scott – I’m waiting with much anticipation for our upgrade so I can start doing some much needed customisation and alterations to reflect process flow – so am following the progress of your blog. Preseumably without too much effort you could perhaps add a button to extract the data rather than autorun etc.
    Good stuff here , Cheers, Paul

    • potatoit says:

      Hi Paul,

      yup, you can add buttons to extract data. It does get more complicated trying to extract data from different panels and different programs though – which depending on the situation WebServices may be the best bet.

      I must say that going to MoveX Explorer to Smart Office has opened up a whole new world of possibilities – I’m very pleased with the potential now that we are on 10.1.

      Cheers,
      Scott

  2. Colm Hayes says:

    Thank you very much for this, something I was hoping to be able to do with MO creation in PMS001 and with your code I can save hours of work by automating the order creation part. Cheers.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s