Journal Importing & jscript handling of Error Messages

Hold on to your seats, today I am going to talk about something that actually has a use.

So our Accounting Manager worked in a company that had modified MoveX extensively and they had some nice functionality. One of these nice things that they could do was import journals from a spreadsheet. Something that he wanted to help streamline some of the functions of his job but without modifications. πŸ˜‰

So jscripts to the rescue.

The basic premise is that we add a button to GLS100, it is this button that will kick off the import itself. We load the Excel spreadsheet, the Excel spreadsheet determines the FAM Function, the data that gets entered in to the E panel before moving on to GLS120/J1, once we are in GLS120/J1 we loop through the spreadsheet extracting lines and entering it in to Smart Office.

There were a few challenges that needed to be addressed – how does one handle errors? How do we actually present the user with a File Dialog, strangely the WPF dialog doesn’t seem to play well (something I need to investigate a little further). And there were some issues around the handling of dates from Excel and empty cells.

How do we know if there was an error entering a line? I had a good look but couldn’t find any events that I could subscribe to nor any properties, so roll on a nasty hack. You’ll notice at the bottom of the window in the status bar we get the notification of errors. This in turn means that we can take a look at the notification message itself – we just have to find the control. So I needed to enlist some of the code that I wrote a while ago to run through the Visual Tree and find the name of the control. Then at runtime we need to find that control, again, I took some existing code.

So the Status notification control was called labelStatus and it is a Label control. Now that we know what it is called we need to go up the Visual Tree far enough that we can start moving down until we get to the label.

To this end, I have a little function called goUp(), I typically only go up as far as the control PART_Window

        // go up the VisualTree until we get to PART_Window
        private function goUp(aContent : Object)
        {
            var parent : Object = aContent;
            var lastParent : Object = aContent;
            var result : Object = null;

            // here we will loop UP the VisualTree seeking the top
            while(null != (parent = VisualTreeHelper.GetParent(parent)))
            {
                lastParent = parent;

                var objType = lastParent.GetType();
                var objPropertyName = objType.GetProperty("Name");
                var strName : String = objPropertyName.GetValue(lastParent)

                if(null != strName)
                {
                    // PART_ContentPanel
                    if(String.Compare(strName,"PART_Window") == 0)
                    {
                        result = parent;

                        break;
                    }
                }
            }
            return(result);
        }

