Populating Combo Box from the excel sheet

[Jess]

Hi

I am trying on a script where in I have GUI with 3-4 combos and If i select a value from one combo, the other combo boxes should be filled according to the value selected in combo box1 referring to the excel sheet. In the excel sheet the column 1 is what the combo 1 is populated with. Here is a structure of my excel sheet. 

If anyone can help me in this.

App       En        S           c
App1    en1        s1        c1
App1    en2        s1        c2
App2    en3        s2        c4    
App2    en3        s1        c3
App3    en1        s1        c7

 

Thank You

[kylomas]

Jess,

Is this what you are asking...?

expandcollapse popup

; *** Start added by AutoIt3Wrapper ***
#include <ComboConstants.au3>
; *** End added by AutoIt3Wrapper ***
; *** Start added by AutoIt3Wrapper ***
#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
; *** End added by AutoIt3Wrapper ***

#include <Excel.au3>
#include <array.au3>
#include <GuiComboBox.au3>

#AutoIt3Wrapper_Add_Constants=n

; 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 & '\test.xls'
Local $oBOOK = _Excel_BookOpen($oExcel, $sExcelFile)
If @error Then Exit MsgBox($MB_ICONERROR, 'ERROR', 'Excel failed to open' & @CRLF & 'File Name = ' & $sExcelFile)

;get all used cells
Local $aResult = _Excel_RangeRead($oBOOK)

;close the instance of excel
If _Excel_Close($oExcel) <> 1 Then Exit MsgBox(17, 'ERROR', 'Error closing excel = ' & @error)

Local $gui010 = GUICreate('Example of populating combo boxes')
Local $cmb01 = GUICtrlCreateCombo('', 10, 10, 50, 20)
Local $cmb02 = GUICtrlCreateCombo('', 70, 10, 50, 20)
Local $cmb03 = GUICtrlCreateCombo('', 130, 10, 50, 20)
Local $cmb04 = GUICtrlCreateCombo('', 190, 10, 50, 20)

;populate combo 1
For $1 = 0 To UBound($aResult) - 1
    GUICtrlSetData($cmb01, $aResult[$1][0])
Next
GUICtrlSetData($cmb01, $aResult[0][0]) ;    sety first entry as default

GUISetState()

While 1

    $msg = GUIGetMsg()

    Switch $msg
        Case $gui_event_close
            Exit
        Case $cmb01
            _pop_other_combos(GUICtrlRead($cmb01))
    EndSwitch

WEnd

Func _pop_other_combos($str)

    ; clear combos

    GUICtrlSetData($cmb02, '')
    GUICtrlSetData($cmb03, '')
    GUICtrlSetData($cmb04, '')

    ; populate combos based on combo1 selection

    For $1 = 0 To UBound($aResult) - 1
        If $aResult[$1][0] = $str Then
            GUICtrlSetData($cmb02, $aResult[$1][1])
            GUICtrlSetData($cmb03, $aResult[$1][2])
            GUICtrlSetData($cmb04, $aResult[$1][3])
        EndIf
    Next

    ; set default entry

    _GUICtrlComboBox_SetCurSel($cmb02, 0)
    _GUICtrlComboBox_SetCurSel($cmb03, 0)
    _GUICtrlComboBox_SetCurSel($cmb04, 0)

EndFunc   ;==>_pop_other_combos

excel file used test.xls

kylomas

[Jess]

Hi @kylomas,

thank you so much for this help. It has helped me a lot.

And sorry for replying too late.