ComboBox populated from Excel file?

[nikolas]

Hi all,

I am new user of autoit and try to make a simple app. I want to have a combobox in my form that is populated through a range from excel ("A:A"). Not all rows are full of course, but's a next step.

 

I tried this code, but my combobox is always empty...

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

Local $oExcel = _Excel_open(false,false,true,true,false)

$sWorkbook = "C:\Users\protos\Desktop\nikolas\GROUP 2016_123.123.xlsx"
$oWorkbook = _Excel_BookOpen($oExcel,$sWorkbook,False,False,Default,Default,Default)
$aResult = _Excel_RangeRead($oWorkbook,"ΤΗΛΕΦΩΝΑ",$oWorkbook.ActiveSheet.Usedrange.Columns("A:A"),1,False)

#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 615, 437, -648, 503)
$Combo1 = GUICtrlCreateCombo("", 88, 40, 220, 250)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

GUICtrlSetData($Combo1,$aResult)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

    EndSwitch
WEnd

Any idea/help?

[kylomas]

nikolas,

GuiCtrlSetData takes a simple variable (not an array).  Try it like this...

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

Local $oExcel = _Excel_open(false,false,true,true,false)

;$sWorkbook = "C:\Users\protos\Desktop\nikolas\GROUP 2016_123.123.xlsx"
$sWorkbook = @scriptdir & "\test.xls"
$oWorkbook = _Excel_BookOpen($oExcel,$sWorkbook,False,False,Default,Default,Default)
$aResult = _Excel_RangeRead($oWorkbook,"FO",$oWorkbook.ActiveSheet.Usedrange.Columns("A:A"),1,False)

_arraydisplay($aResult)

#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 615, 437, 648, 503)
$Combo1 = GUICtrlCreateCombo("", 88, 40, 220, 250)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

GUICtrlSetData($Combo1,_arraytostring($aResult))

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

    EndSwitch
WEnd

This is one of many ways to populate a combo box.  You should also do some error checking after function calls.

Test spreadsheet test.xls

kylomas

Edited November 12, 2016 by kylomas

[nikolas]

17 hours ago, kylomas said:

nikolas,

GuiCtrlSetData takes a simple variable (not an array).  Try it like this...

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

Local $oExcel = _Excel_open(false,false,true,true,false)

;$sWorkbook = "C:\Users\protos\Desktop\nikolas\GROUP 2016_123.123.xlsx"
$sWorkbook = @scriptdir & "\test.xls"
$oWorkbook = _Excel_BookOpen($oExcel,$sWorkbook,False,False,Default,Default,Default)
$aResult = _Excel_RangeRead($oWorkbook,"FO",$oWorkbook.ActiveSheet.Usedrange.Columns("A:A"),1,False)

_arraydisplay($aResult)

#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 615, 437, 648, 503)
$Combo1 = GUICtrlCreateCombo("", 88, 40, 220, 250)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

GUICtrlSetData($Combo1,_arraytostring($aResult))

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

    EndSwitch
WEnd

This is one of many ways to populate a combo box.  You should also do some error checking after function calls.

Test spreadsheet test.xls

kylomas

This is great kylomas! Thank you very match!

Now, I have another "problem" with this.

When I use me excel file in .xls format, everything is nice! But when i try to use xlsx or xlsm, there is a situation... In fact nothing is in my combobox, than just "-1". Neither the Array is Displayed...

With your test file, worked fine when i saved it as xlsx...

I think maybe it's because of the time that takes to load the workbook. If I try and have a delay in my script (is this possible at all?) will fix it?

 

Thank's again for helping me...

[nikolas]

In fact workbook itself want shown up. It just loads for a second, and then closes. So I have an empty excel application remaining on screen.

[nikolas]

Ok, I solved my problem... (Can't see edit button in my post). 

While in xls I have no problem with the format of cells, in xlsx I had to convert them to something else than "currency".

 

See you in another post