OnEvent

[nodochau]

Hello All,
Trying OnEvent today on my spreadsheet to capture the out of spec value when operator keys in. The scripts work fine in debug mode but in run mode nothing shows up when the wrong value is typed in.
Any thoughts will be appreciated.
Here is my code:

Code: Select all

XLOpen>C:\Users\...\149405.xlsx,1,xl
Wait>3
SetFocus>149405 - Excel
Let>row=15
Let>col=6
XLGetCell>xl,ProcessSheet,row,4,upper_val
XLGetCell>xl,ProcessSheet,row,5,lower_val
Let>warning=FALSE
Wait>1.5

//**BREAKPOINT**

OnEvent>CUSTOM,CheckCells,warning,PopupWindow

Label>start
Wait>1
Goto>start
SRT>CheckCells
   
//**BREAKPOINT**    
    XLGetCell>xl,ProcessSheet,row,col,val
    If>val<>
      If>{(%val%>%upper_val%)OR(%val%<%lower_val%)}
        Let>warning=TRUE
      else
        Add>col,1
      Endif
    Endif
END>CheckCells

SRT>PopupWindow
  MDL>Value %val% at row: %row% and column %col% %crlf% is out of Spec
  Let>empty_cell=
  XLSetCell>xl,ProcessSheet,row,col,empty_cell,
  Let>warning=FALSE
END>PopupWindow

[JRL]

nodochau,
I did some testing with your script. Based on your comment that it worked in the editor, I suspected a timing issue. Turns out that is not correct. Not being able to get the script to function I started boiling down to the essentials. If you take the script below and rewrite with your excel information you will find that you cannot type data into a field that is being monitored by the XLGetCell> function. The moment you type a character into the monitored cell the script crashes with a Macro Scheduler error message. "Call was rejected by callee."

I did find its possible to paste a value into the cell.

When you were running in the editor you apparently typed in the data then stepped through the XLGetCell> line. since you did not type while the function was monitoring the cell, the script functioned for you.

Code: Select all

//Move the message box out from under the excel sheet.
Let>msg_xpos=0
Message>
//Filename.  No path.  No extension.
Let>vFilename=Filenmae without path or extension.

//Add path
Let>ExcelFileName=c:\path\%vFilename%.xlsx

//A counter to see the script is running
Let>CheckCellCount=0
Let>val=

//Open the file
XLOpen>ExcelFileName,1,xl
WaitWindowOpen>%vFilename%.xls*
Wait>3
SetFocus>%vFilename%.xls*

//Loop waiting for cell input
Label>Test
  Add>CheckCellCount,1
  Wait>0.1
  XLGetCell>xl,ProcessSheet,15,6,val
  SetControlText>Macro Scheduler Message,TMemo,1,%CheckCellCount%%crlf%%val%
Goto>Test

[Dorian (MJT support)]

Excel can perform custom data verification, so this operation can be achieved entirely within Excel, taking the upper and lower values and giving a popup if anything outside those parameters is entered anywhere within a given range.

It's in

>Data >Data Verification >Data Tools

I think then it's >Settings >Custom...

... But there are plenty of tutorials for this online

[JRL]

Excel can perform custom data verification...

nodochau might have an alternate method to accomplish his task. I'm still curious about why XLGetCell> would crash if it is used to monitor cell input. Seems like this should work.

[nodochau]

Here is my latest code and it works fine at the first value and not works at the second value. I just adjusted some time delay and changed the location of the time delay in the program. Still not getting it works.

Code: Select all

XLOpen>C:\Users\...\149405.xlsx,1,xl
Wait>3
SetFocus>149405 - Excel
Let>row=15
Let>col=6
XLGetCell>xl,ProcessSheet,row,4,upper_val
XLGetCell>xl,ProcessSheet,row,5,lower_val
Let>is_working=TRUE
Let>warning=FALSE
Wait>1.5
//**BREAKPOINT**
OnEvent>CUSTOM,CheckCells,warning,PopupWindow
Label>start
Wait>1
Goto>start
SRT>CheckCells
  Wait>2
//**BREAKPOINT**    
    XLGetCell>xl,ProcessSheet,row,col,val
    If>val<>
      If>{(%val%>%upper_val%)OR(%val%<%lower_val%)}
        Let>warning=TRUE
      else
        Add>col,1
      Endif
    Endif
END>CheckCells

SRT>PopupWindow
  Wait>1
  MDL>Value %val% at row: %row% and column %col% %crlf% is out of Spec
  Let>empty_cell=
  XLSetCell>xl,ProcessSheet,row,col,empty_cell,
  Let>warning=FALSE
END>PopupWindow

[nodochau]

Excel can perform custom data verification, so this operation can be achieved entirely within Excel, taking the upper and lower values and giving a popup if anything outside those parameters is entered anywhere within a given range.

It's in

>Data >Data Verification >Data Tools

I think then it's >Settings >Custom...

... But there are plenty of tutorials for this online

Dorian,
Yes, I know excel has that option but I am not able to set that for every single spreadsheet for operators.
There is no excel template and we work on the sheets that our customers send to us.

Thanks

[Dorian (MJT support)]

Excel can perform custom data verification...

nodochau might have an alternate method to accomplish his task. I'm still curious about why XLGetCell> would crash if it is used to monitor cell input. Seems like this should work.

I just experimented with your example and it throws that error even if I type into a completely different cell, not just the one being monitored.

In a sense, this aspect is a timing issue, as it happens if the user attempts to enter data at the exact moment XLGetCell is running and causing Excel to be "busy".

If I increase the wait from 0.1 second to 5 seconds I can type in a good few cells before I'm unlucky enough to experience the clash. Of course that's not a solution - just a demonstration of the issue - and I'm sure it's only a matter of time before it raises it's head in the original OnEvent script.

I've experimented with the OnEvent script in this thread for a good few hours and have had varying results - but I haven't yet managed to consistently reproduce the same results whether they're success or failure. Sometimes it'll monitor the first few entries and then stop checking, sometimes it doesn't monitor any at all. tbh this one has really got under my skin.

But bearing in mind your discovery it may simply be that this method may not be a reliable one.

[JRL]

...and causing Excel to be "busy".

I don't have time to test but if multiple usage is the problem, might SHARING the files alleviate the issue?

[Dorian (MJT support)]

...and causing Excel to be "busy".

I don't have time to test but if multiple usage is the problem, might SHARING the files alleviate the issue?

I just tried it and unfortunately still had the same issue.