SeF Posted November 29, 2009 Share Posted November 29, 2009 (edited) Hi! First I will show an example:I want to search the number "7890" and then read and put into variables the values from the B, C and D columns.I started my script like this, just for testing out the functions:#include <Excel.au3> #include <Array.au3> $sFilePath = @ScriptDir & "\Test.xls" _ExcelBookOpen($sFilePath, 0) $oExcel = _ExcelBookAttach("Test.xls", "FileName") $aArray = _ExcelReadSheetToArray($oExcel, 1, 1) _ArrayDisplay($aArray, "Array List") _ExcelBookClose($oExcel, 0)But, nothing happens. Also, the _ExcelReadSheetToArray example from the Help File don't work too. Edited November 29, 2009 by SeF Link to comment Share on other sites More sharing options...
SeF Posted November 29, 2009 Author Share Posted November 29, 2009 Problem solved! In two different ways: (Just don't know which one is faster. Still testing it) ArraySearch's way: #include <Excel.au3> #include <Array.au3> $sFilePath = @ScriptDir & "\Test.xls" _ExcelBookOpen($sFilePath, 0) $oExcel = _ExcelBookAttach("Test.xls", "FileName") $aArray = _ExcelReadArray($oExcel, 1, 1, 5, 1) $iIndex = _ArraySearch($aArray, "7890") $Value1 = _ExcelReadCell($oExcel, $iIndex + 1, 2) $Value2 = _ExcelReadCell($oExcel, $iIndex + 1, 3) $Value3 = _ExcelReadCell($oExcel, $iIndex + 1, 4) Msgbox (0, "Value1", $Value1) Msgbox (0, "Value2", $Value2) Msgbox (0, "Value3", $Value3) _ExcelBookClose($oExcel, 0) ----- ExcelReadCell + Loop way: #include <Excel.au3> $sFilePath = @ScriptDir & "\Test.xls" _ExcelBookOpen($sFilePath, 0) $oExcel = _ExcelBookAttach("Test.xls", "FileName") $i = 1 Do $sCellValue = _ExcelReadCell($oExcel, $i, 1) $i = $i + 1 Until $sCellValue = "7890" $Value1 = _ExcelReadCell($oExcel, $i - 1, 2) $Value2 = _ExcelReadCell($oExcel, $i - 1, 3) $Value3 = _ExcelReadCell($oExcel, $i - 1, 4) Msgbox (0, "Value1", $Value1) Msgbox (0, "Value2", $Value2) Msgbox (0, "Value3", $Value3) _ExcelBookClose($oExcel, 0) Suggestions? Complaints? Doubts? Comments? Feel free! Link to comment Share on other sites More sharing options...
Juvigy Posted November 30, 2009 Share Posted November 30, 2009 You can also test the : $oExcel.ActiveSheet.Range ("A1:D20").Find ("what you search for") It looks it is going to be faster then looping true the array. Link to comment Share on other sites More sharing options...
SeF Posted December 1, 2009 Author Share Posted December 1, 2009 You can also test the :$oExcel.ActiveSheet.Range ("A1:D20").Find ("what you search for")It looks it is going to be faster then looping true the array.Thanks for the tip! But, how can I use it on my Script for example? (Don't know much how to use COM Objects in AutoIt ) Link to comment Share on other sites More sharing options...
Juvigy Posted December 1, 2009 Share Posted December 1, 2009 $oExcel=_ExcelBookAttach("c:\Book1.xlsx") $oExcel.Application.ActiveSheet.Range ("A1:D5").Find ("7890").Select $oExcel.Application.ActiveCell.Offset (0, 1).Select $b=$oExcel.Application.ActiveCell.value $oExcel.Application.ActiveCell.Offset (0, 1).Select $c=$oExcel.Application.ActiveCell.value $oExcel.Application.ActiveCell.Offset (0, 1).Select $d=$oExcel.Application.ActiveCell.value Msgbox (0, "Value1", $b) Msgbox (0, "Value2", $c) Msgbox (0, "Value3", $d) exit Link to comment Share on other sites More sharing options...
SeF Posted December 2, 2009 Author Share Posted December 2, 2009 $oExcel=_ExcelBookAttach("c:\Book1.xlsx") $oExcel.Application.ActiveSheet.Range ("A1:D5").Find ("7890").Select $oExcel.Application.ActiveCell.Offset (0, 1).Select $b=$oExcel.Application.ActiveCell.value $oExcel.Application.ActiveCell.Offset (0, 1).Select $c=$oExcel.Application.ActiveCell.value $oExcel.Application.ActiveCell.Offset (0, 1).Select $d=$oExcel.Application.ActiveCell.value Msgbox (0, "Value1", $b) Msgbox (0, "Value2", $c) Msgbox (0, "Value3", $d) exit Thanks again! It appears to be faster Link to comment Share on other sites More sharing options...
gcue Posted December 7, 2009 Share Posted December 7, 2009 (edited) if the value is always going to be in column A then you can just look at each item in that column with a For loop (since you already have an array built) and if it matches with what ur looking for then report column B C and D for that row. Edited December 7, 2009 by gcue Link to comment Share on other sites More sharing options...
AnuReddy Posted February 23, 2010 Share Posted February 23, 2010 if the value is always going to be in column A then you can just look at each item in that column with a For loop (since you already have an array built) and if it matches with what ur looking for then report column B C and D for that row.guys wat if we dont know in what range of the excel sheet the value is present?? Link to comment Share on other sites More sharing options...
Juvigy Posted February 23, 2010 Share Posted February 23, 2010 Change $oExcel.Application.ActiveSheet.Range ("A1:D5").Find ("7890").Select to $oExcel.Application.ActiveSheet.UsedRange.Find ("7890").Select Not tested but should work... Link to comment Share on other sites More sharing options...
AnuReddy Posted February 23, 2010 Share Posted February 23, 2010 guys wat if we dont know in what range of the excel sheet the value is present??it works 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