Jump to content
Sign in to follow this  
XinYoung

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!

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

 

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:

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

Share this post


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

 

 

Share this post


Link to post
Share on other sites

OMG the partial search fixed it!

image.png.dd4dec3b95c636db7a2e04b050f3e86d.png

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 roeselpi
      hello again,
      it has been a long time since i have been here and a long time since i last used autoit. ever so often when the time allows me to, then i follow up on an idea that i had a long time ago. i have done all the work on paper but now it is up to writing it in autoit and i keep stumbling over many little issues here and there. sometimes after a few days i will try again and get a step further but sometimes it just will not help no matter how long i try and think about a solution. for most of you it will be the basics but for me it is not all that easy, but at least i give it a try.
      right, down to business:
      here is my code:
      #include <MsgBoxConstants.au3> #include <StringConstants.au3> #include <Array.au3> #include <String.au3> ; ; PART 1: define replacements and check with msgbox ; Global $y, $z $y = "Yes" $z = "No" MsgBox(0,"replacements", $y & @CRLF & $z) ;the replacements in a message box ; ; PART 2: set the texts and check via console and msgbox ; Global $my1string = "abab" ;the first specified text MsgBox(0,"my1string", $my1string) ;the message box to output the first specified text Global $my2string = "icic" ;the second specified text MsgBox(0,"my2string", $my2string) ;the message box to output the second specified text ; ; PART 3: transform the strings to individual arrays ; $my1array = StringSplit($my1string, "") $my1array[0] = "" _ArrayDelete($my1array, 0) _ArrayDisplay($my1array, "my1array") ;the display of the first specified array $my2array = StringSplit($my2string, "") $my2array[0] = "" _ArrayDelete($my2array, 0) _ArrayDisplay($my2array, "my2array") ;the display of the first specified array ; ; PART 4: create an empty array for filling ; Global $OutputArray[4] $OutputArray[0] = "" _ArrayDisplay($OutputArray, "OutputArray") ;the display of the first specified array ; ; PART 5: compare & fill empty OutputArray with data after evaluation ; Global $i, $j, $k For $i = 0 to UBound($my1array) -1 For $j = 0 to UBound($my2array) -1 For $k = 0 to UBound($OutputArray) -1 If $my1array[$i] = "a" And $my2array[$j] = "i" Then $OutputArray[$k] = $y Else $OutputArray[$k] = $z EndIf Next Next Next _ArrayDisplay($OutputArray, "OutputArray") ;the display of the Newly filled Array In "Part 2" i make a string that is converted to an array in "Part 3" ... Now, I know that "a" and "i" are always in the exact same spot in both arrays and so i wanted to compare this and make a further array to document my findings by saying "yes" or "no" ... however my new array keeps saying just "no" allthough i can clearly see and know that it should say:
      yes no yes no my guess is that there is something wrong within my for-loops and that the counting is somehow "off" i guess that when the first for-loop is finished it reaches the second whilst the second for-loop is checking the first which would explain why it always says "no" instead of seeing the obvious.
      so my question would be: what is wrong with my for-loop? or where am i making an error that ultimately gives me the wrong results?
      help is much appreciated.
      kind regards
      roeselpi
       
       
      PS: sorry for my not so great english spelling ... stupid german sitting here trying out intermediate english skills.
    • By Zobu
      Hey Guys,
      I want to add a new checkbox with its own variable every time the add button is clicked.
      The added checkboxes should remain when I close the window or exit the script and when I reopen I should be able to add new checkboxes aswell.
      here is what I have so far..
      #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <StaticConstants.au3> #include <EditConstants.au3> #include <MsgBoxConstants.au3> #include <GuiButton.au3> $test = GUICreate("adding test", 475, 345, 500, 175) $Check1 = GUICtrlCreateCheckbox("Checkbox 1", 15, 25, 300, 25) $Button = GUICtrlCreateButton("Add", 365, 25, 90, 20) $Check2 = GUICtrlCreateCheckbox("Checkbox 2", 15, 50, 300, 25) $Check3 = GUICtrlCreateCheckbox("Checkbox 3", 15, 75, 300, 25) GUICtrlSetState($Check2, $GUI_HIDE) GUICtrlSetState($Check3, $GUI_HIDE) GUISetState(@SW_SHOW) While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE ; Exit Case $Button GUICtrlSetPos($Check2, 15, 50, 300, 25) GUICtrlSetState($Check2, $GUI_SHOW) Case $Button GUICtrlSetPos($Check3, 15, 75, 300, 25) GUICtrlSetState($Check3, $GUI_SHOW) EndSwitch WEnd  
    • By Jangal
      Hello friends
      This app is slow
      How to increase its speed?
       
      #include <Array.au3> #include <StringConstants.au3> #include <File.au3> #include <String.au3> Global $aWord[][2]  = [[1, "google"],[2,"hello"]]


        Global $sFileName = @ScriptDir & "\1.txt" ; 2MB Text File Local $sFileRead = FileRead($sFileName) Local $res = StringRegExp($sFileRead, "(*UCP)\b[\pL\d]{2,}", 3) _ArrayDisplay($res)   for $sWord in $res     $iIndex = _ArraySearch($aWord, $sWord, 0, 0, 0, 0, 1, 2)     ;MsgBox(0,0,$iIndex)     if $iIndex == -1 Then         Local $aFill = [[0,$sWord]]         _ArrayAdd($aWord,$aFill)        ;      Else         $aWord[$iIndex][0] +=1     EndIf   Next _ArrayDisplay($aWord)

      1.txt
×
×
  • Create New...