Script successes, but intermittent failure with GTA

[jculp]

I have used MacroScheduler for about 18 months now, and have documented labor savings of about $50,000 for my purchasing dept using the script below to create purchase orders from Excel spreadsheet data. However, there is one nagging problem I have not been able to fix.

Sometimes, on the very first row of spreadsheet data, the GetTextAtPoint function used at line 343 does not work; but after the first row, it works fine. I get the coordinates for the GTA at line 184, which is before the MainLoop starts. I can't figure out why it sometimes fails, but only on the first row of data.

I am posting my code, although it won't execute for anyone else, because it uses a proprietary application; but I am hoping that someone with broader experience may be able to tell me either why I get the intermittent failure, or how to proceed to troubleshoot it. I have done the debugging thing, looking at variable values, and determined that when it fails, the failure is directly at the point of GTA -- i.e., the result variable has no text in it. It is possible that this is caused by my target app, but it would be great if I could identify that.

Both the failed line and the successful lines run in the same section of code - at line 343. The screen coordinates are identified and captured earlier in the code, at line 184, and I do not get them again.

There are about 526 total lines in the macro.

//Put this VBSTART..VBEND block at top of script
//to declare the functions once
VBSTART
Dim xlApp
Dim xlBook
Dim xlSheet

'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = true
Set xlBook = xlApp.Workbooks.open(filename)
end sub

'Find open Active worksheet in Excel
Function GetActiveSpreadsheet
Set xlApp = GetObject(,"Excel.Application")
xlApp.visible = true
Set xlBook = xlApp.ActiveWorkbook
Set xlSheet = xlApp.ActiveSheet
GetActiveSpreadsheet = xlSheet.Name
end function

'Use this to close Excel later
Sub CloseExcel
xlApp.quit
Set xlApp = Nothing
End Sub

'Retrieves a cell value from the specified
'worksheet
Function GetCell(Sheet,Row,Column)
Dim xlSheet
Set xlSheet = xlBook.Worksheets(Sheet)
GetCell = xlSheet.Cells(Row, Column).Value
End Function

'Retrieves a cell value from the active worksheet
Function GetCellActive(Row,Column)
GetCell = xlSheet.Cells(Row, Column).Value
End Function

'Sets specified cell of specified worksheet
Function SetCell(Sheet,Row,Column,NewValue)
Dim xlSheet
Set xlSheet = xlBook.Worksheets(Sheet)
xlSheet.Cells(Row,Column).Value = NewValue
End Function

'multiply 2 numbers
Function XX(a, b)
XX = a * b
End Function
VBEND
GetTime>StartTime
SRT>SetFont12
//Set Font to 12, not default
//font size of 12 is essential for capturing the PO number consistently
//using MouseMoveRel, then GetCursorPos, then GetTextAtPoint
//only way I have found to reliably, consistently capture the PO
Press Alt
Wait>0.07
Send>o
Wait>0.07
Send>f
Wait>0.07
Send>s
Release Alt
Wait>0.07
Send>12
Wait>0.07

/*
Press Alt
Send>u
Wait>0.1
Release Alt
Send>SPACE
Wait>0.1

*/
Press Enter
Wait>0.1
END>SetFont12

//check for TEST or PROD
IfWindowOpen>Host: laws-tst-1
Let>host=TEST
Else
Let>host=PROD
Endif
//MessageModal>host

SK_IGNORECAPS=1

//first section is assignment of spreadsheet column numbers to variables
//header info
//po_date is 3rd screen field, but formatted from today's date
Let>company=1
Let>buyer=1
Let>vendor=2
Let>pfrom=3
Let>ShipTo=08200
Let>Dflt_date=4

//line item detail info
Let>item=5
Let>qty=6
Let>cost=7

//after PO Add
Let>comments=8
Let>po_min=9

//Excel column to put PO number in
Let>POnum=10
//and results messages after attempted Add/Changes for each step
Let>msgAdd=11
Let>msgComments=12
Let>msgTot=13
Let>msgRelease=14

//next section formats today's date for Lawson PO Date field
Month>get_mo
VBEval>XX(%get_mo%,10000),po_date
Day>get_day
VBEval>XX(%get_day%,100),day_add
Add>po_date,day_add
Year>get_yr
Sub>get_yr,2000
Add>po_date,get_yr
//send po_date along with the spreadsheet data

