Excel lookup

[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?

[kylomas]

Stimorol,

This may get you started...

expandcollapse popup

#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 August 7, 2014 by kylomas