Jump to content

Excel search


hirak
 Share

Recommended Posts

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

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

here 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 & '.')

EndIf

If 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 2

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?

Edited by hirak
Link to comment
Share on other sites

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 by JoHanatCent
Link to comment
Share on other sites

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

You are WelCome.

hello JoHanatCent

thank you for your help

but i've another question

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.

plzz help me with these

:-(

Link to comment
Share on other sites

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

<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 by hirak
Link to comment
Share on other sites

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 by JoHanatCent
Link to comment
Share on other sites

  • 2 years later...

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 by Annonyreeder
Link to comment
Share on other sites

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...