If...Then - Based on string in an array?

[XinYoung]

Hello all! I hope everyone is enjoying their holiday festivities. 

I'm working on a script that involves copying a string of text from an Excel workbook and searching for it in a particular website's search tool. If a result is found, it will do something. If not, it will do something else.

So far, it can successfully execute the search -- and then it shows me the results in an array.

Screenshot of the successful search:

The search results in an array:

 

Here's the code (sorry for all my comments):

expandcollapse popup

;~ All the functions this app performs require the external files listed here. So, theyre "included".
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <WinAPIFiles.au3>
#include <Array.au3>
#include <File.au3>
#include <Excel.au3>
#include <DateTimeConstants.au3>
#include <MsgBoxConstants.au3>
#include <WinAPIShellEx.au3>
#include <Date.au3>
#include <ComboConstants.au3>
#include <Misc.au3>
#include <WinAPIProc.au3>
#include <WinAPISys.au3>
#include <WinAPIConstants.au3>
#include <Crypt.au3>
#include <ColorConstants.au3>
#include <guimenu.au3>
#include <IE.au3>

;~ Kill all functions and close the app at anytime by pressing F4.
HotKeySet("{F4}", "_Exit")
;~ Keep track whether or not a file is selected. When the program first opens, a file is currently not selected.
Global $FileChosen = 0
;~ The app must remember certain strings of text:
  ;~    1. Login page
  Global $urlBBLogin = "website.com"
  ;~    2. Credentials
  Global $bbUsername = "USER"
  Global $bbPassword = "PW"
  ;~  3. Search page
  Global $urlBBCourseSearch = "website.com/search"

;~ When you launch the app, the UI gets built and is displayed to the user in the center of the screen. the "Function" buttons are disabled until a file is chosen.
$MasterUI = GUICreate("Master Re-Creator", 469, 145, -1, -1)
$Label1 = GUICtrlCreateLabel("Choose the Excel file", 8, 8, 103, 17)
$Select = GUICtrlCreateButton("Select File", 16, 32, 75, 25)
$FileName = GUICtrlCreateLabel("[No File Selected]", 104, 40, 88, 17)
$Group1 = GUICtrlCreateGroup("Functions", 8, 72, 449, 65)
$CheckCourse = GUICtrlCreateButton("Check Courses Exist", 24, 96, 123, 25)
GUICtrlSetState(-1, $GUI_DISABLE)
$DeleteCourse = GUICtrlCreateButton("Delete Courses", 168, 96, 123, 25)
GUICtrlSetState(-1, $GUI_DISABLE)
$CopyCourse = GUICtrlCreateButton("Copy Courses", 312, 96, 123, 25)
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)

;~  While the UI is open, it listens for triggers (in this case, button presses).
While 1
    $UI = GUIGetMsg()
    Select
;~  If the app is closed, the _Exit() function is performed (same function the F4 hotkey calls).
        Case $UI = $GUI_EVENT_CLOSE
            _Exit()
;~  The user has clicked the "Select File" button, the _LocateGetFileName() function is performed. Go there.
        Case $UI = $Select
            _LocateGetFileName()
;~  The user has clicked the "Check Courses Exist" button.
        Case $UI = $CheckCourse
            _CheckCourses()
;~  Other buttons are not ready
    EndSelect
WEnd

;~  The user clicked the "Select File" button. This function will execute now.
Func _LocateGetFileName()
;~  Prepare the app to take note of the details of a file.
    Local $sDrive = "", $sDir = "", $sFileName = "", $sExtension = ""
;~  Open a File Explorer to allow the user to select a file. Only Excel files are allowed to be chosen.
    Global $ChosenFileName = FileOpenDialog("Locate File", @DesktopDir, "Excel Files (*.xlsx)|Excel Macro Files (*.xlsm)", BitOR(1, 2), "")
    If @error Then
        Return 0
    EndIf
;~  When an Excel file is selected, remember of the files location (path), file name, and file extension.
    $aPathSplit = _PathSplit($ChosenFileName, $sDrive, $sDir, $sFileName, $sExtension)
;~  Show me what file I selected in a Message Box.
    MsgBox(0, "Selected File", $sFileName)
;~  Display the chosen file name in the UI label (previously [No File Selected]) and make it green.
    GUICtrlSetData($FileName, "")
    $FileName = GUICtrlCreateLabel($sFileName, 104, 40)
    $FileName = GUICtrlSetColor($FileName, 0x32CD32)
;~ A file is now selected. The "Function" buttons are now enabled.
    Global $FileChosen = 1
    GUICtrlSetState($CheckCourse, $GUI_ENABLE)
    GUICtrlSetState($DeleteCourse, $GUI_ENABLE)
    GUICtrlSetState($CopyCourse, $GUI_ENABLE)
EndFunc   ;==>_LocateGetFileName

;~  The user clicked the "Check Courses" button. This function will execute now.
Func _CheckCourses()
;~  Disable the "Function" buttons again to prevent multiple processes.
    GUICtrlSetState($CheckCourse, $GUI_DISABLE)
    GUICtrlSetState($DeleteCourse, $GUI_DISABLE)
    GUICtrlSetState($CopyCourse, $GUI_DISABLE)
;~  Open a IE window and navigate to the login page.
    Global $oIE = _IECreate($urlBBLogin)
;~  Recognize the form on this page (login input boxes).
    Local $oForm = _IEFormGetObjByName($oIE, "login")
    Local $oTextLogin = _IEFormElementGetObjByName($oForm, "user_id")
    Local $oTextPass = _IEFormElementGetObjByName($oForm, "password")
;~  Enter the Automation user credentials into the form.
    _IEFormElementSetValue($oTextLogin, $bbUsername)
    _IEFormElementSetValue($oTextPass, $bbPassword)
