Another Excel question from me

[Bearpocalypse]

I have a sheet that has ticket numbers in one row and a description in another. I have my function pull from the spreadsheet and place the data into an array. I can get the information from the array and place it in my two different list boxes. The only problem I have is that the information is entered chronologically, IE ticket 1 might get paired with description 6. When I bring up the data via _arraydisplay, it all comes up correct. It's just when it's entered into the list box is when it gets sorted chronologically. Here is my code if anyone is interested in seeing it.

Func f_GetTickets()
    GUICtrlSetData($Ticket, "")
    GUICtrlSetData($Desc, "")
 $oExcel = _ExcelBookOpen("Excel.xlsx")

    $oSheet = $oExcel.Worksheets(1)
    $oSheet.Activate                                        ;Activate the appropriate sheet
    $oRange = $oSheet.UsedRange
    $oRange.SpecialCells($xlCellTypeLastCell).Activate
    $newRow = $oExcel.ActiveCell.Row                        ;Obtain the next open row


    ;$aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 0, True) ;Using Default Parameters, except Shifting Column (True)
               ;_ArrayDisplay($aArray, "Array with Column shifting")

for $i = 1 To $newRow
    $Ticknum = _ExcelReadCell($oExcel, $i, 1)
    GUICtrlSetData($Ticket, $Ticknum)
    $Description = _ExcelReadCell($oExcel, $i, 2)
    GUICtrlSetData($Desc, $Description)
Next

_ExcelBookClose($oExcel)
EndFunc

[JLogan3o13]

Can you please post an example of the spreadsheet, even if just a few rows? Not sure why you would see it differently between _ArrayDisplay and what you're inserting with GUICtrlSetData.

[Bearpocalypse]

It seems the description(left column) is the one that is getting sorted now. I just reran and the ticket numbers came out fine.

Edited August 2, 2013 by Bearpocalypse

[JLogan3o13]

I think what you're seeing is actually behavior from the GUICtrlSetData function. Notice that if you change "One" to "_One" it will come out correctly. It does not seem to sort on numbers, just text. Below is what I used, since I didn't know your GUI setup:

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

Local $msg

GUICreate("Test", 300, 300)
$Ticket = GUICtrlCreateList("", 10, 10, 100, 40)
$Desc = GUICtrlCreateList("", 10, 120, 100, 40)

 $oExcel = _ExcelBookOpen(@DesktopDir & "\test.xls")

    $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 0, True) ;Using Default Parameters, except Shifting Column (True)
        _ArrayDisplay($aArray, "Array with Column shifting")

for $i = 1 To $aArray[0][0]
    $Ticknum = $aArray[$i][0] - You have your array, no more need to read from the excel file.
    $Description = $aArray[$i][1]
    GUICtrlSetData($Ticket, $Ticknum)
    GUICtrlSetData($Desc, $Description)
Next

GUISetState(@SW_SHOW)

    While 1
        $msg = GUIGetMsg()
            Select
                Case $msg = $GUI_EVENT_CLOSE
                    ExitLoop
            EndSelect
    WEnd

_ExcelBookClose($oExcel)
GUIDelete()

[Bearpocalypse]

I entered what you have and I still get the sorted text. I'll toss my whole code in so you can see what I'm doing.

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <WindowsConstants.au3>
#include <Excel.au3>
#include <EditConstants.au3>
#include <StaticConstants.au3>
#include <Array.au3>
#include <GUIConstantsEx.au3>



Opt("GUIOnEventMode",-1) ;Enables events, I.E. exiting application from file-exit.

#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Ticket Tracker", 364, 335, 192, 124)
   GUISetOnEvent($gui_event_close, "f_exit")
$Ticket = GUICtrlCreateList("", 24, 80, 73, 201)
$Desc = GUICtrlCreateList("", 104, 80, 241, 201)
$filemenu = GUICtrlCreateMenu("File") ;Creates the File menu.
      $Refresh = GUICtrlCreateMenuItem("Refresh", $filemenu)
      GUICtrlSetOnEvent(-1, "f_GetTickets")
      $ExitItem = GUICtrlCreateMenuItem("Exit", $filemenu) ;Creates the exit submenu under File.
      GUICtrlSetOnEvent(-1, "f_exit") ;calls the function to close the appplication via file-exit.
$MakeTick = GUICtrlCreateInput("", 32, 16, 97, 21)
$MakeDesc = GUICtrlCreateInput("", 136, 16, 169, 21)
$Write = GUICtrlCreateButton("Write", 32, 40, 49, 25)
    GUICtrlSetOnEvent(-1, "f_write")
$Clear = GUICtrlCreateButton("Clear", 80, 40, 49, 25)
   GUICtrlSetOnEvent(-1, "f_reset")
