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):
;~ 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!
By XinYoung
By ShakeelAhmad
By WoodGrain
By AndreasNWWWWW
By c1one