//2 different wait variables
//WaitTime for sending data to screen
Let>WaitTime=0.25
//WaitChange for making changes to screen (adding/changing PO/lines, clearing screen, going to subform, etc.
Let>WaitChange=1.5
//CursorChanged is timeout when waiting for cursor to stop hourglass
Let>CursorChanged=30

//if PO20 is already open, go to it
IfWindowOpen>Lawson - Purchase Order Entry (PO20.1)
SetFocus>Lawson - Purchase Order Entry (PO20.1)
messagemodal>Please be sure that:%CRLF% - the PO20 screen is cleared (Shift-F3)%CRLF% - the cursor is in the Company field%CRLF% - your spreadsheet is open%CRLF%before proceeding.%CRLF%%CRLF%Release the mouse; press Enter to continue.
Press Shift
Wait>0.05
Press F3
Wait>0.05
Release Shift
Wait>0.05
Goto>Start_PO20
Endif


//if PO20 is not open, then we have to open it up
SetFocus>Lawson Open Enterprise Applications
MouseMove>673,344
Wait>0.01
//Open a new form transfer window
Press F8
WaitWindowOpen>Form Transfer (@UFT.1)
Send>po20
Wait>WaitTime
Press Enter

//Wait an appropriate length of time for the new Form to open
// Let>WW_TIMEOUT=30
WaitWindowOpen>Lawson - Purchase Order Entry (PO20.1)

Label>Start_PO20
//this sets the Workbook variable to the active workbook, and the spreadsheet variable to the active worksheet, and returns the active sheet name
VBEval>GetActiveSpreadsheet,xlActive

SetFocus>Lawson - Purchase Order Entry (PO20.1)
Wait>WaitTime

GoSub>SetFont12

MoveWindow>Lawson - Purchase Order Entry (PO20.1),100,100
Wait>WaitTime
MouseMoveRel>110,127
Wait>WaitTime
GetCursorPos>x,y
// MessageModal>%x%, %y%
//Get user input on which spreadsheet rows to process
Input>rowStart,Enter row to start,2
Input>rowEnd,Now enter row to end
Let>RowCount=%rowEnd%-%rowStart%
Add>RowCount,1
If>%rowEnd%>%rowStart%
Input>maxlines,Do you want a specific number of lines per PO? %CRLF%(Enter 1 or 4 or 0. 0 means all Vendor-Pfrom lines on one PO.),1
If>%maxlines%=0
Let>maxlines=9999
Endif
Else
Let>maxlines=1
Endif
Input>release_PO,Do you want to auto-release the POs?%CRLF%(Enter 0 to release all meeting dollar constraints;%CRLF%1 - release only if all lines create;%CRLF%2 - no releases at all.),0

Let>r=rowStart
Sub>r,1
Message>Script is running. Use SHIFT-ESC keys to stop the script. Use the Pause key to pause execution. %CRLF%This message box will stay open until script finishes.
repeat>r
Label>MAIN_LOOP
Let>Failed=0
Add>r,1
//Header data section
//focus on Lawson
SetFocus>Lawson - Purchase Order Entry (PO20.1)
Wait>WaitTime

//then send the company number, tab to the PO field
Send>%company%
Wait>WaitTime
Press Tab
Wait>WaitTime

//send buyer code
VBEval>GetCell("%xlActive%",%r%,%buyer%),xlValue
Send>%xlValue%
Wait>WaitTime
//Press Tab
//Press Tab not needed?
//buyer code should fill up the field

//send PO Date
Send>%po_date%
Wait>WaitTime
If>%po_date%>93200
Press Tab * 3
Else
Press Tab * 4
Endif

//send vendor
VBEval>GetCell("%xlActive%",%r%,%vendor%),xlVendor
Send>%xlVendor%
Wait>WaitTime
Press Tab

//send pfrom
VBEval>GetCell("%xlActive%",%r%,%pfrom%),xlPfrom
ConCat>xlVendor,xlPfrom
Send>%xlPfrom%
Wait>WaitTime
//Press Tab not needed because Pfrom fills up the field

//send shipto
// VBEval>GetCell("%xlActive%",%r%,%%),xlValue
Send>%ShipTo%
Wait>WaitTime
Press Tab

//check and send Deflt Del Date
VBEval>GetCell("%xlActive%",%r%,%Dflt_date%),xlValue
If>%xlValue%>0
Send>%xlValue%
Wait>WaitTime
Endif
If>%xlValue%>93200
Press Tab * 4
Else
Press Tab * 5
Endif

//Line detail section
Label>AddLines

