Skip to main content

Find-Data

Command Description

Search for data in the specified area of the worksheet, and return the cell that hits the search

Command Prototype

objRet = Excel.Find(objExcelWorkBook,sheet,strRange,findValue,retIndex,retAll)

Parameter Description

ParameterRequiredTypeDefaultDescription
objExcelWorkBookTrueexpressionobjExcelWorkBookExcel workbook object (workbook opened with "Open Excel" command (Excel.OpenExcel) or the workbook object returned by the command "Bind Excel" (Excel.BindBook))
sheetTruestring"Sheet1"The string means sheet name. The number means sheet order(starting from 0)
strRangeTruestring"A1:B2"[[cell 1 row number, cell 1 column number],[cell 2 row number, cell 2 column number]] and "A1:B2" are supported to specify a certain area, and are case insensitive. When only one cell is written, data is read from the beginning of the cell to the end of the table
findValueTruestring""Need to find the data content
retIndexTruebooleanNoneWhether to return the cell index; if it is true, the cell index in the form of [row number, column number] will be returned. If it is false, the cell name with letters and numbers will be returned
retAllTruebooleanNoneWhether to return all cells, true returns the array containing all cells with data found, false returns the first cell with data found in the range

return

objRet,The variable used to save the output of the command.

Demo

/************************Execute macro************************** ******** 
**Input 1:
** objExcelWorkBook--Excel workbook object (a workbook opened using the "Open Excel" command (Excel.OpenExcel) or a job bound using the "Bind Excel" command (Excel.BindBook) book object).
**Entry 2:
** sheet--If a string is used, it means the name of the specified worksheet; if a number is used, it means the order of the specified worksheet (starting from 0)
**Entry 3:
** strRange--Specified range, supports cell names such as "A1:B2" and two-dimensional row and column arrays such as [[cell 1 row number, cell 1 column number], [cell 2 Row number, cell 2 column number]] two forms, case-insensitive when using the cell name. When only one cell is written, read from the beginning of the cell to the place where there is data at the end of this table
**Entry 4:
** findValue--the data content to be found
**Entry 5:
** retIndex--whether to return the cell index, true returns the cell index in the form of [row number, column number], false returns the cell name with letters and numbers
**Entry 6:
** retAll--whether to return all cells, true returns the array containing all cells with data found, false returns the first cell in the range with data found
**Exports:
** objRet--The variable to which the output of the function call is saved.
**
*********************************************************************/

objExcelWorkBook = Excel.OpenExcel(@res'''Test.xls''',true,"WPS","","")
objRet = Excel.Find(objExcelWorkBook,"Sheet1","A1:B2","UiBot",false,false)
TracePrint "Find data: the location of the cell where 'UiBot' is located in the area where the Excel object 'Sheet1' worksheet A1:B2 is located"
Excel.CloseExcel(objExcelWorkBook,True)