Conditional select from _arraydisplay (Excel)

[DStraathof]

Hi all,

For a new test solution I'm using an Excel file with user credentials (password protected on disk) to be able to test a webportal using one of these users. Now I need to set the record I'd like to use each time I run my code.

Does anyone know how to select one of the row results from the read Excel sheet, using _arraydisplay, and pass these data back to the script to continue the rest of my code using the selected user?

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

Local $oE = _Excel_Open()

Local $sWorkbook = @ScriptDir & "\Credentials.xls"
Local $oWB = _Excel_BookOpen($oE, $sWorkbook, Default, Default, "Very-Secret_PW!")

Local $aResult = _Excel_RangeRead($oWB, Default, $oWB.ActiveSheet.Usedrange.Columns("A:B"))

local $aFinal[UBound($aResult)][2]

for $1 = 0 to UBound($aResult) - 1
    $aFinal[$1][0] = $aResult[$1][0] ; Username
    $aFinal[$1][1] = $aResult[$1][1] ; Password
next


_arraydisplay($aFinal,'Final',default,default,default,'A|B')

_Excel_Close($oE)

The Excel file contains 2 columns: A=Username; B=Password.

Thanks for your help!

[Jos]

Why not create a simple GUI with a GUICtrlCreateCombo() containing the options so you can select the appropriate one?

Jos

[DStraathof]

I'll try to make this work with Excel then. Thanks for the tip.

[Jos]

Not sure what you meant by that statement, but is is pretty easy to add to the posted code.  
Shout when you have questions.

Jos

Edited August 8, 2019 by Jos

[jchd]

Just use the Excel MATCH function.

[jchd]

Also in AutoIt you don't need a loop to duplicate an array: in your code you could just have used Local $aFinal = $aResult (provided you really need $aFinal at all).

[Nine]

May be you want something like that (untested) :

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

Local $UserName = "UsernameIwant"

MsgBox (0,"",_SearchUser ($UserName))

Func _SearchUser ($UserName)

  Local $oE = _Excel_Open()
  Local $sWorkbook = @ScriptDir & "\Credentials.xls"
  Local $oWB = _Excel_BookOpen($oE, $sWorkbook, Default, Default, "Very-Secret_PW!")

  Local $aResult = _Excel_RangeRead($oWB, Default, $oWB.ActiveSheet.Usedrange.Columns("A:B"))
  _Excel_Close($oE)
  
  _arraydisplay($aResult,'Final',default,default,default,'A|B')
  Local $iFound = _arraySearch ($aResult, $UserName, 0, 0, 1, 2, 0, 0)
  If @error then Return SetError (1,0,-1)
  Return $aResult[$iFound][0] & "|" & $aResult[$iFound][1]
  
EndFunc

 

Edited August 8, 2019 by Nine

[DStraathof]

Thanks all for the tips 'n' tricks. 🙂 It works!!

This is what I ended up with:

expandcollapse popup

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

; Create Excel object
Local $oE = _Excel_Open()

; Open Workbook (password-protected)
Local $sWorkbook = @ScriptDir & "\Credentials.xls"
Local $oWB = _Excel_BookOpen($oE, $sWorkbook, Default, Default, "Very-Secret_PW!")

; Select range A:B for 'Username:Password'
Local $aResult = _Excel_RangeRead($oWB, Default, $oWB.ActiveSheet.Usedrange.Columns("A:B"))

; Create a GUI with various controls.
Local $hGUI = GUICreate("Gebruikers", 300, 200)

; Create a combobox control.
Local $idComboBox = GUICtrlCreateCombo("Select user", 10, 10, 185, 20)
Local $idButton_Close = GUICtrlCreateButton("Close", 210, 170, 85, 25)

for $1 = 0 to UBound($aResult) - 1
    ; Add usernames to the combobox.
    GUICtrlSetData($idComboBox, $aResult[$1][0]&"|", "Select user")
next

; Display the GUI.
GUISetState(@SW_SHOW, $hGUI)

; Loop until the user exits.
While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE, $idButton_Close
            ExitLoop
        
        ; Read the selected value
        Case $idComboBox
            $sComboRead = GUICtrlRead($idComboBox)

    EndSwitch
WEnd

; Get the index from the Excel array to select the row for the selected user
Local $iFound = _arraySearch ($aResult, $sComboRead, 0, 0, 1, 2, 0, 0)

; Fill local variables with the correct set.
Local $sUsername = $aResult[$iFound][0]
Local $sPassword = $aResult[$iFound][1]

; Delete the previous GUI and all controls.
GUIDelete($hGUI)

; Close Excel
_Excel_Close($oE)

 

Edited August 9, 2019 by DStraathof