Problem with listboxs & Excel

[mafioso]

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)

expandcollapse popup

#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 April 18, 2008 by mafioso

[PsaltyDS]

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)"?

[mafioso]

#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

[PsaltyDS]

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

[mafioso]

Thanks !

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

write($listbox2, $count + 2) to

write($listbox2, $count + 1)

=)

Thanks again !

Edited April 18, 2008 by mafioso

[mafioso]

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 ?

[PsaltyDS]

"For $i = 1 To 5"

[mafioso]

Stupid me.