karudish

Finding values on excel

5 posts in this topic

#1 ·  Posted (edited)

Hi, i'm a user of AutoIT by a long a time (i've learned all of it online) and i've never used Excel interactions before. I started today looking at Excel.au3 but i think i really need a bit of help on this cause i just don't know where to begin..

I would appreciate very much if someone could give me some ideas on this.

MY EXCEL: (example)

http://imgur.com/6zbRW5f

So, i've already have a code, that gives the values of 3rd and 4th column of that excel.

I need to get the value of the 1st column that matches.

Example: (Using the values from the 2nd Row on the image)

My script already made $ctype = "COn" and $ps = "Brianna".. How can i make AutoIT to search the excel and make $id = 1667530?

 

Thanks for reading,

PS- I will remove the duplicates on the excel. Those are not a problem

EDIT. I cant attach the image to the topic so i will leave the imgur link there..

Edited by karudish

Share this post


Link to post
Share on other sites

Please show your code

Share this post


Link to post
Share on other sites

Simple Example:

#include <Array.au3>
#include <Excel.au3>

Local $sWorkbook = @ScriptDir & "\Excel.xlsx"
Local $oExcel =_Excel_Open(False)
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, True)
Local $aWorkbook = _Excel_RangeRead($oWorkbook, Default)

_ArrayDisplay($aWorkbook)

MsgBox(0, "ID", "ID#: " & _SearchExcel("COn", "Brianna"))

Func _SearchExcel($sCType, $sPS)
    For $i = 1 To UBound($aWorkbook) - 1
        If $aWorkbook[$i][2] = $sCType And $aWorkbook[$i][3] = $sPS Then Return $aWorkbook[$i][0]
    Next
    Return 0
EndFunc

 

1 person likes this

Share this post


Link to post
Share on other sites

Holy f*ck!!

@Subz That was awesome! It fits perfectly on what i need. Thanks buddy :)

I don't know any programming language, and this seems so simple after i see it for the first time.. Really good work bro ;)

Share this post


Link to post
Share on other sites

Not to discount Subz's solution, but you may also want to check out the _Excel_RangeFind method in the Excel UDF.  It uses the Excel COM find method, which in theory should be more efficient.  There are a few examples using it in the help file.  Might not make much of a difference depending on your use case(s), but if you are dealing with a very large spreadsheet I would expect it to have better performance.

Still...props to @Subz for providing a concise solution.

1 person likes this

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