Jump to content
nikolas

ComboBox populated from Excel file?

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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 by kylomas
  • Like 1

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
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...

Share this post


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

Share this post


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

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

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.