hirak Posted February 8, 2011 Share Posted February 8, 2011 I want to search through excel sheet by one keyword and after finding that i want to fetch the content of whole row in an array. how can i do it? help!!!!!!!!!! and i want to know the total no of rows and columns. Link to comment Share on other sites More sharing options...
RagsRevenge Posted February 8, 2011 Share Posted February 8, 2011 I want to search through excel sheet by one keyword and after finding that i want to fetch the content of whole row in an array.how can i do it?help!!!!!!!!!!and i want to know the total no of rows and columns.Hi Hirak,show us the code you have at the moment to achieve this.If none, then I'd advise doing some reading on _ExcelReadSheetToArray.D Link to comment Share on other sites More sharing options...
hirak Posted February 10, 2011 Author Share Posted February 10, 2011 (edited) Hi Hirak,show us the code you have at the moment to achieve this.If none, then I'd advise doing some reading on _ExcelReadSheetToArray.Dhere is my code to search the string and return the index#include <Excel.au3>#include<Array.au3>$sFilePath1 = "C:\Documents and Settings\trainee\Desktop\ex.xls" ;This file should already exist$oExcel = _ExcelBookOpen($sFilePath1)$aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters_ArrayDisplay($aArray,"")$sSearch = InputBox("", "String to find?")If @error Then Exit$sColumn = InputBox("", "Column to search?")If @error Then Exit$sColumn = Int($sColumn)$row=UBound($aArray)$column=UBound($aArray,2)$iIndex = _ArraySearch($aArray,$sSearch,"","","","","",$sColumn)If @error Then MsgBox(0, "Not Found",'"' & $sSearch & '" was not found on column ' & $sColumn & '.')Else MsgBox(0, "Found",'"' & $sSearch & '" was found in the array at position ' & $iIndex & ' on column ' & $sColumn & '.') EndIfIf i want to search a string say "tested" and if it is found on S2 (2'nd row and column s) then index that is $iIndex having value of 2so if i want to add a value in to the excel sheet on the right cell of s2 means t2 then how can i do that? Edited February 10, 2011 by hirak Link to comment Share on other sites More sharing options...
JoHanatCent Posted February 10, 2011 Share Posted February 10, 2011 (edited) so if i want to add a value in to the excel sheet on the right cell of s2 means t2 then how can i do that? See last line: Also try to put your posted script between these [ autoit] your script [ /autoit] no spaces in the square brackets. #include <Excel.au3> #include<Array.au3> $sFilePath1 = "C:\Documents and Settings\trainee\Desktop\ex.xls" ;This file should already exist $oExcel = _ExcelBookOpen($sFilePath1) $aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters _ArrayDisplay($aArray,"") $sSearch = InputBox("", "String to find?") If @error Then Exit $sColumn = InputBox("", "Column to search?") If @error Then Exit $sColumn = Int($sColumn) $row=UBound($aArray) $column=UBound($aArray,2) $iIndex = _ArraySearch($aArray,$sSearch,"","","","","",$sColumn) If @error Then MsgBox(0, "Not Found",'"' & $sSearch & '" was not found on column ' & $sColumn & '.') Else MsgBox(0, "Found",'"' & $sSearch & '" was found in the array at position ' & $iIndex & ' on column ' & $sColumn & '.') EndIf _ExcelWriteCell($oExcel,$sSearch,$iIndex,$sColumn + 1);<< ======= This line Edited February 10, 2011 by JoHanatCent Link to comment Share on other sites More sharing options...
hirak Posted February 11, 2011 Author Share Posted February 11, 2011 See last line: Also try to put your posted script between these [ autoit] your script [ /autoit] no spaces in the square brackets. #include <Excel.au3> #include<Array.au3> $sFilePath1 = "C:\Documents and Settings\trainee\Desktop\ex.xls" ;This file should already exist $oExcel = _ExcelBookOpen($sFilePath1) $aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters _ArrayDisplay($aArray,"") $sSearch = InputBox("", "String to find?") If @error Then Exit $sColumn = InputBox("", "Column to search?") If @error Then Exit $sColumn = Int($sColumn) $row=UBound($aArray) $column=UBound($aArray,2) $iIndex = _ArraySearch($aArray,$sSearch,"","","","","",$sColumn) If @error Then MsgBox(0, "Not Found",'"' & $sSearch & '" was not found on column ' & $sColumn & '.') Else MsgBox(0, "Found",'"' & $sSearch & '" was found in the array at position ' & $iIndex & ' on column ' & $sColumn & '.') EndIf _ExcelWriteCell($oExcel,$sSearch,$iIndex,$sColumn + 1);<< ======= This line thank you very much Link to comment Share on other sites More sharing options...
JoHanatCent Posted February 11, 2011 Share Posted February 11, 2011 thank you very muchYou are WelCome. Link to comment Share on other sites More sharing options...
hirak Posted February 14, 2011 Author Share Posted February 14, 2011 You are WelCome.hello JoHanatCent thank you for your helpbut i've another questionwhen i've to work with big excel sheets with more than 150 rows then _ExcelReadSheetToArray($oExcel) is giving error and when i checked then it will set @extended=0 means rows are out of range.plzz help me with these:-( Link to comment Share on other sites More sharing options...
JoHanatCent Posted February 14, 2011 Share Posted February 14, 2011 when i've to work with big excel sheets with more than 150 rows then _ExcelReadSheetToArray($oExcel) is giving error and when i checked then it will set @extended=0 means rows are out of range.:-(Just tested with over a 1000 lines and 8 columns - no problem.Just to make sure that the @extended message are not because of something else check the console for errors and post it here. If not I suggest post your sheet and script here and we'll try to help. There are many ways to get this done. Link to comment Share on other sites More sharing options...
hirak Posted February 15, 2011 Author Share Posted February 15, 2011 (edited) <autoit> #include <Excel.au3> #include<Array.au3> $sFilePath1 = "C:\Documents and Settings\trainee\Desktop\sample.xls" ;This file should already exist $oExcel = _ExcelBookOpen($sFilePath1) $aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters If @extended=0 Then MsgBox(0,"","row out of range") EndIf If @extended=1 Then MsgBox(0,"","column out of range") EndIf _ArrayDisplay($aArray,"") $sSearch = InputBox("", "String to find?") If @error Then Exit $sColumn = InputBox("", "Column to search?") If @error Then Exit $sColumn = Int($sColumn) $row=UBound($aArray) $column=UBound($aArray,2) $iIndex = _ArraySearch($aArray,$sSearch,"","","","",0,$sColumn) If @error Then MsgBox(0, "Not Found",'"' & $sSearch & '" was not found on column ' & $sColumn & '.') Else MsgBox(0, "Found",'"' & $sSearch & '" was found in the array at position ' & $iIndex & ' on column ' & $sColumn & '.') EndIf </autoit> hey .. this is the code and i am not able to attach the excel file here this code worked fine today but @extended=0 is set up any way n giving row out of bound Edited February 15, 2011 by hirak Link to comment Share on other sites More sharing options...
JoHanatCent Posted February 15, 2011 Share Posted February 15, 2011 (edited) this code worked fine today but @extended=0 is set up any way n giving row out of bound Did you get to checking the console for any other errors? I ran this code on 3000 lines by 8 columns without a problem. If it is only about the @extended error that you are concerned about run this: #include <Excel.au3> #include<Array.au3> Dim $tes1, $tes2, $tes3, $tes4 $sFilePath1 = "C:\Documents and Settings\trainee\Desktop\sample.xls" $oExcel = _ExcelBookOpen($sFilePath1) $tes1 = @error If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist!") EndIf $aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters $tes3 = @error $tes4 = @extended If @error = 0 Then MsgBox(0, '', 'No errors opening the sheet!', 4) If @error > 0 Then MsgBox(0, 'Not Opening the sheet', 'The Extended error for opening the sheet is = ' & @extended, 4) MsgBox(0, 'Error = ' & @error, 'Book Open error = ' & $tes1 & @CRLF & 'Sheet Open error = ' & $tes3 & @CRLF & 'Sheet Open Extended = ' & $tes4) _ArrayDisplay($aArray, "") $sSearch = InputBox("", "String to find?") If @error Then Exit $sColumn = InputBox("", "Column to search?") If @error Then Exit $sColumn = Int($sColumn) $row = UBound($aArray) $column = UBound($aArray, 2) $iIndex = _ArraySearch($aArray, $sSearch, "", "", "", "", 0, $sColumn) If @error Then MsgBox(0, "Not Found", '"' & $sSearch & '" was not found on column ' & $sColumn & '.') Else MsgBox(0, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ' on column ' & $sColumn & '.') EndIf _ExcelWriteCell($oExcel, $sSearch, $iIndex, $sColumn + 1) Edited February 15, 2011 by JoHanatCent Link to comment Share on other sites More sharing options...
hirak Posted February 15, 2011 Author Share Posted February 15, 2011 (edited) console does not show up about any errors n thank u for your codebut in this code also @extended=0 is setting upi've no problem with @extended because we r gettin what we want. Edited February 15, 2011 by hirak Link to comment Share on other sites More sharing options...
JoHanatCent Posted February 15, 2011 Share Posted February 15, 2011 but in this code also @extended=0 is setting upi've no problem with @extended because we r gettin what we want. I C what you say.BUT if @error = 0 (meaning there is no error) then you don't use @extended because nothing was posted to it. Link to comment Share on other sites More sharing options...
hirak Posted February 16, 2011 Author Share Posted February 16, 2011 I C what you say.BUT if @error = 0 (meaning there is no error) then you don't use @extended because nothing was posted to it.ok i understand nowthank u very much for your help Link to comment Share on other sites More sharing options...
Annonyreeder Posted November 21, 2013 Share Posted November 21, 2013 (edited) Hello, I have used the codes provided in this thread and they do exactly as i want, My only issue is that the excel document is very large uses 1431 rows about A-P collums, my issue is that it takes far to long to read for this to be practicle for what i want, is there a way to save the results or the array in a .InI or somthing after it has performed "_ExcelReadSheetToArray($oExcel)" so that future searches are quicker. Edited November 21, 2013 by Annonyreeder Link to comment Share on other sites More sharing options...
water Posted November 21, 2013 Share Posted November 21, 2013 Please have a look at my rewrite of the Excel UDF. The functions there are much faster. There is no need to read the whole sheet to an array and then search through the array. Use function _Excel_RangeFind. BTW: You need the latest Beta version of AutoIt to use my UDF. 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...
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