Then we need to go down until we find the control.

        // find a specific control
        private function findControl()
        {
            var result = null;
            var objTopControl = goUp(ggrdContentGrid);      // go up the visual tree to somewhere useful

            if(null != objTopControl)   // we found 'somewhere useful' πŸ˜‰
            {
                result = findControlInner(objTopControl, 0, "labelStatus"); // search for a control with a name "labelStatus"
            }
            else MessageBox.Show("Went up without success");

            return(result);
        }

        // this function goes down the tree looking for a specific object
        private function findControlInner(parent : Object, depth : int, astrControlName : String)
        {
            var objResult = null;

            try
            {
                if(null != parent)
                {
                    // get the type of our object, we do this
                    // so we can check if the object inherits
                    // from a DependencyObject
                    var parentobjType : Type = parent.GetType();
                    if(parentobjType.IsSubclassOf(DependencyObject) == true)
                    {
                        for(var i=0; i < VisualTreeHelper.GetChildrenCount(parent);i++)
                        {
                            // retrieve the child object
                            var current : Object = VisualTreeHelper.GetChild(parent,i);
                            if(null != current)
                            {
                                // here we shall deterine the type of the new object
                                var objType = current.GetType();

                                if(null != objType)
                                {
                                    // we're looking for the Name property, because
                                    // this is what I am interested in
                                    var objPropertyName = objType.GetProperty("Name");
                                    if(null != objPropertyName)
                                    {
                                        var strName = objPropertyName.GetValue(current);
                                        if(null != strName)
                                        {
                                            if(0 == String.Compare(strName, astrControlName))
                                            {
                                                objResult = current;

                                                break;
                                            }

                                            // does the current object have any children?
                                            if(VisualTreeHelper.GetChildrenCount(current) >= 1)
                                            {
                                                // recurse down
                                                objResult = findControlInner(current, depth+1, astrControlName);
                                                if(null != objResult)
                                                {
                                                    break;
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch(ex)
            {
                //debug.WriteLine("!-! Exception: " + ex.Message + " " + ex.StackTrace);
            }

            return(objResult);
        }

Then after we submit a journal line we check to see if there was an error against the labelStatus, if there is an error we stop any further submissions of lines.

The file prompting dialog was another issue, for some reason the WPF OpenFileDialog didn’t want to present from the jscript, so I had to resort to the System.Windows.Forms.OpenFileDialog()

The next issue to overcome was the way that Excel stores the dates in an odd format – so I couldn’t use the .Value, instead I needed to use Value2 property and then convert it with the DateTime.FromOADate()

So, the spreadsheet in question is as you see below:

Cell L5 is the FAM Function we select from GLS100/B
Cell R5 is the Accounting Date used in GLS100/E
Cell E7 is the Voucher Text used in GLS100/E
Rows 15 to 42 relate to the lines in GLS120/J1.

The code will issue a SELECT when we find the appropriate FAM Function.

We then set the Accounting Date and Voucher Text

And finally we enter each line.

And all of the code to do it…

import System;
import System.Text;
import System.Windows;
import System.Windows.Controls;
import System.Windows.Media;
import System.Windows.Media.Media3D;

import MForms;
import Mango.UI.Core;
import Mango.UI.Core.Util;
import Mango.UI.Services;
import Mango.Services;

import Excel;
import System.Reflection;

package MForms.JScript
{
	class GLS100_JournalImport_V00
	{
		var giicInstanceController : IInstanceController = null;    // this is where we will store the IInstanceController to make it available to the rest of the class
		var ggrdContentGrid : Grid = null;                          // this is the Grid that we get passed by the Init()
		var gexaApplication = null;                                 // Excel.Application object
		
		var gbtnImportFromExcel : Button = null;                    // this is the button that we will put on to the panel that will kick off the whole import
		var glvListView : ListView = null;                          // this is the ListView on the panel
		
		var gwbWorkbook = null;                                     // here we will store the Workbook object
		
		var giStartRow : int = 15;                                  // the starting row in the Spreadsheet
		var giMaxRow : int = 42;                                    // the end row in the Spreadsheet
		var giCurrentRow : int = 15;                                // the current row in the Spreadsheet
		
		var gbLookForResponse = false;                              // should we be looking for a response?

        var gobjStatus = null;                                      // the statusbar

        var gbRequest : boolean = false;                            // the request event 

		public function Init(element: Object, args: Object, controller : Object, debug : Object)
		{
            // lets make some of the controls and other
            // bits pieces available to other sections of our code
			ggrdContentGrid = controller.RenderEngine.Content;
			giicInstanceController = controller;
			glvListView = controller.RenderEngine.ListControl.ListView;
			
			try
			{
                // create the button for importing
				gbtnImportFromExcel = new Button();
				gbtnImportFromExcel.Content = "Import";

				Grid.SetColumnSpan(gbtnImportFromExcel, 10);
				Grid.SetColumn(gbtnImportFromExcel, 1);
				Grid.SetRow(gbtnImportFromExcel, 22);
				
                // finally add the control to the grid
				ggrdContentGrid.Children.Add(gbtnImportFromExcel);
				
				// ----- Events -----
				gbtnImportFromExcel.add_Click(OnImportFromExcelClicked);
				gbtnImportFromExcel.add_Unloaded(OnImportFromExcelUnloaded);
				
				giicInstanceController.add_RequestCompleted(OnRequestCompleted);
				giicInstanceController.add_RequestCompleted(OnRequested);
                gbRequest = true;
			}
			catch(exException)
			{
				MessageBox.Show("Error: " + exException.Message + Environment.NewLine + exException.StackTrace);
			}
		
		}
		
        // find a specific control
        private function findControl()
        {
            var result = null;
            var objTopControl = goUp(ggrdContentGrid);      // go up the visual tree to somewhere useful

            if(null != objTopControl)   // we found 'somewhere useful' πŸ˜‰
            {
                result = findControlInner(objTopControl, 0, "labelStatus"); // search for a control with a name "labelStatus"
            }
            else MessageBox.Show("Went up without success");

            return(result);
        }

        // this function goes down the tree looking for a specific object
        private function findControlInner(parent : Object, depth : int, astrControlName : String)
        {
            var objResult = null;

            try
            {
                if(null != parent)
                {
                    // get the type of our object, we do this
                    // so we can check if the object inherits
                    // from a DependencyObject
                    var parentobjType : Type = parent.GetType();
                    if(parentobjType.IsSubclassOf(DependencyObject) == true)
                    {
                        for(var i=0; i < VisualTreeHelper.GetChildrenCount(parent);i++)
                        {
                            // retrieve the child object
                            var current : Object = VisualTreeHelper.GetChild(parent,i);
                            if(null != current)
                            {
                                // here we shall deterine the type of the new object
                                var objType = current.GetType();

                                if(null != objType)
                                {
                                    // we're looking for the Name property, because
                                    // this is what I am interested in
                                    var objPropertyName = objType.GetProperty("Name");
                                    if(null != objPropertyName)
                                    {
                                        var strName = objPropertyName.GetValue(current);
                                        if(null != strName)
                                        {
                                            if(0 == String.Compare(strName, astrControlName))
                                            {
                                                objResult = current;

                                                break;
                                            }

                                            // does the current object have any children?
                                            if(VisualTreeHelper.GetChildrenCount(current) >= 1)
                                            {
                                                // recurse down
                                                objResult = findControlInner(current, depth+1, astrControlName);
                                                if(null != objResult)
                                                {
                                                    break;
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch(ex)
            {
                //debug.WriteLine("!-! Exception: " + ex.Message + " " + ex.StackTrace);
            }

            return(objResult);
        }

        // go up the VisualTree until we get to PART_Window
        private function goUp(aContent : Object)
        {
            var parent : Object = aContent;
            var lastParent : Object = aContent;
            var result : Object = null;

            // here we will loop UP the VisualTree seeking the top
            while(null != (parent = VisualTreeHelper.GetParent(parent)))
            {
                lastParent = parent;

                var objType = lastParent.GetType();
                var objPropertyName = objType.GetProperty("Name");
                var strName : String = objPropertyName.GetValue(lastParent)

                if(null != strName)
                {
                    // PART_ContentPanel
                    if(String.Compare(strName,"PART_Window") == 0)
                    {
                        result = parent;

                        break;
                    }
                }
            }
            return(result);
        }

        // display an OpenFileDialog box
        // and extract the result
		private function retrieveImportFile()
		{
			var result : String = null;
			var ofdFile = new System.Windows.Forms.OpenFileDialog();    // we have to use the forms OpenFileDialog unfortunately
			if(null != ofdFile)
			{
				ofdFile.Multiselect = false;
				ofdFile.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx|All Files (*.*)|*.*"; // filter on xls or xlsx files only
				
				if(true == ofdFile.ShowDialog())
				{
					result = ofdFile.FileName;
				}
			}
			return(result);
		}
		
        // this is where we actually do the import
		private function OnImportFromExcelClicked(sender : Object, e : RoutedEventArgs)
		{
			try
			{
                // here we do some initialisation of Excel
				InitialiseExcel();

				var strFilename : String = retrieveImportFile();            // retrieve the filename of the Excel spreadsheet to open
				if((null != strFilename) && (null != gexaApplication))      // ensure that not only do we have a filename, but we also managed to initialise Excel
				{
					gwbWorkbook = gexaApplication.Workbooks.Open(strFilename);  // open the spreadsheet
					if(null != gwbWorkbook)
					{
						gwbWorkbook.Saved = true;                               // get rid of those annoying save messages
						var strVoucherType : String = retrieveVoucherType();    // we want to get the voucher type from the spreadsheet (the GLS100 voucher)
						
						if(!String.IsNullOrEmpty(strVoucherType))               // we need to ensure that we have a voucher type
						{
                            giicInstanceController.RenderEngine.SetFocusOnList();
							selectFAMFunction(strVoucherType);              // now we need to go out and select the function
							
							// from where on out, we start using the events
						}
					}
					else MessageBox.Show("Failed to Open Workbook");
				}
				else MessageBox.Show("Filename or Excel doesn't exist: " + strFilename);
			}
			catch(exException)
			{
				MessageBox.Show("Error: " + exException.description);
			}
		}

		// set the VoucherText within GLS100/E
		private function setM3VoucherText(astrVoucherText : String)
		{
			var tbVoucherText : TextBox = ScriptUtil.FindChild(ggrdContentGrid, "WWGVTX");
			if(null != tbVoucherText)
			{
				tbVoucherText.Text = astrVoucherText;
			}
			else MessageBox.Show("setM3VoucherText() - Child not found");
		}

		// set the accounting date within GLS100/E
		private function setM3AccountingDate(astrAccountingDate : String)
		{
			var tbITNO = ScriptUtil.FindChild(ggrdContentGrid, "WWACDT");
			if(null != tbITNO)
			{
				try
				{
					var dtValue : DateTime = DateTime.FromOADate(Convert.ToDouble(astrAccountingDate));
					tbITNO.Value = dtValue;
				}
				catch(ex)
				{
					MessageBox.Show(ex.description);
				}
			}
			else MessageBox.Show("Accounting Date not found");
		}

        // retrieve the voucher text from the spreadsheet
		private function retrieveVoucherText()
		{
			return(gwbWorkbook.ActiveSheet.Range("E7").Value);
		}
		
        // retrieve the accounting date from the spreadsheet
        // we need to use Value2 in this instance to get
        // a value that we can actually use
		private function retrieveAccountingDate()
		{
			return(gwbWorkbook.ActiveSheet.Range("R5").Value2);
		}

		// retrieve the voucher type from the Spreadsheet
		private function retrieveVoucherType()
		{
			return(gwbWorkbook.ActiveSheet.Range("K5").Value);
		}		

		// GLS100/B set the FAM Function
		private function selectFAMFunction(astrFAMFunction : String)
		{
			var bFound : boolean = false;
			
			if(!String.IsNullOrEmpty(astrFAMFunction))
			{
                // search through the ListView for the FAM function
				for(var iCount : int = 0; iCount < glvListView.Items.Count; iCount++)
				{
					var itmCurrentItem = glvListView.Items[iCount];
					if(null != itmCurrentItem)
					{
						if(!String.IsNullOrEmpty(itmCurrentItem[0]))
						{
							var strCurrentString = itmCurrentItem[0].ToString();
							if(0 == String.Compare(strCurrentString, astrFAMFunction))
							{
								glvListView.SelectedItem = itmCurrentItem;
								bFound = true;
								break;
							}
						}
					}
				}
			}
			if(true == bFound)
			{
                // ok, we've found the FAM Function on the ListView
                // now we need to SELECT it
				giicInstanceController.ListOption("1");	// SELECT
			}
			
		}
		
        // our Import button is being unloaded, now's a good time to clean
        // everything up
		private function OnImportFromExcelUnloaded(sender : Object, e : RoutedEventArgs)
		{
			if(null != gbtnImportFromExcel)
			{
				gbtnImportFromExcel.remove_Click(OnImportFromExcelClicked);
				gbtnImportFromExcel.remove_Unloaded(OnImportFromExcelUnloaded);
			}
		}
		
		public function OnRequested(sender: Object, e: RequestEventArgs)
		{
            // we don't really use this at all at the moment
		}
				
		public function OnRequestCompleted(sender: Object, e: RequestEventArgs)
		{
			if(e.CommandType == MNEProtocol.CommandTypeKey)     // we're looking for a key event
			{
				if(e.CommandValue == MNEProtocol.KeyEnter)      // specifically we're looking the enter key event
				{
					if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("E"))   // are we on panel E?
					{
						handleEPanel();     // handle panel E
					}
					else if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("J1")) // are we on panel G1 (this should be GLS120/G1)
					{
                        if(null == gobjStatus)  // have we found the status control, if not, try to find it
                        {
                            gobjStatus = findControl(); // do the actual finding of the control
                        }
                        if(null != gobjStatus)
                        {
                            // now we should extract the status
                            var strStatusMessage : String = gobjStatus.Content.ToString();
                            if(false == String.IsNullOrEmpty(strStatusMessage))
                            {
                                giCurrentRow = giMaxRow + 1;
                                MessageBox.Show("There was an error, cannot continue! '" + gobjStatus.Content.toString() + "'");
                            }
                        }
                        else
                        {
                            MessageBox.Show("Couldn't find the StatusBar");
                        }
						handleJ1Panel();    // handle panel j
					}

				}
			}
			else if(e.CommandType == MNEProtocol.CommandTypeListOption)
			{
                if(e.CommandValue == MNEProtocol.OptionSelect)
                {
                    handleEPanel();
                }
			}
		}
		
        // this is where we do the actual handling of the J1 Panel
		private function handleJ1Panel()
		{
			if(giCurrentRow <= giMaxRow)    // the spreadsheet has a limited number of rows...
			{
				// extract the lines from the spreadsheet
				var strWXAIT1 : String = retrieveFromActiveSheet("B" + giCurrentRow);
				var strWXAIT2 : String = retrieveFromActiveSheet("C" + giCurrentRow);
				var strWXAIT3 : String = retrieveFromActiveSheet("D" + giCurrentRow);
				var strWXAIT4 : String = retrieveFromActiveSheet("E" + giCurrentRow);
				var strWXAIT5 : String = retrieveFromActiveSheet("F" + giCurrentRow);
				var strWXAIT6 : String = retrieveFromActiveSheet("G" + giCurrentRow);
                var strWWCUAMDebit : String = retrieveFromActiveSheet("I" + giCurrentRow);
				var strWWCUAMCredit : String = retrieveFromActiveSheet("L" + giCurrentRow);
				var strWWVTXT : String = retrieveFromActiveSheet("O" + giCurrentRow);
                var strWWVTCD : String = retrieveFromActiveSheet("N" + giCurrentRow);

                // this is the current row
				giCurrentRow = giCurrentRow + 1;
				if(!String.IsNullOrEmpty(strWXAIT1))
				{
					if(0 != String.Compare(strWXAIT1,"undefined"))  // verify that we actually have content
					{
						setM3TextField("WXAIT1", strWXAIT1);	// account
	
						setM3TextField("WXAIT2", strWXAIT2);	// Dept
						setM3TextField("WXAIT3", strWXAIT3);	// Dim3
						setM3TextField("WXAIT4", strWXAIT4);	// Dim4
						setM3TextField("WXAIT5", strWXAIT5);	// Dim5
						setM3TextField("WXAIT6", strWXAIT6);	// Dim6
						//setM3TextField("", retrieveFromActiveSheet("H" + giCurrentRow));	// Division
                        if(!String.IsNullOrEmpty(strWWCUAMDebit))
                        {
                            setM3TextField("WWCUAM", "-" + strWWCUAMDebit);	// Value
                        }
                        else if(!String.IsNullOrEmpty(strWWCUAMCredit))
                        {
                            setM3TextField("WWCUAM", strWWCUAMCredit);	// Value
                        }
						
						setM3TextField("WWVTXT", strWWVTXT);	// Voucher Text
                        setM3TextField("WWVTCD", strWWVTCD);    // VAT type

						giicInstanceController.PressKey("ENTER");   // press the enter key
					}
				}
			}
            else
            {
                if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("J1"))
                {
                    giicInstanceController.remove_RequestCompleted(OnRequestCompleted);
        			giicInstanceController.remove_RequestCompleted(OnRequested);
                    gbRequest = false;
                }

                // check to ensure we have a Workbook object
                // before we attempt to close the workbook
                if(null != gwbWorkbook)
                {
                    gwbWorkbook.Close(false);
                    gwbWorkbook = null;
                }
                // make sure we have actually created
                // the Excel Application object before
                // we Quit
                if(null != gexaApplication)
                {
                    gexaApplication.Quit();
                    gexaApplication = null;
                }
            }
		}
		
        // a little wee helper function that will search for a TextBox name
        // and set the TextBox value
		private function setM3TextField(astrName : String, astrValue : String)
		{
			var tbTextBox : TextBox = ScriptUtil.FindChild(ggrdContentGrid, astrName);
			if(null != tbTextBox)
			{
				tbTextBox.Text = astrValue;
			}
			else MessageBox.Show("Can't find: " + tbTextBox.Text);
		}

        // retrieve some data from the active spreadsheet
        // at a specific location
		private function retrieveFromActiveSheet(astrPosition : String)
		{
			var strValue : String = gwbWorkbook.ActiveSheet.Range(astrPosition).Value;
			if(true == String.IsNullOrEmpty(strValue))
			{
				strValue = "";
			}
            else if(0 == String.Compare(strValue, "undefined"))
            {
                strValue = "";
            }

			return(strValue);
		}

        // handle the E Panel
		private function handleEPanel()
		{
			var strAccountingDate : String = retrieveAccountingDate();  // retrieve the accounting date from the Spreadsheet
			var strVoucherText : String = retrieveVoucherText();        // retroeve the voucher text from the Spreadsheet

			if((!String.IsNullOrEmpty(strAccountingDate)) && (!String.IsNullOrEmpty(strVoucherText)))
			{
				setM3AccountingDate(strAccountingDate);     // now we actually set the accounting date in the TextBox
				setM3VoucherText(strVoucherText);           // and the Voucher TextBox
				giicInstanceController.PressKey("ENTER");   // now we press enter - this will fire off a Request event and should take us to GLS120/G1
			}
			else MessageBox.Show("We require an Account Date and Voucher Text");
		}
		
		private function InitialiseExcel()
		{
			var result = null;
			try
			{
				gexaApplication = new ActiveXObject("Excel.Application");
				gexaApplication.Visible = true;
				
				result = gexaApplication;
			}
			catch(exException)
			{
				MessageBox.Show("Error: " + exException.Message + Environment.NewLine + exException.StackTrace);
			}
			return(result);
		}
		
		//
	}
}

Have fun coding!

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

21 Responses to Journal Importing & jscript handling of Error Messages

  1. Pingback: Journal Importing From Excel & jscript Handling of Error Messages Part II | Potato IT

  2. Awesome post. Do you mind if I ask what your source is for this information?

    • potatoit says:

      Thank you πŸ™‚

      Most of the information has come through the M3 LSO scripting document and experimentation. There was another comment on one of my posts about the RequestCompleted() event which really helped.
      I have previously mucked around with the Windows Presentation Foundation (WPF) and Excel integration in VB and C# so have a good idea on how it is structured and some of the nifty things you can do.

      If you start from the first post, you’ll notice a difference in the change in the tone and the usefulness of what I do – so it’s very much a case of educated experimentation – or in some cases blindly dumping information from fields (yup, I have spent many hours doing that)
      I use the Object Browser in Visual Studio extensively to look at the functions and methods in the various Smart Office assemblies – trying to build up relationships in my mind between the different objects…

      I have made the comment to Lawson about expanding their documentation on the subject – I firmly believe that the jscripting functionality takes ‘just another nice WPF application’ and turns it in to something truely fabulous. It is a shame if organisations aren’t taking advantage of the possibilities purely because the information isn’t out there.

      With all that in mind, though what I post may work, I’m not a programmer by trade – so it may not be the most graceful or even ‘right’ way of achieving the goal πŸ˜‰

  3. HendrikA says:

    This is really brilliant!
    I’m Solution Consultant atLawson and used JScript for several purposes in the past.
    This is really a very nice example what customers can achieve with the use of Lawson Smart Office and JScript.
    I have never seen this kind of batch processing of several accounting lines within a JScript. Big applause!
    I used an Excel template before with VB communicating with a WebService and a client Pocket SOAP. Compared to your solution much more complicated, so that’s why I like your solution, because ‘Simpler is better!’.
    I have used your code and made some minor changes to use my existing GL template and to work with D/C-Codes.
    Unfortunately there is for the moment one limitation: This jscript does only work if the Excel language and the Windows regional settings are the same. So for a lot of users this is not an issue, but I’m working with an English version of Excel and my Default Regional Setting in WIN7 is German. After changing regional settings it works.
    I know there is an option in jscript to have it more generic, like ‘ExcelUtilities.GetPropertyInternational’, but for the moment I can live with this.

    So, thank you again for posting here really values!

    Note: Small remark:
    …Cell L5 is the FAM Function we select from GLS100/B
    -> the jscript looks for cell K5 instead!

    • potatoit says:

      Thank you for the comments πŸ™‚

      Regarding the language, it’s something I hadn’t even considered.

      There is a property in Excel for the Language settings
      Application.LanguageSettings.LanguageID which if set may resolve the issue.

      Or failing that, as you say, use the Smart Office functionality – but I already knew my way and haven’t got around to getting my head around the Smart Office functionality πŸ™‚

      Cheers,
      Scott

  4. Pam Ekstrom says:

    Is there anything in this script that refers to the version of M3 we are on? We installed this script many months ago and it worked. Now it is not working. We are on LSO version 10.0.2.0.4

    • potatoit says:

      Hi Pam,

      yes, LSO 10 handles items in the ListViews differently. Previously it used the .Items for the items, in LSO 10 .ItemsSource

      Bearing that in mind, the section that breaks is at a guess the:
      selectFAMFunction()

      You could change the glvListView.Items to glvListView.ItemsSource and it may work.

      We don’t have LSO10 installed yet, so I can’t be sure.

      Cheers,
      Scott

  5. Dilshan Embuldeniya says:

    Hi,

    Nice post. Like to try this out, have a hunch that this will a burning concern we have currently. We need to upload APS100 vouchers (One AP line, multiple GL lines per invoice). Where can I find the excel format you used in this upload?

    Regards,
    Dilshan.

  6. Alexandra Ramos says:

    Hi Scott,

    Congrats, great job! I’ve already tested your script in LSO 9 and it worked perfectly. Now I would like to implement it on ISO 10 but I don’t know why (I’m a functional consultant not skilled in development) the script doesn’t load and the Import button doesn’t show on the panel. However if I try run it with jscript it works fine. Can you help? Thank you so much.
    Cheers, Alexandra

    • potatoit says:

      Hi Alexandra,

      there were some changes in Smart Office 10 around the ListView. So I would suggest that you try the script from here:
      https://potatoit.wordpress.com/2012/05/16/gls100-journal-import-part-iii-12/

      It’s what we currently use in Smart Office 10.x

      If you still have problems, please let me know and we’ll see what we can do.

      Good luck!

      Cheers,
      Scott

      • Alexandra Ramos says:

        Thank you Scott. I’ll do that and get back to you with the results.

        Cheers,
        Alexandra

      • Alexandra Ramos says:

        Hi again Scott,

        I had no luck. The Import button still doesn’t appear after adding the script.

        Do I need to add any arguments?

        Thanks,
        Alexandra

      • potatoit says:

        Hi Alexandra,

        you don’t need any arguments to run it. If you use the JScript editor built in to Smart Office it still compiles correctly?

        If yes, then it sounds like the script isn’t associated with the panel.

        So first thing. The new version of the script – what was the filename you gave it? It should be
        GLS100_JournalImport_V09.js
        This should be copied in to the JScripts directory on your server.

        Then go to GLS100/B. Click on Tools -> Personalize -> Scripts

        Under the “Created scripts” list, you need to make sure you have the following entry:
        GLS100_JournalImport_V09

        If it’s still not working then we will need to take a look at the Smart Office logs (? menu -> About Infor Smart Office -> View Log file). Is there an error in there about not being able to find GLS100_JournalImport_V09?
        You may need to change the logging level in Smart Office to debug (Show -> Settings -> Infor Smart Office -> Log Level)

        Cheers,
        Scott

      • Alexandra Ramos says:

        Hi Scott,

        It works!!! I had changed the file name but I forgot to change also inside the file.

        One question: the last line only updates M3 after I close the excel file. Is this correct?

        Thanks a lot for your help.

        Cheers,
        Alexandra

  7. Alexandra Ramos says:

    Hi Scott,

    Nevermind the issue about the last line. It’s solved.

    Once more thank you for your help and congratulations on the great job you done!

    Cheers,
    Alexandra

  8. Jessika Israelsson says:

    Hi Scott,
    Thanks for an inspiring site!
    We are running a version of your GLS100 script since a couple of years back, but have run into problems when upgrading to Microsoft Office 365 Pro Plus. Have you heard or seen this before?
    Cheers,
    Jessika

    • potatoit says:

      Hi Jessika,

      no, I haven’t encountered the issue, but I don’t use Office 365 yet. I gather that Office 365 is a click to run install?

      Is there an error message?

      Cheers,
      Scott

      • Jessika Israelsson says:

        Hi again,

        The error message comes from InitialiseExcel(), Excel doesn’t open. From the message I would have guessed it was the known error with an English version of Excel and regional settings of the computer configured for a non-English language, but there is a fix for that in the script (I haven’t an upgraded computer near by right now so I can’t check the exact message).

        I tried initiating Excel with ExcelUtilities instead of ActiveX and that works. Could that be a solution? Replacing ActiveX with ExcelUtilities? Is there a reason to use ActiveX instead? I guess backward compatibility with old office version is a reason, but anything else?

        Thanks,
        Jessika

      • potatoit says:

        I didn’t use the MForms.ExcelUtilities purely because I had already written code to handle Excel.
        I’m a little surprised that that works and not my implementation – there must be some subtle issue.

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