victorPh Posted January 22, 2011 Share Posted January 22, 2011 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 Link to comment Share on other sites More sharing options...
water Posted January 22, 2011 Share Posted January 22, 2011 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
victorPh Posted January 22, 2011 Author Share Posted January 22, 2011 @ 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 Link to comment Share on other sites More sharing options...
Juvigy Posted January 24, 2011 Share Posted January 24, 2011 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 Link to comment Share on other sites More sharing options...
Juvigy Posted January 24, 2011 Share Posted January 24, 2011 (edited) @edit Forum bug , double post. Edited January 24, 2011 by Juvigy Link to comment Share on other sites More sharing options...
JoHanatCent Posted January 24, 2011 Share Posted January 24, 2011 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 Link to comment Share on other sites More sharing options...
victorPh Posted January 25, 2011 Author Share Posted January 25, 2011 (edited) @JuvigyVery concise code! Didn't know about this VLOOKUP function @ JohanatCentI 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 Link to comment Share on other sites More sharing options...
Juvigy Posted January 26, 2011 Share Posted January 26, 2011 The problem with the excel.UDF code is that it is quite slower. Especially if you have a big excel file. Link to comment Share on other sites More sharing options...
victorPh Posted January 26, 2011 Author Share Posted January 26, 2011 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? Link to comment Share on other sites More sharing options...
Juvigy Posted January 26, 2011 Share Posted January 26, 2011 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 Link to comment Share on other sites More sharing options...
victorPh Posted January 26, 2011 Author Share Posted January 26, 2011 (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.QuitThe 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now