Sign in to follow this  
Followers 0
victorPh

Find method and Excel

11 posts in this topic

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

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

@ 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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

@edit

Forum bug , double post.

Edited by Juvigy

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

@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 by victorPh

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 by victorPh

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  
Followers 0