Searching specified values with Excel.au3

[SeF]

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

[SeF]

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!

[Juvigy]

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.

[SeF]

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 )

[Juvigy]

$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

[SeF]

$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

[gcue]

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

[AnuReddy]

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??

[Juvigy]

Change

$oExcel.Application.ActiveSheet.Range ("A1:D5").Find ("7890").Select

to

$oExcel.Application.ActiveSheet.UsedRange.Find ("7890").Select

Not tested but should work...

[AnuReddy]

guys wat if we dont know in what range of the excel sheet the value is present??

it works