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



#2 ·  Posted

Please show your code

Share this post


Link to post
Share on other sites

#3 ·  Posted

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

#4 ·  Posted

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

#5 ·  Posted

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