Sign in to follow this  
Followers 0
mafioso

Problem with listboxs & Excel

8 posts in this topic

#1 ·  Posted (edited)

Hello all.

I'm trying to write two listbox data to Excel, like, the first listbox has got 4 items, second 3. I want to write to excel the content of both listboxes like B1, B2, B3, B4, B5, B6, B7...I hope you got what I'm saying =)

I'm using the ExcelCOM_UDF.

Ex. (I get 0's & -1's lol)

#include <GUIConstants.au3>
#include <String.au3>
#include <GUIListBox.au3>
#include <ExcelCom_UDF.au3>


$Form1 = GUICreate("AForm1", 329, 276, 193, 115)
$listbox1 = GUICtrlCreateList("", 24, 20, 137, 175)
GUICtrlSetData(-1, "1|2|3|4")
$listbox2 = GUICtrlCreateList("", 168, 20, 129, 175)
GUICtrlSetData(-1, "5|6|7")
$write = GUICtrlCreateButton("Write !", 62, 208, 199, 43, 0)
GUISetState(@SW_SHOW)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $write
        $k0c = _GUICtrlListBox_GetCount($listbox1)
        $k1c = _GuiCtrlListBox_GetCount($listbox2)
        $oExcel = _ExcelBookNew(1)
        write ($listbox1, "1", _GUICtrlListBox_GetCount($listbox1))
        $count = _GUICtrlListBox_GetCount ($listbox1)
        $count1 = _GUICtrlListBox_GetCount ($listbox2)
        write ($listbox2, $count + $count1, $count + $count + $count1)
    EndSwitch
WEnd

Func write ($list, $f, $count)
    $n = $f
    For $i = $n To $count
    $value = _GuiCtrlListBox_GetItemData ($list, $i)
    _ExcelWriteCell($oExcel, $value, "B" & $i + 2);B & $i means, for ex. if the $i = 1, instead of B1, it would write to B3
Next
EndFunc
Edited by mafioso

Share this post


Link to post
Share on other sites



To start with, you want _GuiCtrlListBox_GetText(), not ...GetItemData().

Next, the item indexes are 0-based, but the Excel cell numbering is 1-based, so your math is bound to be off.

Then, what is up with the weird math here: "write ($listbox2, $count + $count1, $count + $count + $count1)"?

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#include <GUIConstants.au3>
#include <String.au3>
#include <GUIListBox.au3>
#include <ExcelCom_UDF.au3>


$Form1 = GUICreate("AForm1", 329, 276, 193, 115)
$listbox1 = GUICtrlCreateList("", 24, 20, 137, 175)
GUICtrlSetData(-1, "1|2|3|4")
$listbox2 = GUICtrlCreateList("", 168, 20, 129, 175)
GUICtrlSetData(-1, "5|6|7")
$write = GUICtrlCreateButton("Write !", 62, 208, 199, 43, 0)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $write
        $oExcel = _ExcelBookNew(1)
        write ($listbox1, "1", _GUICtrlListBox_GetCount($listbox1))
        $count = _GUICtrlListBox_GetCount ($listbox1)
        $count1 = _GUICtrlListBox_GetCount ($listbox2)
        write ($listbox2, $count, $count + $count1)
    EndSwitch
WEnd

Func write ($list, $f, $count)
    $n = $f
    For $i = $n To $count
    $value = _GuiCtrlListBox_GetText ($list, $i)
    _ExcelWriteCell($oExcel, $value, "B" & $i + 2);B & $i means, for ex. if the $i = 3, then it would write to B3
Next
EndFunc;==> Writing

I don't know why it doesn't work now :S

Share this post


Link to post
Share on other sites

This works, although you might have to tweak the math to get exactly the rows you want. It simply lets the called function find out how many items there are for itself:

#include <GUIConstants.au3>
#include <String.au3>
#include <GUIListBox.au3>
#include <ExcelCom_UDF.au3>


$Form1 = GUICreate("AForm1", 329, 276, 193, 115)
$listbox1 = GUICtrlCreateList("", 24, 20, 137, 175)
GUICtrlSetData(-1, "1|2|3|4")
$listbox2 = GUICtrlCreateList("", 168, 20, 129, 175)
GUICtrlSetData(-1, "5|6|7")
$write = GUICtrlCreateButton("Write !", 62, 208, 199, 43, 0)
GUISetState(@SW_SHOW)
#EndRegion

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $write
            $oExcel = _ExcelBookNew(1)
            write($listbox1, "1")
            $count = _GUICtrlListBox_GetCount($listbox1)
            write($listbox2, $count + 2)
    EndSwitch
WEnd

; $list = Listbox control, $f = starting row for Excel write
Func write($list, $f)
    Local $iCnt = _GUICtrlListBox_GetCount($list)
    For $i = 0 To $iCnt - 1
        $value = _GUICtrlListBox_GetText($list, $i)
        _ExcelWriteCell($oExcel, $value, "B" & ($f + $i))
    Next
EndFunc   ;==>write

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Thanks !

EDIT : And yeah, I got the result I wanted by changing

write($listbox2, $count + 2) to

write($listbox2, $count + 1)

=)

Thanks again !

Edited by mafioso

Share this post


Link to post
Share on other sites

A new problem (nothing to do with listboxes, though)

Example :

#include <GUIConstants.au3>
#include <ExcelCom_UDF.au3>


$Form1 = GUICreate("AForm1", 184, 73, 193, 115)
$write = GUICtrlCreateButton("Write", 24, 14, 141, 41, 0)
GUISetState(@SW_SHOW)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $write
            $oExcel = _ExcelBookNew(1)
        For $i = 1 To $i = 5
            $value = IniRead ("C:\Documents and Settings\Administrator\My Documents\temp.ini", "k0", $i, "nf")
        _ExcelWriteCell($oExcel, $value, "B" & $i + 2)  
        Next
    EndSwitch
WEnd

So, it should read the .ini file which looks like this :

[k0]

1=014 Koncesije i ostala prava

2=026 Stambene zgrade i stanovi

3=035 Bioloska imovina u pripremi

[k1]

4=111 Poluproizvodi i dijelovi vlastite proizvodnje

5=124 Proizvodi u komisiinu i konsignaciji

And then just write it to Excel...and I don't get why it won't work ?

Share this post


Link to post
Share on other sites

Stupid me.

:D

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
Sign in to follow this  
Followers 0