Jump to content
Sign in to follow this  
SeF

Searching specified values with Excel.au3

Recommended Posts

SeF

Hi! :)

First I will show an example:

Posted Image

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 by SeF

Share this post


Link to post
Share on other sites
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! B)


Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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? B)

(Don't know much how to use COM Objects in AutoIt :) )


Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 ;)


Share this post


Link to post
Share on other sites
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 by gcue

Share this post


Link to post
Share on other sites
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??

Share this post


Link to post
Share on other sites
Juvigy

Change

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

to

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

Not tested but should work...

Share this post


Link to post
Share on other sites
AnuReddy

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

it works :mellow::(

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.