//Action is Add for 1st line, Change for subsequent lines on single PO
Let>Action=a
Let>LineCount=0
Repeat>LineCount
//send FC value
Send>a
Wait>WaitChange

//send item
VBEval>GetCell("%xlActive%",%r%,%item%),xlValue
Send>%xlValue%
Wait>WaitTime
Press Tab

//send quantity
VBEval>GetCell("%xlActive%",%r%,%qty%),xlValue
Send>%xlValue%
Wait>WaitTime
Press Tab

//skip UOM
Press Tab
Wait>WaitTime

//send unit cost if populated
VBEval>GetCell("%xlActive%",%r%,%cost%),xlValue
If>%xlValue%>0
Send>%xlValue%
Wait>WaitTime
Press Tab
Endif

//perform Action-Add/Change sequence
Press ALT
Release ALT
Send>a
Wait>0.01
Send>%Action%
//Wait>WaitChange
WaitReady>0
//testing using WaitCursorChanged to minimize processing speed differences

//get the results msg, but don't wait forever for it to show up
//the Wait>0.05 with the Until limits the wait to 5 seconds for every 100
Let>secs=0
Repeat>secs
Add>secs,1
GetControlText>Lawson - Purchase Order Entry (PO20.1),STATIC_UNIV,1,strText
If>strText>%SPACE%
Let>secs=900
Endif
Wait>0.05
Until>secs=900

VBEval>SetCell("%xlActive%",%r%,%msgAdd%,"%strText%"),nul
Position>complete,strText,1,result

If>result=0
Sub>LineCount,1
Press Up
Wait>WaitTime
Press Tab
Wait>WaitTime
Press Shift
Press F3
Release Shift
Wait>WaitChange
Press Down * 5
Let>Failed=1
//started with Goto MoreLines, but removed it to capture PO num even when line fails
//Goto MoreLines
Endif

//get PO number and put in Excel
SetFocus>Lawson - Purchase Order Entry (PO20.1)
Wait>WaitTime
GetTextAtPoint>x,y,mouseText,nChar
Wait>WaitTime
// MessageModal>%x%, %y%; %mouseText%, %nChar%
MidStr>%mouseText%,10,9,strSub
VBEval>SetCell("%xlActive%",%r%,%ponum%,"%strSub%"),nul
Wait>WaitTime

//evaluate next row on spreadsheet to see if more lines for this vendor-pfrom
//if more lines, then continue to do Action-Change
Label>MoreLines
Let>NextRow=%r%
Add>NextRow,1
VBEval>GetCell("%xlActive%",%NextRow%,%vendor%),xlVend2
VBEval>GetCell("%xlActive%",%NextRow%,%pfrom%),xlPfr2
ConCat>xlVend2,xlPfr2
//this compares vendor and pfrom from current and next rows on spreadsheet
If>%xlVend2%=%xlVendor%
Add>LineCount,1
If>LineCountr,1
Let>Action=c
If>r>rowEnd
Sub>r,1
Goto>PO_Completion
Endif
Press Tab * 15
Wait>WaitTime
Press Shift
Press F3
Release Shift

Wait>WaitChange
Endif
Else
Let>LineCount=%maxlines%
Endif
Until>LineCount=%maxlines%

//Once the vendor and purchase from no longer match,
//or the max lines per PO are reached,
//or the end of the specified spreadsheet rows are reached,
//move on and complete the PO

Label>PO_Completion
//do po completion stuff
//on PO20 screen
Let>finish=release_PO
Add>finish,%Failed%
SetFocus>Lawson - Purchase Order Entry (PO20.1)
Wait>WaitTime
//insert goto to skip totals
Goto>ClearScreen

Label>Add_Comments

Label>Check_Total
SetFocus>Lawson - Purchase Order Entry (PO20.1)
Wait>WaitTime
Press Alt
Send>s
Wait>WaitTime
Send>4
Wait>WaitTime
Release Alt
Wait>WaitChange
Let>WW_TIMEOUT=30

//duplicate sections below, to allow for Test and Prod discrepancy on screen name
// PO Totals (20.5) vs Nulls (PO20.5)

If>host=PROD
//first section for PROD
WaitWindowOpen>Nulls (PO20.5)
If>WW_RESULT=TRUE
// IfWindowOpen>Nulls (PO20.5)
// GoSub>SetFont12
MoveWindow>Nulls (PO20.5),200,200
MouseMoveRel>118,392
GetCursorPos>xtot,ytot
GetTextAtPoint>xtot,ytot,totText,nChar
MidStr>totText,17,15,strSub

