nikolas

ComboBox populated from Excel file?

5 posts in this topic

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?

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by kylomas
1 person likes this

Forum Rules         Procedure for posting code

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 :)

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