How to Set Value in Excel based on a variable

[rishi.trivedi]

Hello ,
I am trying to return a 1, 0 provided my task completed.

here's my code

//Specify the path of the Excel file here
Let>filename=C:\Users\rtrivedi\Desktop\Test.xlsx
IfFileExists>filename
//Start Excel and open the book
XLOpen>filename,1,xlH
//Ask how many rows we should get
Input>maxrows,How many rows shall I get?,1
If>maxrows=0
Exit>0
Endif
//Data starts on row 2 - row 1 has header

Let>row=1
Repeat>row
Let>row=row+1
//get the fields for this row
XLGetCell>xlH,Sheet1,row,1,field_1
XLGetCell>xlH,Sheet1,row,2,field_2
//trim the results (Excel sometimes adds CRLFs)
Trim>field_1,Field_1
Trim>field_2,Field_2
//we'll just paste each row into CRM

SetFocus>Patient Programs Active Programs - Microsoft Dynamics CRM - Internet Explorer

//Move mouse to insert value from Excel

//Click on Patient Program
Let>FIP_SCANPIXELS=ALL
FindImagePos>C:\Users\rtrivedi\Desktop\PP_Screen.bmp,SCREEN,0,1,XArr,YArr,numFound
If>numFound=0
MessageModal>Image Not Found
Exit>0
Endif
MouseMove>XArr_0,YArr_0
LClick


Wait > 3

//Click on Search
MouseMove>-452,53
LDblClick
LDblClick
Press Delete
Wait> 3
Send>%field_1%
Wait>3
Press Enter
Wait>3
Press Down
Wait>3
Press Down
Wait >3
Press Enter
Wait> 4
Press Tab
Wait> 3
Press Tab
Wait> 3
Press Tab
Wait> 3
Press Tab
Wait> 3
Press Tab
Wait> 3
Press Tab
Wait> 3
Press Tab
Wait> 3
Press Tab
Wait> 3
Press Tab
Wait> 3
Press Tab
Wait> 3
Press Tab
Wait> 3

//Enter Date in Fill Date Section in CRM
Send >%field_2%
Wait> 2
//Enter Date And Close
Press LALT
Wait>0.05
Send>s
Wait>1
Release LALT -
IfWindowOpen>Message from webpage--------- if this window is clicked the value 1 must be stored in b
Press Enter
Let>b=1
XLSetCell>xlH,Sheet1,row,3,b,scresult-- the value of b should be copied to excel in column 3
Else
Wait>8
Until>row>maxrows
Else
MessageModal>Could not find: %filename%
Endif

[JRL]

Kind of cleaned things up but can't test anything except the excel part. See the comments. LEt us know how it went.

Code: Select all

//Specify the path of the Excel file here
Let>filename=%desktop_dir%\Test.xlsx
IfFileExists>filename
  //Start Excel and open the book
  XLOpen>filename,1,xlH
  //Ask how many rows we should get
  Input>maxrows,How many rows shall I get?,1
  If>maxrows=0
    Exit>0
  Endif
  //Data starts on row 2 - row 1 has header
  Let>row=1
  Repeat>row
    Let>row=row+1
    //get the fields for this row
    XLGetCell>xlH,Sheet1,row,1,field_1
    XLGetCell>xlH,Sheet1,row,2,field_2
    //trim only removes spaces
    //StringReplace> the results (Excel sometimes adds CRLFs and TABs)
    StringReplace>field_1,crlf,,field_1
    StringReplace>field_1,tab,,field_1
    StringReplace>field_2,crlf,,field_2
    StringReplace>field_2,tab,,field_2


    //Can't test any of the CRM software control.  Cleaned it up so it is
    //syntactically correct.

    //we'll just paste each row into CRM

    SetFocus>Patient Programs Active Programs - Microsoft Dynamics CRM - Internet Explorer

    //Move mouse to insert value from Excel

    //Click on Patient Program
    Let>FIP_SCANPIXELS=ALL
    FindImagePos>%desktop_dir%\PP_Screen.bmp,SCREEN,0,1,XArr,YArr,numFound
    If>numFound=0
      MessageModal>Image Not Found
      Exit>0
    Endif
    MouseMove>XArr_0,YArr_0
    LClick

    Wait>3
    //Click on Search
    MouseMove>452,53
    LDblClick
    LDblClick
    Press Delete
    Wait>3
    Send>%field_1%
    Wait>3
    Press Enter
    Wait>3
    Press Down
    Wait>3
    Press Down
    Wait>3
    Press Enter
    Wait>4
    Press>Tab
    Wait>3
    Press>Tab
    Wait>3
    Press>Tab
    Wait>3
    Press>Tab
    Wait>3
    Press>Tab
    Wait>3
    Press>Tab
    Wait>3
    Press>Tab
    Wait>3
    Press>Tab
    Wait>3
    Press>Tab
    Wait>3
    Press>Tab
    Wait>3
    Press>Tab
    Wait>3

    //Enter Date in Fill Date Section in CRM
    Send>%field_2%
    Wait>2
    //Enter Date And Close
    Press LALT
    Wait>0.05
    Send>s
    Wait>1
    Release LALT
    IfWindowOpen>Message from webpage/*--------- if this window is clicked the value 1 must be stored in b */
      Press Enter
      Let>b=1
      XLSetCell>xlH,Sheet1,row,3,b,scresult/*-- the value of b should be copied to excel in column 3 */
    Else
      //From your opening statement I think you want a "0" placed if the window is not open.
      //If not, delete the next two lines.
      Let>b=0
      XLSetCell>xlH,Sheet1,row,3,b,scresult/*-- the value of b should be copied to excel in column 3 */
      
      Wait>8
    EndIf
  Until>row>maxrows
Else
  MessageModal>Could not find: %filename%
Endif

//You need to save the work and quit out of excel.
XLSave>xlH,filename
XLQuit>xlH