Jump to content

Finding values on excel


Recommended Posts

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
Link to comment
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

 

Link to comment
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.

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...