Jump to content
Sign in to follow this  
Stimorol

Excel lookup

Recommended Posts

Stimorol

Hi AutoIT. 

I'm looking for a way to lookup text with AutoIT

  1. Copy and save string of text (lets call it 1234/12) from a web page that AutoIT Window Info can't see but is selectable and can be copied.
  2. Take the string and remove the and anything after the slash. 
  3. Look up the string in a excel document and if it finds it in column Account then take the string in the column right next to it in column User and save it to variable $userstring
  4. If it does not find the 1234 string in column Account then I need it to ask me for input and then save the manual string into $userstring

Is there anyway to do this with AutoIT?

Share this post


Link to post
Share on other sites
kylomas

Stimorol,

This may get you started...

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $sString = '1238/12' ;    string from Web page
$sString = StringRegExpReplace($sString, '(\d+)/.*$', '$1') ;   grab numbers to first '/'

; validate string
If @error Or @extended = 0 Then Exit MsgBox($MB_ICONERROR, 'ERROR', 'Invalid input' & @CRLF & 'Input = ' & $sString)

; start an instance of excel
Local $oExcel = _Excel_Open(False)
If @error Then Exit MsgBox($MB_ICONERROR, 'ERROR', 'Excel failed to initialize')

;open a test excel workbook
Local $sExcelFile = @ScriptDir & '\test010.xls'
Local $oBOOK = _Excel_BookOpen($oExcel, $sExcelFile)
If @error Then
    MsgBox($MB_ICONERROR, 'ERROR', 'Excel failed to open' & @CRLF & 'File Name = ' & $sExcelFile)
    _Excel_Close($oExcel)
    Exit
endif

; search for our account number
Local $aRSLT = _Excel_RangeFind($oBOOK, $sString, "A1:A99")
If $aRSLT[0][2] = '' Then
    MsgBox($MB_ICONERROR, 'ERROR', 'Account not found' & @CRLF & 'Account = ' & $sString)
    _Excel_Close($oExcel)
    Exit
EndIf

; set the adjacent column
Local $sAdjacentColumn = Chr(Asc(StringRegExp($aRSLT[0][2], '[^\$]', 3)[0]) + 1)

; set the row
Local $sRow = StringRegExp($aRSLT[0][2], '[^\$]', 3)[1]

; get the User Name
Local $sUser = _Excel_RangeRead($oBOOK, Default, $sAdjacentColumn & $sRow)
If $sUser = '' Then $sUser = InputBox('Account = ' & $sString, 'Enter User Name')

MsgBox($mb_OK, 'User Lookup', 'Account = ' & $sString & @CRLF & 'User = ' & $sUser)

_excel_close($oExcel)

The test spreadsheet that I used looks like this test010.xls.

Point #4 did not make sense to me because if you do not have an account # then you are not likely to have a user.  I set the code up so that if you have an account # with a blank user then you are prompted for the user.  If this is not your intent it is easily modified. 

Good Luck,

kylomas

edit:

Assumptions

data range is A1:Z99

AutoIT 3.3.12+

edit2:

There is probably a way within excel to get to the adjacent column but I don't know what it is so I just added +1 to the found column to move to the next column to the right.

Edited by kylomas
  • Like 1

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

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  

×