Jump to content
Sign in to follow this  

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:


The search results in an array:



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)

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

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

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

;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    Local $aBBTableData = _IETableWriteToArray($oBBTable)
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()
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!

Share this post

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")
    MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])

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)

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


Share this post

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:


^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

Share this post

Link to post
Share on other sites

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")
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
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")
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
EndFunc   ;==>Forums




Share this post

Link to post
Share on other sites

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

Share this post

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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Colduction
      Hi dear friends!, i'm sorry for creating a new thread (a new problem), i have over than 9 lists that i want to combine them to be this (in this example, there are 3 test files):

      I've written a little code for splitting main information, but i really confused how to make results as "Output.txt", here is that code:
      $sRegex_1 = StringRegExp(FileRead("1.txt"), '(?s:(?<=\=\=\r\n)(.*?)(?=\r\n\=\=))', 3) $sRegex_2 = StringRegExp(FileRead("2.txt"), '(?s:(?<=\=\=\r\n)(.*?)(?=\r\n\=\=))', 3) $sRegex_3 = StringRegExp(FileRead("3.txt"), '(?s:(?<=\=\=\r\n)(.*?)(?=\r\n\=\=))', 3) For $i = 0 To UBound($sRegex_1) - 1 ConsoleWrite($sRegex_1[$i] & @CRLF) For $j = 0 To UBound($sRegex_2) - 1 ConsoleWrite($sRegex_2[$j] & @CRLF) For $k = 0 To UBound($sRegex_3) - 1 ConsoleWrite($sRegex_3[$k] & @CRLF) Next Next Next  
    • By nacerbaaziz
      hello evrybody
      here is an example about how to split your texts using a delimiter with the ability to select how much of delimiters shows in each colum  with $i_number
      you have a long text and you want to split it in an array
      that evry colum have a number (n) of lines
      i made a function that do that for you
      just call it with a three params
      your text
      the number that you want to put in each col
      the siparator
      default is "|"
      here is the function with example
      i hope that it will be useful for you
      #include <Array.au3> $s_txt = "some text1some text2|some text3|some text4|some text5|some text6" $array = splitText($s_txt, 2) _ArrayDisplay($array) Func splitText($s_text, $i_number, $s_siparator = "|") Local $a_TXT = StringSplit($s_text, $s_siparator) Local $a_Return[$a_TXT[0] + 1] If ($a_TXT[0] <= $i_number) Or ($i_number <= 0) Then ReDim $a_Return[2] $a_Return[0] = 1 $a_Return[1] = $s_text Return $a_Return EndIf Local $i_Processed = 1, $i_arrayProcessed = 1 Do For $i = $i_Processed To ($i_Processed + $i_number) - 1 If ($a_TXT[0] < $i) Then ExitLoop If Not ($a_Return[$i_arrayProcessed]) Then $a_Return[$i_arrayProcessed] = $a_TXT[$i] Else $a_Return[$i_arrayProcessed] &= $s_siparator & $a_TXT[$i] EndIf $i_Processed += 1 Next $i_arrayProcessed += 1 Until ($a_TXT[0] < $i_Processed) ReDim $a_Return[$i_arrayProcessed] $a_Return[0] = $i_arrayProcessed - 1 Return $a_Return EndFunc ;==>splitText
      accept my greetings
      thanks to
      for his notes
    • By MesterPerfect
      good morning
      this is the first post here in the autoit forums
      i hope that you can help me in my problem
      i have a JSON encoded
      it a map of my forums
      where i want to make a treeview that have the same type of map
      a system (as category)
      windows (as sub category)
      software (as an child item in the windows category)
      i don't know how to do that
      so, i know that i can do that using the json functions
      but i need your help about how we can do it as the type that i told you
      by the way i need to put the sub info for each item in an array that give me the ability to manage my items
      can post thread
      can reply
      message cound ...
      you just give me a small example and i can continue.
      am sorry if this against the rules of the forum.
      but i realy searched a lot but i couldn't
      i hope some one give me the way.
      thank you very much in advance
      here is the link of json forum
      and here is my encoded json file
      { "tree_map": { "0": [ 1, 5, 6, 7 ], "1": [ 2 ], "2": [ 4 ], "5": [ 3 ], "6": [ 8 ], "8": [ 9, 10 ] }, "nodes": [ { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 1, "node_id": 1, "node_name": null, "node_type_id": "Category", "parent_node_id": 0, "title": "Main category", "type_data": {} }, { "breadcrumbs": [ { "node_id": 1, "title": "Main category", "node_type_id": "Category" } ], "description": "", "display_in_list": true, "display_order": 1, "node_id": 2, "node_name": null, "node_type_id": "Forum", "parent_node_id": 1, "title": "Main forum", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 1, "title": "Main category", "node_type_id": "Category" }, { "node_id": 2, "title": "Main forum", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 1, "node_id": 4, "node_name": null, "node_type_id": "Forum", "parent_node_id": 2, "title": "my forums1", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 2, "node_id": 5, "node_name": null, "node_type_id": "Category", "parent_node_id": 0, "title": "Perfect", "type_data": {} }, { "breadcrumbs": [ { "node_id": 5, "title": "Perfect", "node_type_id": "Category" } ], "description": "", "display_in_list": true, "display_order": 2, "node_id": 3, "node_name": null, "node_type_id": "Forum", "parent_node_id": 5, "title": "ahmed", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 3, "node_id": 6, "node_name": null, "node_type_id": "Forum", "parent_node_id": 0, "title": "autoit", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 6, "title": "autoit", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 3, "node_id": 8, "node_name": null, "node_type_id": "Forum", "parent_node_id": 6, "title": "examples", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 6, "title": "autoit", "node_type_id": "Forum" }, { "node_id": 8, "title": "examples", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 3, "node_id": 9, "node_name": null, "node_type_id": "Forum", "parent_node_id": 8, "title": "GUI", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 6, "title": "autoit", "node_type_id": "Forum" }, { "node_id": 8, "title": "examples", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 31, "node_id": 10, "node_name": null, "node_type_id": "Forum", "parent_node_id": 8, "title": "windowEX", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 4, "node_id": 7, "node_name": null, "node_type_id": "Category", "parent_node_id": 0, "title": "vbs", "type_data": {} } ] }  
    • By cruisepandey
      I have a string like this : 
      Global $Msga = "urrent directory is /send.  (Submission of file with log number 29381077284 is confirmed)";
      I want to extract the number 29381077284  from the string. I did StringSplit to split based on "(" and then use space to reach there, But it's not a good choice. 
      Can anyone help me with regular expression to find the number from String using AutoIT. TIA
    • By DannyJ
      I have this example string:
      2020-06-08 09:23:33 : abcdefghifjklm How to trim just the right part? Or how to trim this text right to left?
      I want to trim this string right to left until ' : '.
      In ohter words: I need the 'abcdefghifklm' text.
      I have tried StringTrimRight, but that requires 
      The number of characters to trim. Unfortunately StringSplit trims the left part of the strings.
  • Create New...