Sign in to follow this  
Followers 0
SeF

Searching specified values with Excel.au3

10 posts in this topic

#1 ·  Posted (edited)

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



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

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

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

$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

$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

#7 ·  Posted (edited)

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

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

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

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  
Followers 0