take GUI inputs, then build an array and write to excel

[kor 5]

expandcollapse popup

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <TabConstants.au3>
#include <WindowsConstants.au3>
#Include <GuiComboBox.au3>
#Include <Array.au3>
#include <Excel.au3>

Global $staffvars[6][2]
Global $staffarray[2][7]

$gui = GUICreate("Account Creator", 280, 275, 320, 227) ; left, top, width, height 
$PageControl1 = GUICtrlCreateTab(8, 8, 263, 225)

; Staff Tab
$stafftab = GUICtrlCreateTabItem("Staff Account")
GUICtrlCreateLabel("First Name:", 18, 38, 65, 20) ; left, top, width, height
$staffvars[0][0] = GUICtrlCreateInput("", 90, 36, 125, 20)
GUICtrlCreateLabel("Last Name:", 18, 63, 65, 20) ; left, top, width, height
$staffvars[1][0] = GUICtrlCreateInput("", 90, 61, 125, 20)
GUICtrlCreateLabel("Staff ID:", 18, 89, 65, 20) ; left, top, width, height
$staffvars[2][0] = GUICtrlCreateInput("", 90, 86, 125, 20) 
GUICtrlCreateLabel("Location:", 18, 114, 65, 20) ; left, top, width, height
$staffvars[3][0] = GUICtrlCreateCombo("", 90, 111, 125, 18, $CBS_DROPDOWNLIST)
GUICtrlSetData(-1, "example|example2")
GUICtrlCreateLabel("Job Title:", 18, 139, 65, 20) ; left, top, width, height
$staffvars[4][0] = GUICtrlCreateInput("", 90, 137, 125, 20)    
GUICtrlCreateLabel("Classification:", 18, 164, 65, 20) ; left, top, width, height
$staffvars[5][0] = GUICtrlCreateCombo("", 90, 162, 125, 20, $CBS_DROPDOWNLIST)
GUICtrlSetData(-1, "1|2")

; Student Tab
$studenttab = GUICtrlCreateTabItem("another Account")

; Below
GUICtrlCreateTabItem("")
$create = GUICtrlCreateButton("&Create", 8, 242, 75, 25)
$reset = GUICtrlCreateButton("&Reset", 91, 242, 75, 25)
$exit = GUICtrlCreateButton("&Exit", 196, 242, 75, 25)
GUICtrlSetState($create, $GUI_DEFBUTTON) ; allows enter inside input boxes
GUISetState(@SW_SHOW)

$msg = 0
While $msg <> $GUI_EVENT_CLOSE
    $msg = GUIGetMsg() 
    Select
        Case $msg = $create
            If GUICtrlRead($PageControl1) = 0 Then ; read what tab you are one
                _CreateStaff()
            ElseIf GUICtrlRead($PageControl1) = 1 Then
                _Createanother()
            Else
                msgbox(0, "error", "error creating")
            EndIf
        Case $msg = $reset
            _SelfRestart()
        Case $msg = $exit
            Exit
    EndSelect
WEnd

Func _SelfRestart()
    If @Compiled Then
        Run(FileGetShortName(@ScriptFullPath))
    Else
        Run(FileGetShortName(@AutoItExe) & " " & FileGetShortName(@ScriptFullPath))
    EndIf
    Exit
EndFunc ;==> _SelfRestart

Func _CreateStaff()
    Local $errorcheck
    For $x = 0 To 5
        If Not GUICtrlRead($staffvars[$x][0]) Then $errorcheck = 1
    Next
    If $errorcheck Then MsgBox(16, "Error", "All fields are required")
EndFunc ;==> _CreateStaff

    ; Create headers
    $staffarray[0][0] = "First Name"
    $staffarray[0][1] = "Last Name"
    $staffarray[0][2] = "Staff ID"
    $staffarray[0][3] = "Location"
    $staffarray[0][4] = "Title"
    $staffarray[0][5] = "Classification"
    $staffarray[0][6] = "Action"
    $staffarray[1][6] = "New Hire"
    _ArrayDisplay($staffarray)
    $staffarray[1][0] = $staffvars[0][1]
    $staffarray[1][1] = $staffvars[1][1]
    $staffarray[1][2] = $staffvars[2][1]
    $staffarray[1][3] = $staffvars[3][1]
    $staffarray[1][4] = $staffvars[4][1]
    $staffarray[1][5] = $staffvars[5][1]
    _ArrayDisplay($staffarray)
    Local $oExcel = _ExcelBookNew() ;Create new book, make it visible
    _ExcelWriteArray($oExcel, 0, 0, $staffarray)
    _ExcelBookSaveAs($oExcel, @ScriptDir & "\Temp.xls", "xls", 0, 1) ; Now we save it into the temp directory; overwrite existing file if necessary
    _ExcelBookClose($oExcel) ; And finally we close out

First great gui and take in all inputs.

Do a loop to make sure all fields have a value

Take the values and create an array formatted how I have set up my array.

Write the array to an excel file.

I have almost everything up until it's time to write the excel sheet. My final array display shows me that the array looks like I need it to look, and is formatted correctly. However when it writes to the excel sheet nothing is written.

I'm sure my approach is convoluted, so if anyone knows a more efficient way to approach it let me know. The array has to be formatted with Row 0 having all the headers because another script depends on everything being formatted that way in another program.

[enaiman 16]

Use column 1 and row 1 in your _ExcelWriteArray function - I am pretty sure that row/column 0 does not exist

[kor 5]

C:\Program Files (x86)\AutoIt3\Include\Excel.au3 (526) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

$oExcel.Activesheet.Cells($iStartRow, ($xx - $iIndexBase) + $iStartColumn).Value = $aArray[$xx]

$oExcel.Activesheet.Cells($iStartRow, ($xx - $iIndexBase) + $iStartColumn).Value = ^ ERROR

I get the above error when changing my excelwrite to 1, 1

[enaiman 16]

- Changing row/column to 1, 1 still stands - it won't work if they are set to 0,0

- this isn't in the docs but _ExcelWriteArray works only with 1-dimensional arrays; if you try to use a 2-dimensional it will fail (as it did)

An alternative: use _ExcelWriteSheetFromArray - that function can write 2-dimensional arrays - only pay attention each time to StartRow/StartColumn; if you don't care about these, just set them to 1,1 everytime.