VBEval>SetCell("%xlActive%",%r%,%msgTot%,"%strSub%"),nul
Wait>WaitTime
VBEval>GetCell("%xlActive%",%r%,%msgTot%),pototal

SetFocus>Nulls (PO20.5)
Press Alt
Send>f
Release Alt
Wait>0.1
Press Down * 3
Wait>WaitTime
Press Enter
VBEval>GetCell("%xlActive%",%r%,%po_min%),vendor_min
//messagemodal>%pototal%
If>%pototal%>30000,ClearScreen
If>%pototal%finish>1,ClearScreen,PO_Release

Else
//second section for TEST region during development
WaitWindowOpen>PO Totals (PO20.5)
If>WW_RESULT=TRUE
// IfWindowOpen>PO Totals (20.5)
// GoSub>SetFont12
MoveWindow>PO Totals (PO20.5),200,200
MouseMoveRel>118,392
GetCursorPos>xtot,ytot
GetTextAtPoint>xtot,ytot,totText,nChar
MidStr>totText,17,15,strSub

VBEval>SetCell("%xlActive%",%r%,%msgTot%,"%strSub%"),nul
Wait>WaitTime
VBEval>GetCell("%xlActive%",%r%,%msgTot%),pototal

SetFocus>PO Totals (PO20.5)
Press Alt
Send>f
Release Alt
Wait>0.1
Press Down * 3
Wait>WaitTime
Press Enter
VBEval>GetCell("%xlActive%",%r%,%po_min%),vendor_min
//messagemodal>%pototal%
If>%pototal%>30000,ClearScreen
If>%pototal%finish>1,ClearScreen,PO_Release
Endif


Label>PO_Release
If>finish>1,ClearScreen
SetFocus>Lawson - Purchase Order Entry (PO20.1)
Wait>WaitTime
Press Alt
Send>a
Release Alt
Press Down * 5
Press Enter
//Wait>WaitChange
//Send>r
//Release Alt
//WaitCursorChanged>CursorChanged
Wait>WaitChange
Repeat>secs
Add>secs,1
GetControlText>Lawson - Purchase Order Entry (PO20.1),STATIC_UNIV,1,strRelease
If>strRelease>%SPACE%
Let>secs=200
Endif
Wait>0.05
Until>secs=200
Position>Release,%strRelease%,1,nRelease,FALSE
If>nRelease=0
Let>strRelease=Failed to release
Endif
VBEval>SetCell("%xlActive%",%r%,%msgRelease%,"%strRelease%"),nul

Label>ClearScreen
//messagemodal>now at ClearScreen
SetFocus>Lawson - Purchase Order Entry (PO20.1)
Press Shift
Wait>WaitTime
Press F3
Wait>WaitChange
Release Shift
Wait>WaitTime

//MessageModal>At UNTIL statement, r=%r%
//MessageModal>ready for next vendor-pfrom just finished row %r%
Until>r=%rowEnd%
//end of main loop

GetDate>Today
GetTime>Now
Let>logtext=%USER_NAME%%TAB%%COMPUTER_NAME%%TAB%%RowCount%%TAB%%Today%%TAB%%StartTime%%TAB%%Now%%TAB%%SCRIPT_FILE%
Let>logtext2=%SCRIPT_FILE%%TAB%%RowCount%%TAB%%Today%%TAB%%Now%
WriteLn>S:\Departments\Procurement\Automation\logs\po_add_log_notot.txt,result,%logtext%
If>%result%Write Line method failed. Notify Jim Culp at x1848. Lines = %RowCount%
Endif
//MessageModal>%logtext2%
MessageModal>End of Script
//at end of script file

[Marcus Tettmar]

The text capture libraries are at the mercy of other processes and applications.

Some things to try to improve reliability:

1. Call GetTextReset before using the text capture functions. Before each one if you like though it might incur a small delay - it reloads the libraries.

2. Use GetTextAtPoint/GetTextInRect in a loop, looping until text is returned. Sometimes, some apps, at some tiny moment in time will not report text but will do so a nanosecond later. So try using a loop. Notice that the Text Capture sample macro operates in a loop. So you could try, e.g.:

Label>waitfortext
GetTextAtPoint>x,y,text
If>text,waitfortext

[jculp]

Thanks for the reply. I'll try putting a loop in at this point. I already use a loop at other points in this script, but I hadn't yet tried it for this GTA.