;~  Click the Login button.
    _IEFormSubmit($oForm)

;~  Now that were logged in, navigate to the course search page.
    _IENavigate($oIE, $urlBBCourseSearch)
;~  Change the search criteria to "Course ID"
    _bbCourseSearchCategoryChange("Course ID")
    
;~  Open the selected Excel file
    Local $oAppl = _Excel_Open()
    Local $oWorkbook = _Excel_BookOpen($oAppl, $ChosenFileName, Default, Default, True)

;~ Copy just whats in cell A1 (for now)
    _Excel_RangeCopyPaste($oWorkbook.Worksheets(1), "A1")
    Global $WhatsCopied = ClipGet()
;~ Paste whats copied into the search text box and click submit
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $WhatsCopied)
    _IEFormSubmit($oForm)

;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    Local $aBBTableData = _IETableWriteToArray($oBBTable)
    _ArrayDisplay($aBBTableData)
EndFunc   ;==>_CheckCourses

;~ This function allows changing the search criteria.
Func _bbCourseSearchCategoryChange($sCategoryToSearch)
    Local $aSearchCategory[6] = ["Course ID", "Course Name", "Description", "Instructor", "Data Source Key", "Term"]
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchCategory = _IEGetObjByName($oForm, "courseInfoSearchKeyString")
    _IEAction($oSearchCategory, "focus")
    _IEFormElementOptionSelect($oSearchCategory, $aSearchCategory[$sCategoryToSearch], 1, "byText")
EndFunc   ;==>_bbCourseSearchCategoryChange

;~ All exit commands, including F4, calls this function
Func _Exit()
    Exit
EndFunc   ;==>_Exit

 

My main question is: How do I create an If... Then based on what is found in the search results? I need additional tasks to run if Col 1, Row 2 in the array contains the exact string I searched for. (Am I going about this the right way?)

My next question (I might make a new thread for): How do I make the whole thing loop, as in, copy the next cell in the Excel sheet and do the whole thing over again until there's no more? I understand that a For/Next loop thingy would be used. I just don't know how. Loops are really confusing to me.

Thank you all for your guidance and have a happy new year!

[Subz]

You can use _ArraySearch to find the index of the search item and if it equals -1 then you know the item was not found for example:

$iSearchIndex = _ArraySearch($aBBTableData, "Instructor Name", 0, 0, 0, 0, 1, 0)
If $iSearchIndex = -1 Then
    MsgBox(4096, "Search Error", "Item not found")
Else
    MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
EndIf

You can loop through the array by using the following, I've included both 1d and 2d example,

;~ 1d Array
Local $1dArray = [1,2,3,4,5]
Local $2dArray = [[1,10],[2,20],[3,30],[4,40],[5,50]]

;~ Instruction to loop from first row (0) to the last row (Ubound($1dArray) - 1) within the array
For $i = 0 To UBound($1dArray) - 1
    ;~ $1dArray[Row#]
    ;~ Example: $1dArray[$i] - Returns the row item
    ConsoleWrite($1dArray[$i] & @CRLF)
Next

;~ Instruction to loop from first row (0) to the last row (Ubound($2dArray) - 1) within the array
For $i = 0 To UBound($2dArray) - 1
    ;~ $2dArray[Row#][Column#]
    ;~ Example: $2dArray[$i][0] - Returns the rows first column item
    ;~ Example: $2dArray[$i][1] - Returns the rows second column item
    ConsoleWrite($2dArray[$i][0] & " = " & $2dArray[$i][1] & @CRLF)
Next

 

[XinYoung]

OK, I see what you're saying, but there's something weird about this website... I'm getting a "successful" search result even when nothing is found.

For example, I made it search for "blahblahblah", which definitely does not exist, and the array is still able to grab something and gave me the "Search Success" outcome.

I used ArrayDisplay to see exactly what it found, and it's this:

^So this is actually a "failed" search...

Any suggestions? 

 

[Edit] My mistake, even with this result I got the "Search Error" msgbox so I guess it's working. Let me see if I can get it to do the thing now... Thank you! [Edit]

Edited January 2, 2019 by XinYoung

[Subz]

Can you post your _ArraySearch code and also can you show the actual result of _ArraySearch?

[XinYoung]

From a good search, I get this array:

I used your exact code for _ArraySearch:

Func Forums()
    $iSearchIndex = _ArraySearch($aBBTableData, "Instructor Name", 0, 0, 0, 0, 1, 0)
    If $iSearchIndex = -1 Then
        MsgBox(4096, "Search Error", "Item not found")
    Else
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
    EndIf
EndFunc   ;==>Forums

Which gives me this: 

 

--> But if I change "Instructor Name" to "Course ID", I get the Search Error result. Why is that? I need it to find the Course ID. 

Func Forums()
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 0, 1, 0)
    If $iSearchIndex = -1 Then
        MsgBox(4096, "Search Error", "Item not found")
    Else
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
    EndIf
EndFunc   ;==>Forums

 

 

[Subz]

Can you try a partial search, might be other hidden characters in the string.

$iSearchIndex = _ArraySearch($aBBTableData, "Instructor Name", 0, 0, 0, 1, 1, 0)

 

[ModemJunki]

I think that "True" has to be set for the last parameter for the sub-index to be the search target in a 2d array.

$iSearchIndex = _ArraySearch($aBBTableData, "Instructor Name", 0, 0, 0, 1, 1, True)

 

[Subz]

I believe the last parameter selects row to search rather than column to search.

[XinYoung]

OMG the partial search fixed it!

Now I can try to make it do things! Thank you so much and happy new year!

I'll be back real soon