$Delete = GUICtrlCreateButton("Remove all data", 262, 285)
    GUICtrlSetOnEvent(-1, "f_Delete")

GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

;----------------------------
Func f_GetTickets()
    GUICtrlSetData($Ticket, "")
    GUICtrlSetData($Desc, "")

    local $msg

 $oExcel = _ExcelBookOpen("Excel.xlsx")

    $oSheet = $oExcel.Worksheets(1)
    $oSheet.Activate                                        ;Activate the appropriate sheet
    $oRange = $oSheet.UsedRange
    $oRange.SpecialCells($xlCellTypeLastCell).Activate
    $newRow = $oExcel.ActiveCell.Row                        ;Obtain the next open row


        $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 0, True) ;Using Default Parameters, except Shifting Column (True)

        ;_ArrayDisplay($aArray, "Array with Column shifting")



for $i = 1 To $aArray[0][0]
    $Ticknum = $aArray[$i][0]; - You have your array, no more need to read from the excel file.
    $Description = $aArray[$i][1]
    GUICtrlSetData($Ticket, $Ticknum)
    GUICtrlSetData($Desc, $Description)

Next
GUISetState(@SW_SHOW)
    While 1
        $msg = GUIGetMsg()
            Select
                Case $msg = $GUI_EVENT_CLOSE
                    ExitLoop
            EndSelect
    WEnd

_ExcelBookClose($oExcel)

GUIDelete()
EndFunc
;----------------------------
Func f_write()
    $T1 = guictrlread($MakeTick)
    $D1 = GUICtrlRead($makedesc)
    $oExcel = _ExcelBookOpen("Excel.xlsx")

    $oSheet = $oExcel.Worksheets(1)
    $oSheet.Activate                                        ;Activate the appropriate sheet
    $oRange = $oSheet.UsedRange
    $oRange.SpecialCells($xlCellTypeLastCell).Activate
    $newRow = $oExcel.ActiveCell.Row                        ;Obtain the next open row


; Read column A to an array
Global $aData = _ExcelReadArray($oExcel, 1, 1, 10, 1, 1)
; Read column B to an array
Global $bData = _ExcelReadArray($oExcel, 1, 2, 10, 1 ,1)

; find the last used cell in this column
Global $iLastUsed = 0, $iNextRow = 0
For $n = UBound($aData) - 1 To 1 Step -1
    If StringStripWS($aData[$n], 8) <> "" Then
        $iLastUsed = $n
        ExitLoop
    EndIf
Next
If $iLastUsed Then
    $iNextRow = $iLastUsed + 1
EndIf

For $bn = UBound($bData) - 1 To 1 Step -1
    If StringStripWS($bData[$bn], 8) <> "" Then
        $iLastUsed = $bn
        ExitLoop
    EndIf
Next
If $iLastUsed Then
    $iNextRow = $iLastUsed + 1
    EndIf

_ExcelWriteCell($oExcel, $T1, $iNextRow, 1)
_ExcelWriteCell($oExcel, $D1, $iNextRow, 2)
_ExcelBookSave($oExcel)
_ExcelBookClose($oExcel)
EndFunc
;----------------------------
func f_Delete()
$oExcel = _ExcelBookOpen("Excel.xlsx")
    _ExcelColumnDelete($oExcel, 1, 1)
    _ExcelColumnDelete($oExcel, 1, 2)
    _ExcelBookSave($oExcel)
    _ExcelBookClose($oExcel)
EndFunc
;----------------------------
Func f_exit()
   Exit
EndFunc
;----------------------------
func f_reset()
   guictrlsetdata($maketick, "")
   GUICtrlSetData($makedesc, "")
EndFunc
;----------------------------

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

    EndSwitch
WEnd

[soonyee91]

Bearpocalpse,

I face the same problem like you last time. If you use _arraydisplay() and the results are in correct order that means your array is sorted in that order. The problem you face is when you want to display in listbox it help you sort again. The best way is you disable sorting in your listbox. I believe you create your listbox with default value (default value contain sorting which will sort your item but will not affect your array)

In my previous attempt. I used this code:

$hListBox = GUICtrlCreateList("", 10, 20, 390, 150, BitOR($WS_BORDER, $WS_VSCROLL, $WS_HSCROLL))

you can check help file for certain listbox criteria

you must use BitOr() to specify which listbox criteria you want.

As you can see my lisbox criteria is I want vertical and horizontal scroll and thin line border only

Hope my explaination helps you!

[Bearpocalypse]

Soonyee91,

Once again you guys prove how awesome you are. BitOr worked! Thanks so much for your help, and you too JL.

Edited August 3, 2013 by Bearpocalypse

[soonyee91]

Bearpocalypse,

No problem. Its my pleasure to help you.