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!
Potato IT 
Pingback: Journal Importing From Excel & jscript Handling of Error Messages Part II | Potato IT
Awesome post. Do you mind if I ask what your source is for this information?
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 😉
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!
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
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
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
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.
Hi Dilshan,
a little late :-/, but … there is a screenshot of the spreadsheet in the post. 🙂
And I’ve also uploaded it to github
https://github.com/potatoit/M3_JScript_GLUpload
Cheers,
Scott
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
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
Thank you Scott. I’ll do that and get back to you with the results.
Cheers,
Alexandra
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
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
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
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
Great, glad you’ve got it working!
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
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
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
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.