Jump to content

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


Recommended Posts

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

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:

image.thumb.png.aa88465fbd5b619c83a81a4001ff8496.png

The search results in an array:

image.png.168519f41ea7f96267f125c47cf59535.png

 

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

;~ 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!

Link to post
Share on other sites

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

 

Link to post
Share on other sites

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:

image.png.032d6905f151ba730d1ad9a49177c841.png

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

Any suggestions? :think:

 

[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 by XinYoung
Link to post
Share on other sites

From a good search, I get this array:

image.png.c6c3177a0e313daa116963cdb908ad1d.png

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: 

image.png.9a5627a2c4416c34a96bce6d2d68a08a.png

 

--> 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

image.png.6f415a9a2ad5e5fad3151fe98a7ed8d4.png

 

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By EmilyLove
      I have a string containing the full path of an executable and an array of executables without their paths. I am trying to compare the string to the list in the array and if a match is found, remove it from the array. The entry get removed from the array successfully, and after checking its return result, uses it to update the ubound if it succeeded, but it doesn't want to update to the new value. Any ideas what I am doing wrong? It acts like it is read-only.
      #include <Array.au3> #include <File.au3> Local $sApp_Exe = "F:\App\Nextcloud\nextcloud.exe" Local $aWaitForEXEX = [3, "Nextcloud.exe", "nextcloudcmd.exe", "QtWebEngineProcess.exe"] For $h = 1 To $aWaitForEXEX[0] If StringInStr($sApp_Exe, $aWaitForEXEX[$h]) <> 0 Then $iRet = _ArrayDelete($aWaitForEXEX, $h) If $iRet <> -1 Then $aWaitForEXEX[0] = $iRet ;this line doesn't work. $aWaitForEXEX[0] doesn't update and shortly gives Error: Array variable has incorrect number of subscripts or subscript dimension range exceeded.: _ArrayDisplay($aWaitForEXEX) EndIf Next  
    • By vinnyMS
      #Include <Array.au3> #include <Constants.au3> $s = FileRead("2.txt") Local $w = StringRegExp($s, '(?is)(\b\w+\b)(?!.*\b\1\b)', 3) _ArrayColInsert($w, 1) For $i = 0 to UBound($w)-1 StringRegExpReplace($s, '(?i)\b' & $w[$i][0] & '\b', $w[$i][0]) $w[$i][1] = @extended Next _ArraySort($w, 1, 0, 0, 1) _ArrayDisplay($w) i have this script that returns 3 columns  
       
      i need to copy the  Col 0 and Col 1 as text to paste on notepad or excel
      you will have to create a "copy" button if possible
      array.au3 2.txt
    • By DannyJ
      I have a dataset like this, (a strubg)
      Username: User1 Type: Admin RegDate: 1999 Username: User2 Type: User RegDate: 2000 How to make a 2 dimensional array that I can display with _ArrayDisplay?
      This would be a perfect 2D array to represent my data:
      Username           Tpye RegDate User1              Admin 1999 User2              User 2000   If you run this Powershell this powershell command, you can get this dataset that I am talking about:
      Get-LocalUser | Select * With this code you can try it to read into a string:
      #include <GuiConstantsEx.au3> #include <WindowsConstants.au3> #include "GUIListViewEx.au3" #include <Array.au3> ; Just for display in example #RequireAdmin #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_UseX64=y #EndRegion $sCommand = "powershell.exe Get-LocalUser | Select *" Local $iPid = Run($sCommand, @WorkingDir , @SW_SHOW , $STDOUT_CHILD) ProcessWaitClose($iPid) Local $sOutput = StdoutRead($iPID) ConsoleWrite($sOutput) How can I correctly split $sOutput into a 2D array (with the above mentioned layout) that I can display and I work with?
    • By kovlad
      My solution is to write nested arrays without copying.
      The problem was described hier.
       
      Function:
      #include <Array.au3> ; #FUNCTION# ==================================================================================================================== ; Name ..........: _ArrayNestedSet ; Description ...: Assigns a value to an element of a nested 1D array. ; Syntax ........: _ArrayNestedSet(ByRef $aArray, $vIndex, $vValue) ; Parameters ....: $aArray - an array of arrays. ; $vIndex - an index or 1d-array of indexes; ; a size if $vValue not defined (zero to delete). ; $vValue - a value (create, resize or delete if not defined). ; ; Return values .: on success - 1 ; @extended - nesting level of operation ; on failure - 0 ; @extended - nesting level of error ; @error = 1 - invalid array ; @error = 2 - invalid index ; Author ........: ; Modified ......: kovlad ; Remarks .......: ; Related .......: ; Link ..........: https://www.autoitscript.com/forum/topic/185638-assign-a-value-to-an-array-in-array-element/ ; https://www.autoitscript.com/trac/autoit/ticket/3515?replyto=description ; Example .......: Yes ; =============================================================================================================================== Func _ArrayNestedSet(ByRef $aArray, $vIndex, $vValue = Default) Local $extended = @extended + 1 If IsArray($vIndex) Then If UBound($vIndex, 0) <> 1 Then _ Return SetError(2, $extended) If UBound($vIndex) > 1 Then If UBound($aArray, 0) <> 1 Then _ Return SetError(1, $extended) ; keep index for this array Local $i = $vIndex[0] If $i < 0 Or UBound($aArray) <= $i Then _ Return SetError(2, $extended) ; delete index of this array _ArrayDelete($vIndex, 0) ; recursive function call Local $return = _ArrayNestedSet($aArray[$i], $vIndex, $vValue) If @error Then Return SetError(@error, @extended + 1, 0) Else Return SetExtended(@extended + 1, 1) EndIf Else $vIndex = $vIndex[0] EndIf EndIf If $vValue = Default Then If $vIndex < 0 Then _ Return SetError(2, $extended) If $vIndex = 0 Then ; delete array and free memory $aArray = 0 Return SetExtended($extended, 1) EndIf If UBound($aArray, 0) = 1 Then ; resize array keeping data ReDim $aArray[$vIndex] Return SetExtended($extended, 1) Else ; create new nested array Local $aTmp[$vIndex] $aArray = $aTmp Return SetExtended($extended, 1) EndIf Else If UBound($aArray) <= $vIndex Then _ Return SetError(2, $extended + 1) ; set value of array entry $aArray[$vIndex] = $vValue Return SetExtended($extended, 1) EndIf EndFunc  
      Examples:
      ; write value to 1st nested array ConsoleWrite("@@ Debug(" & @ScriptLineNumber & ") : write value to 1st nested array" & @CRLF) Local $aTmp1[4] = [1,2,3,4] _ArrayDisplay($aTmp1, "$aTmp1") Local $aArray[2] = [$aTmp1] ConsoleWrite( _ "_ArrayNestedSet($aArray[0], 3, 14) = " & _ArrayNestedSet($aArray[0], 3, 14) & @CRLF & _ " @error = " & @error & @CRLF & _ " @extended = " & @extended & @CRLF & @CRLF) _ArrayDisplay($aArray[0], "$aArray[0]") ; resize 1st nested array ConsoleWrite("@@ Debug(" & @ScriptLineNumber & ") : resize 1st nested array" & @CRLF) ConsoleWrite( _ "_ArrayNestedSet($aArray[0], 8) = " & _ArrayNestedSet($aArray[0], 8) & @CRLF & _ " @error = " & @error & @CRLF & _ " @extended = " & @extended & @CRLF & @CRLF) _ArrayDisplay($aArray[0], "$aArray[0]") ; write array to 1st nested array ConsoleWrite("@@ Debug(" & @ScriptLineNumber & ") : write array to 1st nested array" & @CRLF) Local $aTmp11[4] = [11,12,13,14] _ArrayDisplay($aTmp11, "$aTmp11") ConsoleWrite( _ "_ArrayNestedSet($aArray[0], 2, $aTmp11) = " & _ArrayNestedSet($aArray[0], 2, $aTmp11) & @CRLF & _ " @error = " & @error & @CRLF & _ " @extended = " & @extended & @CRLF & @CRLF) _ArrayDisplay(($aArray[0])[2], "($aArray[0])[2]") ; write value to 2nd nested array using index array ConsoleWrite("@@ Debug(" & @ScriptLineNumber & ") : write value to 2nd nested array using index array" & @CRLF) Local $aIndex1[2] = [2,3] _ArrayDisplay($aIndex1, "$aIndex1") ConsoleWrite( _ "_ArrayNestedSet($aArray[0], $aIndex1, 140) = " & _ArrayNestedSet($aArray[0], $aIndex1, 140) & @CRLF & _ " @error = " & @error & @CRLF & _ " @extended = " & @extended & @CRLF & @CRLF) _ArrayDisplay(($aArray[0])[2], "($aArray[0])[2]") ; resize 2nd nested array ConsoleWrite("@@ Debug(" & @ScriptLineNumber & ") : resize 2nd nested array" & @CRLF) Local $aIndex1[2] = [2,8] _ArrayDisplay($aIndex1, "$aIndex1") ConsoleWrite( _ "_ArrayNestedSet($aArray[0], $aIndex1) = " & _ArrayNestedSet($aArray[0], $aIndex1) & @CRLF & _ " @error = " & @error & @CRLF & _ " @extended = " & @extended & @CRLF & @CRLF) _ArrayDisplay(($aArray[0])[2], "($aArray[0])[2]") ; create new 3rd nested array ConsoleWrite("@@ Debug(" & @ScriptLineNumber & ") : create new 3rd nested array" & @CRLF) Local $aIndex2[3] = [2,7,6] _ArrayDisplay($aIndex2, "$aIndex2") ConsoleWrite( _ "_ArrayNestedSet($aArray[0], $aIndex2) = " & _ArrayNestedSet($aArray[0], $aIndex2) & @CRLF & _ " @error = " & @error & @CRLF & _ " @extended = " & @extended & @CRLF & @CRLF) _ArrayDisplay((($aArray[0])[2])[7], ")($aArray[0])[2])[7]") ; delete 3rd nested array ConsoleWrite("@@ Debug(" & @ScriptLineNumber & ") : delete 3rd nested array" & @CRLF) Local $aIndex3[3] = [2,7,0] _ArrayDisplay($aIndex3, "$aIndex2") ConsoleWrite( _ "_ArrayNestedSet($aArray[0], $aIndex3) = " & _ArrayNestedSet($aArray[0], $aIndex3) & @CRLF & _ " @error = " & @error & @CRLF & _ " @extended = " & @extended & @CRLF) ConsoleWrite("IsArray((($aArray[0])[2])[7]) = " & IsArray((($aArray[0])[2])[7]) & @CRLF & @CRLF) ; write 0 in 1st nested array to delete the 2nd nested array ConsoleWrite("@@ Debug(" & @ScriptLineNumber & ") : write 0 in 1st nested array to delete the 2nd nested array" & @CRLF) Local $aIndex4[1] = [2] _ArrayDisplay($aIndex4, "$aIndex4") ConsoleWrite( _ "_ArrayNestedSet($aArray[0], $aIndex4, 0) = " & _ArrayNestedSet($aArray[0], $aIndex4, 0) & @CRLF & _ " @error = " & @error & @CRLF & _ " @extended = " & @extended & @CRLF) ConsoleWrite("IsArray(($aArray[0])[2]) = " & IsArray(($aArray[0])[2]) & @CRLF & @CRLF) ; delete 1st nested array (same as '$aArray[0] = 0') ConsoleWrite("@@ Debug(" & @ScriptLineNumber & ") : delete 1st nested array (same as '$aArray[0] = 0')" & @CRLF) Local $aIndex5[1] = [0] _ArrayDisplay($aIndex5, "$aIndex5") ConsoleWrite( _ "_ArrayNestedSet($aArray[0], $aIndex5) = " & _ArrayNestedSet($aArray[0], $aIndex5) & @CRLF & _ " @error = " & @error & @CRLF & _ " @extended = " & @extended & @CRLF) ConsoleWrite("IsArray($aArray[0]) = " & IsArray($aArray[0]) & @CRLF & @CRLF)  
    • By DannyJ
      $sCommands1 = 'powershell.exe Get-ChildItem' $iPid = run($sCommands1   , @WorkingDir , @SW_SHOW , 0x2) $sOutput = ""  While 1     $sOutput &= StdoutRead($iPID)         If @error Then             ExitLoop         EndIf  WEnd ;~ msgbox(0, '' , $sOutput) ConsoleWrite("$sOutput") ConsoleWrite($sOutput) ConsoleWrite(@CRLF) $aOutput = stringsplit($sOutput ,@LF , 2) For $i=0 To  UBound($aOutput) - 1 Step 1     ConsoleWrite($aOutput[$i]) Next The script above reads the whole directory into a one dimensional array, but I need to work with the array, so I need to split the array into multiple dimensions.
      I have already read some forum answers here, and I have already tried these commands:
       
      Are there any way to use the $aOutput variable like in PowerShell:
      PowerShell:
      $a = Get-ChildItem $a.Mode I imagine this in AutoIt  $aOutput
      ConsoleWrite($aOutput[i].Mode) Or if I split this command into 2 dimension like:
      For $i To UBound($aOutput)-1 Step 1 ConsoleWrite($aOutput[$i][1]) ConsoleWrite($aOutput[$i][2]) Next  
×
×
  • Create New...