Sign in to follow this  
Followers 0
hirak

Excel search

15 posts in this topic

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.

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

#3 ·  Posted (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.

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (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 by JoHanatCent

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

thank you very much

You are WelCome.

Share this post


Link to post
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

:-(

Share this post


Link to post
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.


Share this post


Link to post
Share on other sites

#9 ·  Posted (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 by hirak

Share this post


Link to post
Share on other sites

#10 ·  Posted (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 by JoHanatCent

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

console does not show up about any errors n thank u for your code

but in this code also @extended=0 is setting up

i've no problem with @extended because we r gettin what we want. Posted Image

Edited by hirak

Share this post


Link to post
Share on other sites

but in this code also @extended=0 is setting up

i've no problem with @extended because we r gettin what we want. Posted Image

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.


Share this post


Link to post
Share on other sites

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 now

thank u very much for your help

Share this post


Link to post
Share on other sites

#14 ·  Posted (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 by Annonyreeder

Share this post


Link to post
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 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

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