Find method and Excel

[victorPh]

Hello everyone! I'm sorry if this topic is redundant, but I've already peeked through similar topics and couldn'd solve my problem. The idea here is to search an already created Excel file for a keyword and return the value located on the same row, but following column (i.e, if the searched value is "Hello", located on cell A01, then I'd like to retrieve the value located on cell B01).

The script is the following:

Opt( "MustDeclareVars", 1 )

Global Const $filePath = "..." ;varies from user to user, that's why I didn't specify it

; Validate the given file path
If Not FileExists( $filePath ) Then
  Msgbox( 0,"Excel File Test","Can't run this test, because you didn't create the Excel file " & $filePath )
  Exit
Endif

Local $simpleDatabase = ObjGet( $filePath, "Excel.Application" )

If @error Then
  Msgbox( 0,"ExcelTest","Error Getting an active Excel Object. Error code: " & Hex(@error,8 ))
  Exit
Endif

; The problem!
Local $currentValue = $simpleDatabase.ActiveSheet.Range( "A1:A230" ).Find( "keyWord" )

I've tested and no matter what keyword is given, it returns a "blank" string. The reason for this is that it might not be right to treat $currentValue as a string, but I've tried explicitly converting it and was not successful. Any help will be very much appreciated

[water]

The (an extended version of the Excel UDF included with AutoIt) has a function that I think does what you want: _ExcelFindInRange. This function "finds all instances of a string in a range and returns their addresses as a two dimensional array."

[victorPh]

@ Water

Thank you for the advice! I'll spend more time on those UDFs, they're certainly very helpful. Soon enough the script will be updated so that anyone else benefits from it too =D

[Juvigy]

Maybe try VLOOKUP internal function ?

$oExcel = ObjCreate("Excel.Application")
    $oExcel.Visible = 0
    $oExcel.WorkBooks.Open ("C:\temp.xls")
    $test = $oExcel.Application.WorksheetFunction.VLOOKUP("Hello",$oExcel.Range("A:B"),2,FALSE)
    ConsoleWrite($test&@CRLF)
    $oExcel.Quit

[Juvigy]

@edit

Forum bug , double post.

Edited January 24, 2011 by Juvigy

[JoHanatCent]

Try like this?

Global Const $filePath = @DesktopDir & "\Test.xls";varies from user to user, that's why I didn't specify it
$FromRow = 1
$ToRow = 230
$Column = 1;a
$Find = "Hallo"
If Not FileExists($filePath) Then
    MsgBox(0, "Excel File Test", "Can't run this test, because you didn't create the Excel file " & $filePath)
    Exit
EndIf
Local $simpleDatabase = ObjCreate("Excel.Application")
If @error Then
    MsgBox(0, "ExcelTest", "Error Getting an active Excel Object. Error code: " & Hex(@error, 8))
    Exit
EndIf
$simpleDatabase.Visible = 1
$simpleDatabase.WorkBooks.Open($filePath, Default, Default, Default, Default, Default)
For $i = $FromRow To $ToRow
    $currentValue = $simpleDatabase.Activesheet.Cells($i, $Column).Value
    If $currentValue = $Find Then MsgBox(0, '', $simpleDatabase.Activesheet.Cells($i, $Column + 1).Value)
Next

[victorPh]

@Juvigy

Very concise code! Didn't know about this VLOOKUP function

@ JohanatCent

I actually didn't test your script, but it seems to work!

A more general approach, according to the Excel UDF suggested by water and found here could be:

#Include <Array.au3>
#Include <Excel.au3>

Global Const $dataBaseFilePath = "..." ;create a simple database to test the example!
Global Const $dataBaseColumn = 2 ;the column of the database or excel file in which is located the desired value

Local Const $dataBaseSheet = _ExcelBookOpen( $dataBaseFilePath, 0, False )
Local Const $dataBaseArray = _ExcelReadSheetToArray( $dataBaseSheet )

_ArrayDisplay( $dataBaseArray )

Local Const $searchValueIndex = _ArraySearch( $dataBaseArray, "keyValue" )

If $searchValueIndex <> -1 Then
    Local Const $desiredValue = $dataBaseArray[ $searchValueIndex ][ $dataBaseColumn ]
    MsgBox( 0, "The value is: ", $desiredValue )
Else
    MsgBox( 0, "Error", "Invalid keyword" )
Endif

_ExcelBookClose( $dataBaseSheet, 0 )

If anyone has any suggestions, feel free to help!

Edited January 25, 2011 by victorPh

[Juvigy]

The problem with the excel.UDF code is that it is quite slower. Especially if you have a big excel file.

[victorPh]

So you would recommend the VLOOKUP function to perform operations involving a key value and other attributes if the excel file would be treated as a relational database?

[Juvigy]

Yes.You can test by trying both codes on an excel file with lets say 200 cells.

If you are using SCITE it will tell you how much time exactly the code took to complete.

Also you can use something like:

With $oExcel.ActiveSheet.Range("A1:C200")

$c = .Find ("Hello")

$c.Offset (1, 0).Select

msgbox(0,"",$c)

EndWith

[victorPh]

Thank you very much for this information! Performance is always something good to achieve, and despite the fact that it is not the major goal of this application, it is very much welcome.

I'll test the difference between these approaches and display the result here.

Well, as expected the use of the VLOOKUP internal function is a little faster (approximately 0.5 seconds), as shown below:

;1st method: Excel UDF -> Time = ~2.039
#Include <Array.au3>
#Include <Excel.au3>

Global Const $dataBaseFilePath = "..."
Global Const $attributeColumn = 2

Opt( "MustDeclareVars", 1 )

Local Const $dataBaseSheet = _ExcelBookOpen( $dataBaseFilePath, 0, False )
Local Const $dataBaseArray = _ExcelReadSheetToArray( $dataBaseSheet ) ;$dataBaseArray is a 2D array!

Local Const $searchValueIndex = _ArraySearch( $dataBaseArray, "keyValue" ) ;the desired value is located on the following position of the array

If $searchValueIndex <> -1 Then
    Local Const $desiredValue = $dataBaseArray[ $searchValueIndex ][ $attributeColumn ] ;the third column of the array contains the desired value
    ConsoleWrite( $desiredValue & @CRLF )
Else
    ConsoleWrite( "Invalid keyword" & @CRLF )
Endif

_ExcelBookClose( $dataBaseSheet, 0 )

;2nd method: Internal fucntion -> Time = ~1.539
Global Const $dataBaseFilePath = "..."

$oExcel = ObjCreate( "Excel.Application" )

With $oExcel
    .Visible = 0
    .WorkBooks.Open( $dataBaseFilePath )
EndWith

;Documentation of the VLOOKUP function: http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx?CTT=5&origin=HP005204211
;Note: the lookup value must be valid, otherwise the script will not execute
$value = $oExcel.Application.WorksheetFunction.VLOOKUP( "keyValue", $oExcel.Range("A1:B250"), 2, FALSE )

ConsoleWrite( $value & @CRLF)

$oExcel.Quit

The most important thing I've found so far is this documentation, as it contains a lot of useful (and performance-wise) information regarding MS Excel!

Edited January 26, 2011 by victorPh