Jess

Populating Combo Box from the excel sheet

3 posts in this topic

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                  c
App1    en1        s1        c1
App1    en2        s1        c2
App2    en3        s2        c4    
App2    en3        s1        c3
App3    en1        s1        c7

 

Thank You

Share this post


Link to post
Share on other sites



Jess,

Is this what you are asking...?

; *** 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


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

#3 ·  Posted

Hi @kylomas,

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

And sorry for replying too late.

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