Writing changes back to array - over-write old data

Fractured · September 18, 2018

Ok..so ive got everything working, kinda!! I can read my excel file to an array, display it in the gui, check to see if the user updated the data in the gui, but now I cant figure out how to update the array with the data. I would like it to overwrite the old info with the new info. I looked at _ArrayAdd and _ArrayInsert but it always just adds the process but not any of the other data....and both seem to make a new entry and not overwrite...Feel im missing something....Any hel pwould be great!!

Heres the code...the array is roughly 147Rx16C...I cant add the excel since this is for my company information...

; #INDEX# =================================================================================================
; Name ..........: Process dB Manager
; Version Date ..: 2018-09-17
; AutoIt Version : 3.3.8.1
; Author(s) .....: Charles Wright
; Dll(s) ........:
; Error handling :
; =========================================================================================================

; #Notes# =================================================================================================
; Columns:  0   =   Process - $Process - 21                     9   =   Gold Strike - $GoldStrike - 36
;           1   =   Rev - $Rev - 22                             10  =   Notes - $Edit1 - 37
;           2   =   Type - $Type - 23                           11  =   Drawing Control - $DrawingControl - 25
;           3   =   ROHS - $ROHS - 24                           12  =   Copper Reference - $CopperRef - 31
;           4   =   Copper - $Copper - 26                       13  =   Electroless Nickel Reference - $ElectrolessRef - 32
;           5   =   Electroless Nickel - $ElectrolessN - 27     14  =   Electrolytic Nickel Reference - $ElectrolyticRef - 33
;           6   =   Electrolytic Nickel - $ElectrolyticN - 28   15  =   Silver Reference - $SilverRef - 34
;           7   =   Silver - $SilverRef - 29                    16  =   Gold Ref - $GoldRef - 35
;           8   =   Gold - $Gold - 30
; =========================================================================================================

; Includes ================================================================================================
#include <Array.au3>
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <Excel.au3>
#include "ExtMsgBox.au3"
#include <GDIPlus.au3>
#include <GUIConstantsEx.au3>
#include <GuiListView.au3>
#include <MsgBoxConstants.au3>
#include <StaticConstants.au3>
#include <WinAPI.au3>
#include <WindowsConstants.au3>
;==========================================================================================================

; Variables ===============================================================================================
$idMsg = 0
$iIndex = ""
;==========================================================================================================

; Create application object
Local $oExcel = _Excel_Open(False)
_ExtMsgBoxSet(1,0,default, default, 10, "Consolas", 1000, 1000)
;==========================================================================================================


; Excel Process ===========================================================================================
If @error Then Exit _ExtMsgBox(64,0, "Error", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Open an existing workbook and return its object identifier.
Local $sWorkbook = @ScriptDir & "\Process.xlsx"
_ExtMsgBox(64,0, "Directory", $sWorkbook)
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, False)

If @error Then Exit _ExtMsgBox(64,0, "Process Opening", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_ExtMsgBox(64,0, "Process Opening", $sWorkbook & " has been opened successfully")

; Read the formulas of a cell range on sheet 2 of the specified workbook
Local $aResult = _Excel_RangeRead($oWorkbook, 1, Default, 1)

If @error Then Exit _ExtMsgBox(64,0, "Process Load Report", "Error loading Processes." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_ExtMsgBox(64,0, "Process Load Report", "Processes successfully loaded." & @CRLF & "Please click 'OK'")

;Array Debugging Code - Comment out after testing
;Local $iRows = UBound($aResult, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10.
;Local $iCols = UBound($aResult, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20.
;Local $iDimension = UBound($aResult, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional.;
;
;MsgBox($MB_SYSTEMMODAL, "", "The array is a " & $iDimension & " dimensional array with " & _
;            $iRows & " row(s) & " & $iCols & " column(s).")
;
$aTempModel = _ArrayUnique($aResult, 0)
$sModel = _ArrayToString($aTempModel, "|", 0)

;$stlModel = StringTrimLeft($sModel, 12)

;_ArrayDisplay($aModel, "$aModel Array")
;_ArrayDisplay($aResult, "$aResult Array")
;MsgBox($MB_SYSTEMMODAL, "$sModel String", $sModel)
;MsgBox($MB_SYSTEMMODAL, "$stlModel String", $stlModel)
;==========================================================================================================

; Gui =====================================================================================================
$Form1_1 = GUICreate("Process dB Manager", 653, 543, 740, 105)
GUISetBkColor(0xFFFBF0)
$Label1 = GUICtrlCreateLabel("Process:", 136, 16, 57, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label2 = GUICtrlCreateLabel("Rev:", 296, 16, 32, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label3 = GUICtrlCreateLabel("Type", 416, 16, 36, 20, $SS_CENTERIMAGE)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label4 = GUICtrlCreateLabel("ROHS:", 464, 72, 46, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label5 = GUICtrlCreateLabel("Copper:", 200, 112, 52, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label6 = GUICtrlCreateLabel("Electroless Nickel:", 136, 144, 115, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label7 = GUICtrlCreateLabel("Electrolytic Nickel:", 136, 176, 113, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label8 = GUICtrlCreateLabel("Silver:", 208, 208, 41, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label9 = GUICtrlCreateLabel("Gold:", 216, 240, 36, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label10 = GUICtrlCreateLabel("Drawing Control:", 152, 72, 101, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label11 = GUICtrlCreateLabel("Copper Ref:", 432, 112, 76, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label12 = GUICtrlCreateLabel("Electroless Ref:", 416, 144, 98, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label13 = GUICtrlCreateLabel("Electrolytic Ref:", 416, 176, 96, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label14 = GUICtrlCreateLabel("Silver Ref:", 440, 208, 65, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label15 = GUICtrlCreateLabel("Gold Ref:", 448, 240, 60, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label16 = GUICtrlCreateLabel("Gold Strike", 440, 272, 70, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label17 = GUICtrlCreateLabel("Notes:", 136, 304, 43, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$List1 = GUICtrlCreateList("", 8, 16, 113, 409)     ; 20
$Process = GUICtrlCreateInput("", 200, 16, 65, 24)  ; 21
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Rev = GUICtrlCreateInput("", 336, 16, 41, 24)      ; 22
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Type = GUICtrlCreateInput("", 464, 16, 73, 24)     ;23
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ROHS = GUICtrlCreateInput("", 512, 72, 25, 24, BitOR($GUI_SS_DEFAULT_INPUT, $ES_CENTER))   ;24
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$DrawingControl = GUICtrlCreateInput("", 264, 72, 185, 24)      ; 25
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Copper = GUICtrlCreateInput("", 264, 112, 121, 24) ;26
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolessN = GUICtrlCreateInput("", 264, 144, 121, 24)   ;27
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolyticN = GUICtrlCreateInput("", 264, 176, 121, 24)  ;28
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Silver = GUICtrlCreateInput("", 264, 208, 121, 24)     ; 29
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Gold = GUICtrlCreateInput("", 264, 240, 121, 24)       ; 30
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$CopperRef = GUICtrlCreateInput("", 520, 112, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolessRef = GUICtrlCreateInput("", 520, 144, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolyticRef = GUICtrlCreateInput("", 520, 176, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$SilverRef = GUICtrlCreateInput("", 520, 208, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$GoldRef = GUICtrlCreateInput("", 520, 240, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$GoldStrike = GUICtrlCreateInput("", 520, 272, 25, 24, BitOR($GUI_SS_DEFAULT_INPUT, $ES_CENTER))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Edit1 = GUICtrlCreateEdit("", 184, 304, 457, 121)
GUICtrlSetData(-1, "Edit1")
$SaveRecord = GUICtrlCreateButton("Save Record", 88, 448, 99, 25) ; 38
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$DeleteRecord = GUICtrlCreateButton("Delete Record", 264, 448, 107, 25) ; 39
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$SavedB = GUICtrlCreateButton("Save Database", 464, 448, 107, 25) ; 40
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
;==========================================================================================================

; GUICtrlSet ==============================================================================================
GUISetState(@SW_SHOW)
GUICtrlSetData($List1, "" & $sModel & "")
;==========================================================================================================

; While Loop / Cases ======================================================================================
While 1
    $idMsg = GUIGetMsg()


    Select
        Case $idMsg = $GUI_EVENT_CLOSE
            _Excel_BookClose($oWorkbook, False)
            If @error Then Exit _ExtMsgBox(16,0, "Process Close Report", "Error closing processes." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
            _ExtMsgBox(64,0, "Process Close Report", "Processes successfully closed.")

            WinClose($oWorkbook, "Microsoft Excel")
            If Not @error Then
                _ExtMsgBox(64,0, "Excel", "Excel has been successfully closed.")
            Else
                _ExtMsgBox(16,0, "Excel", "Excel has failed to be closed.")
            EndIf

            ExitLoop
        Case $idMsg = $GUI_EVENT_MINIMIZE
            _ExtMsgBox(64,0, "", "Process Manager minimized", 2)
        Case $idMsg = $GUI_EVENT_MAXIMIZE
            _ExtMsgBox(64,0, "", "Process Manager restored", 2)
        Case $idMsg = $List1
            _Select($List1)
        Case $idMsg = $SaveRecord
            _Update()
    EndSelect
WEnd
;==========================================================================================================

; Functions ===============================================================================================
Func _Select($List1) ;$Combo1 - Selection of Process from Array to display in GUI
    $ChosenProc = GUICtrlRead($List1) ;$Combo1
    $iIndex = _ArraySearch($aResult, $ChosenProc)

    ;MsgBox($MB_SYSTEMMODAL, "$iIndex Array Row", $iIndex) ; $iIndex = The array row # that relates to the chosen process

    GUICtrlSetData($Process, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 0, 0))                ;0 / 21
    GUICtrlSetData($Rev, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 1, 1))                    ;1 / 22
    GUICtrlSetData($Type, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 2, 2))                   ;2 / 23
    GUICtrlSetData($ROHS, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 3, 3))                   ;3 / 24
    GUICtrlSetData($Copper, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 4, 4))                 ;4 / 26
    GUICtrlSetData($ElectrolessN, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 5, 5))           ;5 / 27
    GUICtrlSetData($ElectrolyticN, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 6, 6))          ;6 / 28
    GUICtrlSetData($Silver, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 7, 7))                 ;7 ; 29
    GUICtrlSetData($Gold, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 8, 8))                   ;8 / 30
    GUICtrlSetData($GoldStrike, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 9, 9))             ;9 / 36
    GUICtrlSetData($Edit1, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 10, 10))                ;10 / 37
    GUICtrlSetData($DrawingControl, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 11, 11))       ;11 / 25
    GUICtrlSetData($CopperRef, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 12, 12))            ;12 / 31
    GUICtrlSetData($ElectrolessRef, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 13, 13))       ;13 / 32
    GUICtrlSetData($ElectrolyticRef, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 14, 14))      ;14 / 33
    GUICtrlSetData($SilverRef, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 15, 15))            ;15 / 34
    GUICtrlSetData($GoldRef, _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", 16, 16))              ;16 / 35
EndFunc   ;==>_Select

Func _Update() ; Updates the process in the Excel dB
    ;MsgBox($MB_SYSTEMMODAL, "$iIndex Array Row", $iIndex) ; $iIndex = The array row # that relates to the chosen process
    $sRecordtoUpdate = ""
    $sRecordtoTest = ""
    $StringCount = ""

    ;Read Array Row to Compare
    For $ArrayCount = 0 To 16
        ;_ArrayToString($aArray,Delimiter,StartRow,EndRow,Delimiter,StartColumn,EndColumn)
        $sRecordtoUpdate = $sRecordtoUpdate & "|" & _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", $ArrayCount, $ArrayCount)
    Next
    $sRecordtoUpdate = StringTrimLeft($sRecordtoUpdate, 1)

    ;Read GUI Controls to Compare
    For $StringCountA = 21 To 24
        $sRecordtoTest = $sRecordtoTest & "|" & GUICtrlRead($StringCountA)
    Next
    For $StringCountB = 26 to 30
        $sRecordtoTest = $sRecordtoTest & "|" & GUICtrlRead($StringCountB)
    Next
    For $StringCountC = 36 to 37
        $sRecordtoTest = $sRecordtoTest & "|" & GUICtrlRead($StringCountC)
    Next
    $sRecordtoTest = $sRecordtoTest & "|" & GUICtrlRead(25)
    For $StringCountD = 31 to 35
        $sRecordtoTest = $sRecordtoTest & "|" & GUICtrlRead($StringCountD)
    Next
    $sRecordtoTest= StringTrimLeft($sRecordtoTest, 1)

    ;Compare the Array Row to the Gui Controls
    $iCmp = StringCompare($sRecordtoUpdate, $sRecordtoTest)
    If $iCmp = 0 Then
        _ExtMsgBox(64,0, "Process Save Error", "No changes, process will not updated")
    Else
        _ExtMsgBox(64,0, "Process Save", "Process will be Updated")
        _ArrayInsert($aResult, $iIndex, $sRecordtoTest, 0, "|")
        _ArrayDisplay($aResult)
    Endif

    ;_ExtMsgBox(64,0, "Strings", "Record from Array" & @CRLF & $sRecordtoUpdate & @CRLF & $sRecordtoTest & @CRLF & "Record from GUI" &@CRLF & @CRLF & $iCmp)

EndFunc   ;==>_Update

jdelaney · September 18, 2018

Redim $array[ubound($array)+1)][ubound ($array,2)]

Then loop through that last row of the array and set the data.

Fractured · September 18, 2018

Would I have to use Stringsplit first or just ArrayAdd...tried to ArrayInsert but it only fills to column10 then flips to next row....

Fractured · September 18, 2018

NVM!! Figured it out!! Thanks to jdelaney for the help!!

Subz · September 18, 2018

Not sure you know but you can just use a similar method to your _Select() function except rather than use _ArraytoString just access the data directly, also you should try to keep your list and your array synchronized that way you can use the selected index in the Listbox as the index of the array. For the two functions below to work correctly you would need to change the following line from 0 to 1 meaning don't include the count since your array doesn't use a count. Hope that makes sense.

$sModel = _ArrayToString($aTempModel, "|", 1)

; Functions ===============================================================================================
Func _Select() ;$Combo1 - Selection of Process from Array to display in GUI
    $iIndex = _GUICtrlListBox_GetCurSel($List1)

    GUICtrlRead($Process, $aResult[$iIndex][0])                ;0 / 21
    GUICtrlSetData($Rev, $aResult[$iIndex][1])                    ;1 / 22
    GUICtrlSetData($Type, $aResult[$iIndex][2])                   ;2 / 23
    GUICtrlSetData($ROHS, $aResult[$iIndex][3])                   ;3 / 24
    GUICtrlSetData($Copper, $aResult[$iIndex][4])                 ;4 / 26
    GUICtrlSetData($ElectrolessN, $aResult[$iIndex][5])           ;5 / 27
    GUICtrlSetData($ElectrolyticN, $aResult[$iIndex][6])          ;6 / 28
    GUICtrlSetData($Silver, $aResult[$iIndex][7])                 ;7 ; 29
    GUICtrlSetData($Gold, $aResult[$iIndex][8])                   ;8 / 30
    GUICtrlSetData($GoldStrike, $aResult[$iIndex][9])             ;9 / 36
    GUICtrlSetData($Edit1, $aResult[$iIndex][10])                ;10 / 37
    GUICtrlSetData($DrawingControl, $aResult[$iIndex][11])       ;11 / 25
    GUICtrlSetData($CopperRef, $aResult[$iIndex][12])            ;12 / 31
    GUICtrlSetData($ElectrolessRef, $aResult[$iIndex][13])       ;13 / 32
    GUICtrlSetData($ElectrolyticRef, $aResult[$iIndex][14])      ;14 / 33
    GUICtrlSetData($SilverRef, $aResult[$iIndex][15])            ;15 / 34
    GUICtrlSetData($GoldRef, $aResult[$iIndex][16])              ;16 / 35
EndFunc   ;==>_Select

Func _Update() ;$Combo1 - Selection of Process from Array to display in GUI
    $iIndex = _GUICtrlListBox_GetCurSel($List1)

    $aResult[$iIndex][0] = GUICtrlRead($Process)                ;0 / 21
    $aResult[$iIndex][1] = GUICtrlRead($Rev)                    ;1 / 22
    $aResult[$iIndex][2] = GUICtrlRead($Type)                   ;2 / 23
    $aResult[$iIndex][3] = GUICtrlRead($ROHS)                   ;3 / 24
    $aResult[$iIndex][4] = GUICtrlRead($Copper)                 ;4 / 26
    $aResult[$iIndex][5] = GUICtrlRead($ElectrolessN)           ;5 / 27
    $aResult[$iIndex][6] = GUICtrlRead($ElectrolyticN)          ;6 / 28
    $aResult[$iIndex][7] = GUICtrlRead($Silver)                 ;7 ; 29
    $aResult[$iIndex][8] = GUICtrlRead($Gold)                   ;8 / 30
    $aResult[$iIndex][9] = GUICtrlRead($GoldStrike)             ;9 / 36
    $aResult[$iIndex][10] = GUICtrlRead($Edit1)                ;10 / 37
    $aResult[$iIndex][11] = GUICtrlRead($DrawingControl)       ;11 / 25
    $aResult[$iIndex][12] = GUICtrlRead($CopperRef)            ;12 / 31
    $aResult[$iIndex][13] = GUICtrlRead($ElectrolessRef)       ;13 / 32
    $aResult[$iIndex][14] = GUICtrlRead($ElectrolyticRef)      ;14 / 33
    $aResult[$iIndex][15] = GUICtrlRead($SilverRef)            ;15 / 34
    $aResult[$iIndex][16] = GUICtrlRead($GoldRef)              ;16 / 35
EndFunc   ;==>_Select

Fractured · September 18, 2018

Subz that works awesome! Learning new methods every day!! This is what I came up with that seemed to work..although I like your method beter!

Func _Update() ; Updates the process in the Excel dB
    $sRecordtoUpdate = ""
    $sRecordtoTest = ""
    $StringCount = ""

    ;Read Array Row to Compare
    For $ArrayCount = 0 To 16
        $sRecordtoUpdate = $sRecordtoUpdate & "|" & _ArrayToString($aResult, "|", $iIndex, $iIndex, "|", $ArrayCount, $ArrayCount)
    Next
    $sRecordtoUpdate = StringTrimLeft($sRecordtoUpdate, 1)

    ;Read GUI Controls to Compare
    For $StringCountA = 21 To 24
        $sRecordtoTest = $sRecordtoTest & "|" & GUICtrlRead($StringCountA)
    Next
    For $StringCountB = 26 to 30
        $sRecordtoTest = $sRecordtoTest & "|" & GUICtrlRead($StringCountB)
    Next
    For $StringCountC = 36 to 37
        $sRecordtoTest = $sRecordtoTest & "|" & GUICtrlRead($StringCountC)
    Next
    $sRecordtoTest = $sRecordtoTest & "|" & GUICtrlRead(25)
    For $StringCountD = 31 to 35
        $sRecordtoTest = $sRecordtoTest & "|" & GUICtrlRead($StringCountD)
    Next
    $sRecordtoTest= StringTrimLeft($sRecordtoTest, 1)

    ;Compare the Array Row to the Gui Controls
    $iCmp = StringCompare($sRecordtoUpdate, $sRecordtoTest)
    If $iCmp = 0 Then
        _ExtMsgBox(64,0, "Process Save Error", "No changes, process will not updated.")
    Else
        $sRecordtoTest = StringRegExpReplace($sRecordtoTest,"\r\n|\r|\n", " -- ")
        _ExtMsgBox(64,0, "Process Save", "Process will be Updated but not Saved till Exit.")
        Redim $aResult[ubound ($aResult)+1][18]
        _ArrayDelete($aResult, $iIndex)
        _ArrayAdd($aResult,$sRecordtoTest,"|")
        _ArraySort($aResult)
        Redim $aResult[ubound ($aResult)+1][18]
        _ArrayDisplay($aResult, "Sorted Array")
    Endif
EndFunc   ;==>_Update

Fractured · September 20, 2018

Question...I can delete a row and redim the array but it always seems to put an odd entry at the bottom of the list and if you select it, I get console error:

"K:\Work\AutoIT Scripts\Mine\Models\Excel DB\Alt_ProcessdBManager.au3" (210) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
GUICtrlRead($Process, $aResult[$iIndex][0])
GUICtrlRead($Process, ^ ERROR

Here is the code so far using Subz Fix!!

; #INDEX# =================================================================================================
; Name ..........: Process dB Manager
; Version Date ..: 2018-09-17
; AutoIt Version : 3.3.8.1
; Author(s) .....: Charles Wright
; Dll(s) ........:
; Error handling :
; =========================================================================================================

; #Notes# =================================================================================================
; Columns:  0   =   Process - $Process - 21                     9   =   Gold Strike - $GoldStrike - 36
;           1   =   Rev - $Rev - 22                             10  =   Notes - $Edit1 - 37
;           2   =   Type - $Type - 23                           11  =   Drawing Control - $DrawingControl - 25
;           3   =   ROHS - $ROHS - 24                           12  =   Copper Reference - $CopperRef - 31
;           4   =   Copper - $Copper - 26                       13  =   Electroless Nickel Reference - $ElectrolessRef - 32
;           5   =   Electroless Nickel - $ElectrolessN - 27     14  =   Electrolytic Nickel Reference - $ElectrolyticRef - 33
;           6   =   Electrolytic Nickel - $ElectrolyticN - 28   15  =   Silver Reference - $SilverRef - 34
;           7   =   Silver - $SilverRef - 29                    16  =   Gold Ref - $GoldRef - 35
;           8   =   Gold - $Gold - 30
; =========================================================================================================

; Includes ================================================================================================
#include <Array.au3>
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <Excel.au3>
#include "ExtMsgBox.au3"
#include <GDIPlus.au3>
#include <GUIConstantsEx.au3>
#include <GuiListView.au3>
#include <GuiListBox.au3>
#include <MsgBoxConstants.au3>
#include <StaticConstants.au3>
#include <WinAPI.au3>
#include <WindowsConstants.au3>
;==========================================================================================================

; Variables ===============================================================================================
$idMsg = 0
$iIndex = ""
;==========================================================================================================

; Create application object
Local $oExcel = _Excel_Open(False)
_ExtMsgBoxSet(1,0,default, default, 10, "Consolas", 1000, 1000)
;==========================================================================================================


; Excel Process ===========================================================================================
If @error Then Exit _ExtMsgBox(64,0, "Error", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Open an existing workbook and return its object identifier.
Local $sWorkbook = @ScriptDir & "\Process.xlsx"
_ExtMsgBox(64,0, "Directory", $sWorkbook)
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, False)

If @error Then Exit _ExtMsgBox(64,0, "Process Opening", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_ExtMsgBox(64,0, "Process Opening", $sWorkbook & " has been opened successfully")

; Read the formulas of a cell range on sheet 2 of the specified workbook
Local $aResult = _Excel_RangeRead($oWorkbook, 1, Default, 1)

If @error Then Exit _ExtMsgBox(64,0, "Process Load Report", "Error loading Processes." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_ExtMsgBox(64,0, "Process Load Report", "Processes successfully loaded." & @CRLF & "Please click 'OK'")

;Array Debugging Code - Comment out after testing
;Local $iRows = UBound($aResult, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10.
;Local $iCols = UBound($aResult, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20.
;Local $iDimension = UBound($aResult, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional.;
;
;MsgBox($MB_SYSTEMMODAL, "", "The array is a " & $iDimension & " dimensional array with " & _
;            $iRows & " row(s) & " & $iCols & " column(s).")
;
$aTempModel = _ArrayUnique($aResult, 0)
$sModel = _ArrayToString($aTempModel, "|", 0)

;$stlModel = StringTrimLeft($sModel, 12)

;_ArrayDisplay($aModel, "$aModel Array")
;_ArrayDisplay($aResult, "$aResult Array")
;MsgBox($MB_SYSTEMMODAL, "$sModel String", $sModel)
;MsgBox($MB_SYSTEMMODAL, "$stlModel String", $stlModel)
;==========================================================================================================

; Gui =====================================================================================================
$Form1_1 = GUICreate("Process dB Manager", 653, 543, 740, 105)
GUISetBkColor(0xFFFBF0)
$Label1 = GUICtrlCreateLabel("Process:", 136, 16, 57, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label2 = GUICtrlCreateLabel("Rev:", 296, 16, 32, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label3 = GUICtrlCreateLabel("Type", 416, 16, 36, 20, $SS_CENTERIMAGE)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label4 = GUICtrlCreateLabel("ROHS:", 464, 72, 46, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label5 = GUICtrlCreateLabel("Copper:", 200, 112, 52, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label6 = GUICtrlCreateLabel("Electroless Nickel:", 136, 144, 115, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label7 = GUICtrlCreateLabel("Electrolytic Nickel:", 136, 176, 113, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label8 = GUICtrlCreateLabel("Silver:", 208, 208, 41, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label9 = GUICtrlCreateLabel("Gold:", 216, 240, 36, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label10 = GUICtrlCreateLabel("Drawing Control:", 152, 72, 101, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label11 = GUICtrlCreateLabel("Copper Ref:", 432, 112, 76, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label12 = GUICtrlCreateLabel("Electroless Ref:", 416, 144, 98, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label13 = GUICtrlCreateLabel("Electrolytic Ref:", 416, 176, 96, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label14 = GUICtrlCreateLabel("Silver Ref:", 440, 208, 65, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label15 = GUICtrlCreateLabel("Gold Ref:", 448, 240, 60, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label16 = GUICtrlCreateLabel("Gold Strike", 440, 272, 70, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label17 = GUICtrlCreateLabel("Notes:", 136, 304, 43, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$List1 = GUICtrlCreateList("", 8, 16, 113, 409)     ; 20
$Process = GUICtrlCreateInput("", 200, 16, 65, 24)  ; 21
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Rev = GUICtrlCreateInput("", 336, 16, 41, 24)      ; 22
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Type = GUICtrlCreateInput("", 464, 16, 73, 24)     ;23
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ROHS = GUICtrlCreateInput("", 512, 72, 25, 24, BitOR($GUI_SS_DEFAULT_INPUT, $ES_CENTER))   ;24
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$DrawingControl = GUICtrlCreateInput("", 264, 72, 185, 24)      ; 25
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Copper = GUICtrlCreateInput("", 264, 112, 121, 24) ;26
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolessN = GUICtrlCreateInput("", 264, 144, 121, 24)   ;27
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolyticN = GUICtrlCreateInput("", 264, 176, 121, 24)  ;28
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Silver = GUICtrlCreateInput("", 264, 208, 121, 24)     ; 29
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Gold = GUICtrlCreateInput("", 264, 240, 121, 24)       ; 30
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$CopperRef = GUICtrlCreateInput("", 520, 112, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolessRef = GUICtrlCreateInput("", 520, 144, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolyticRef = GUICtrlCreateInput("", 520, 176, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$SilverRef = GUICtrlCreateInput("", 520, 208, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$GoldRef = GUICtrlCreateInput("", 520, 240, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$GoldStrike = GUICtrlCreateInput("", 520, 272, 25, 24, BitOR($GUI_SS_DEFAULT_INPUT, $ES_CENTER))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Edit1 = GUICtrlCreateEdit("", 184, 304, 457, 121)
GUICtrlSetData(-1, "Edit1")
$UpdateRecord = GUICtrlCreateButton("Update Record", 88, 448, 99, 25) ; 38
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$DeleteRecord = GUICtrlCreateButton("Delete Record", 264, 448, 107, 25) ; 39
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$SavedB = GUICtrlCreateButton("Save Database", 464, 448, 107, 25) ; 40
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
;==========================================================================================================

; GUICtrlSet ==============================================================================================
GUISetState(@SW_SHOW)
GUICtrlSetData($List1, "" & $sModel & "")
;==========================================================================================================

; While Loop / Cases ======================================================================================
While 1
    $idMsg = GUIGetMsg()


    Select
        Case $idMsg = $GUI_EVENT_CLOSE
            _Excel_BookClose($oWorkbook, False)
            If @error Then Exit _ExtMsgBox(16,0, "Process Close Report", "Error closing processes." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
            _ExtMsgBox(64,0, "Process Close Report", "Processes successfully closed.")

            WinClose($oWorkbook, "Microsoft Excel")
            If Not @error Then
                _ExtMsgBox(64,0, "Excel", "Excel has been successfully closed.")
            Else
                _ExtMsgBox(16,0, "Excel", "Excel has failed to be closed.")
            EndIf

            ExitLoop
        Case $idMsg = $GUI_EVENT_MINIMIZE
            _ExtMsgBox(64,0, "", "Process Manager minimized", 2)
        Case $idMsg = $GUI_EVENT_MAXIMIZE
            _ExtMsgBox(64,0, "", "Process Manager restored", 2)
        Case $idMsg = $List1
            _Select()
        Case $idMsg = $UpdateRecord
            _Update()
        Case $idMsg = $DeleteRecord
            _Delete()
        Case $idMsg = $SavedB
            _SavedB()
    EndSelect
WEnd
;==========================================================================================================

; Functions ===============================================================================================
Func _Select() ;$Combo1 - Selection of Process from Array to display in GUI
    $iIndex = _GUICtrlListBox_GetCurSel($List1)

    GUICtrlRead($Process, $aResult[$iIndex][0])                 ;0 / 21
    GUICtrlSetData($Process, $aResult[$iIndex][0])              ;0 / 21
    GUICtrlSetData($Rev, $aResult[$iIndex][1])                  ;1 / 22
    GUICtrlSetData($Type, $aResult[$iIndex][2])                 ;2 / 23
    GUICtrlSetData($ROHS, $aResult[$iIndex][3])                 ;3 / 24
    GUICtrlSetData($Copper, $aResult[$iIndex][4])               ;4 / 26
    GUICtrlSetData($ElectrolessN, $aResult[$iIndex][5])         ;5 / 27
    GUICtrlSetData($ElectrolyticN, $aResult[$iIndex][6])        ;6 / 28
    GUICtrlSetData($Silver, $aResult[$iIndex][7])               ;7 ; 29
    GUICtrlSetData($Gold, $aResult[$iIndex][8])                 ;8 / 30
    GUICtrlSetData($GoldStrike, $aResult[$iIndex][9])           ;9 / 36
    GUICtrlSetData($Edit1, $aResult[$iIndex][10])               ;10 / 37
    GUICtrlSetData($DrawingControl, $aResult[$iIndex][11])      ;11 / 25
    GUICtrlSetData($CopperRef, $aResult[$iIndex][12])           ;12 / 31
    GUICtrlSetData($ElectrolessRef, $aResult[$iIndex][13])      ;13 / 32
    GUICtrlSetData($ElectrolyticRef, $aResult[$iIndex][14])     ;14 / 33
    GUICtrlSetData($SilverRef, $aResult[$iIndex][15])           ;15 / 34
    GUICtrlSetData($GoldRef, $aResult[$iIndex][16])             ;16 / 35
EndFunc   ;==>_Select

Func _Update() ;$Combo1 - Selection of Process from Array to display in GUI
    $iIndex = _GUICtrlListBox_GetCurSel($List1)

    $aResult[$iIndex][0] = GUICtrlRead($Process)                ;0 / 21
    $aResult[$iIndex][1] = GUICtrlRead($Rev)                    ;1 / 22
    $aResult[$iIndex][2] = GUICtrlRead($Type)                   ;2 / 23
    $aResult[$iIndex][3] = GUICtrlRead($ROHS)                   ;3 / 24
    $aResult[$iIndex][4] = GUICtrlRead($Copper)                 ;4 / 26
    $aResult[$iIndex][5] = GUICtrlRead($ElectrolessN)           ;5 / 27
    $aResult[$iIndex][6] = GUICtrlRead($ElectrolyticN)          ;6 / 28
    $aResult[$iIndex][7] = GUICtrlRead($Silver)                 ;7 ; 29
    $aResult[$iIndex][8] = GUICtrlRead($Gold)                   ;8 / 30
    $aResult[$iIndex][9] = GUICtrlRead($GoldStrike)             ;9 / 36
    $aResult[$iIndex][10] = GUICtrlRead($Edit1)                ;10 / 37
    $aResult[$iIndex][11] = GUICtrlRead($DrawingControl)       ;11 / 25
    $aResult[$iIndex][12] = GUICtrlRead($CopperRef)            ;12 / 31
    $aResult[$iIndex][13] = GUICtrlRead($ElectrolessRef)       ;13 / 32
    $aResult[$iIndex][14] = GUICtrlRead($ElectrolyticRef)      ;14 / 33
    $aResult[$iIndex][15] = GUICtrlRead($SilverRef)            ;15 / 34
    $aResult[$iIndex][16] = GUICtrlRead($GoldRef)              ;16 / 35
EndFunc   ;==>_Select

Func _Delete()
    GUICtrlSetData($Process, "")                ;0 / 21
    GUICtrlSetData($Rev, "")                    ;1 / 22
    GUICtrlSetData($Type, "")                   ;2 / 23
    GUICtrlSetData($ROHS, "")                   ;3 / 24
    GUICtrlSetData($Copper, "")                 ;4 / 26
    GUICtrlSetData($ElectrolessN, "")           ;5 / 27
    GUICtrlSetData($ElectrolyticN, "")          ;6 / 28
    GUICtrlSetData($Silver, "")                 ;7 ; 29
    GUICtrlSetData($Gold, "")                   ;8 / 30
    GUICtrlSetData($GoldStrike, "")             ;9 / 36
    GUICtrlSetData($Edit1, "")                  ;10 / 37
    GUICtrlSetData($DrawingControl, "")         ;11 / 25
    GUICtrlSetData($CopperRef, "")              ;12 / 31
    GUICtrlSetData($ElectrolessRef, "")         ;13 / 32
    GUICtrlSetData($ElectrolyticRef, "")        ;14 / 33
    GUICtrlSetData($SilverRef, "")              ;15 / 34
    GUICtrlSetData($GoldRef, "")                ;16 / 35
    GUICtrlSetData($List1, "")
    $aResult[$iIndex][0] = GUICtrlRead($Process)                ;0 / 21
    $aResult[$iIndex][1] = GUICtrlRead($Rev)                    ;1 / 22
    $aResult[$iIndex][2] = GUICtrlRead($Type)                   ;2 / 23
    $aResult[$iIndex][3] = GUICtrlRead($ROHS)                   ;3 / 24
    $aResult[$iIndex][4] = GUICtrlRead($Copper)                 ;4 / 26
    $aResult[$iIndex][5] = GUICtrlRead($ElectrolessN)           ;5 / 27
    $aResult[$iIndex][6] = GUICtrlRead($ElectrolyticN)          ;6 / 28
    $aResult[$iIndex][7] = GUICtrlRead($Silver)                 ;7 ; 29
    $aResult[$iIndex][8] = GUICtrlRead($Gold)                   ;8 / 30
    $aResult[$iIndex][9] = GUICtrlRead($GoldStrike)             ;9 / 36
    $aResult[$iIndex][10] = GUICtrlRead($Edit1)                ;10 / 37
    $aResult[$iIndex][11] = GUICtrlRead($DrawingControl)       ;11 / 25
    $aResult[$iIndex][12] = GUICtrlRead($CopperRef)            ;12 / 31
    $aResult[$iIndex][13] = GUICtrlRead($ElectrolessRef)       ;13 / 32
    $aResult[$iIndex][14] = GUICtrlRead($ElectrolyticRef)      ;14 / 33
    $aResult[$iIndex][15] = GUICtrlRead($SilverRef)            ;15 / 34
    $aResult[$iIndex][16] = GUICtrlRead($GoldRef)              ;16 / 35
    _ArrayDelete($aResult, $iIndex)
    Redim $aResult[UBound($aResult)][17]
    _ArraySort($aResult)
    $aTempModel = _ArrayUnique($aResult, 0)
    $sModel = _ArrayToString($aTempModel, "|", 0)
    GUICtrlSetData($List1, "" & $sModel & "")
    _ArrayDisplay($aResult, "Sorted Array")
EndFunc

Func _SavedB()
    _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, False)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel Error", "Error saving workbook to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel Message", "Workbook successfully saved as '" & $sWorkbook & "'.")
    ShellExecute($sWorkbook)
EndFunc

Subz · September 20, 2018

Do you require _ArrayUnique() function? It would be throwing off the indexing in the array? For example if you had 10 Rows with 5 duplicates, when you use _ArrayUnique you only have 5 items showing in the list, however your array will still have 10. If you don't mind having duplicates than you can use something like, below, I also didn't know how you were saving your data from your array back into the database, the Savedb function would have just saved the same results, I've added in a way to save the results to a new file named @Year-@Mon-@Mday_@Hour@Min@Sec_Process.xlsx just to show how to save the data, otherwise you would need to clear all the existing data from the spreadsheet and add the $aResult to the sheet:

Hope that makes sense.

; #INDEX# =================================================================================================
; Name ..........: Process dB Manager
; Version Date ..: 2018-09-17
; AutoIt Version : 3.3.8.1
; Author(s) .....: Charles Wright
; Dll(s) ........:
; Error handling :
; =========================================================================================================

; #Notes# =================================================================================================
; Columns:  0   =   Process - $Process - 21                     9   =   Gold Strike - $GoldStrike - 36
;           1   =   Rev - $Rev - 22                             10  =   Notes - $Edit1 - 37
;           2   =   Type - $Type - 23                           11  =   Drawing Control - $DrawingControl - 25
;           3   =   ROHS - $ROHS - 24                           12  =   Copper Reference - $CopperRef - 31
;           4   =   Copper - $Copper - 26                       13  =   Electroless Nickel Reference - $ElectrolessRef - 32
;           5   =   Electroless Nickel - $ElectrolessN - 27     14  =   Electrolytic Nickel Reference - $ElectrolyticRef - 33
;           6   =   Electrolytic Nickel - $ElectrolyticN - 28   15  =   Silver Reference - $SilverRef - 34
;           7   =   Silver - $SilverRef - 29                    16  =   Gold Ref - $GoldRef - 35
;           8   =   Gold - $Gold - 30
; =========================================================================================================

; Includes ================================================================================================
#include <Array.au3>
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <Excel.au3>
#include "ExtMsgBox.au3"
#include <GDIPlus.au3>
#include <GUIConstantsEx.au3>
#include <GuiListView.au3>
#include <GuiListBox.au3>
#include <MsgBoxConstants.au3>
#include <StaticConstants.au3>
#include <WinAPI.au3>
#include <WindowsConstants.au3>
;==========================================================================================================

; Variables ===============================================================================================
$idMsg = 0
$iIndex = ""
;==========================================================================================================

; Create application object
Local $oExcel = _Excel_Open(False)
_ExtMsgBoxSet(1,0,default, default, 10, "Consolas", 1000, 1000)
;==========================================================================================================


; Excel Process ===========================================================================================
If @error Then Exit _ExtMsgBox(64,0, "Error", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Open an existing workbook and return its object identifier.
Local $sSavebook = @ScriptDir & "\" & @YEAR & "-" & @MON & "-" & @MDAY & "_" & @HOUR & @MIN & @SEC & "_Process.xlsx"
Local $sWorkbook = @ScriptDir & "\Process.xlsx"
_ExtMsgBox(64,0, "Directory", $sWorkbook)
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, False)

If @error Then Exit _ExtMsgBox(64,0, "Process Opening", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_ExtMsgBox(64,0, "Process Opening", $sWorkbook & " has been opened successfully")

; Read the formulas of a cell range on sheet 2 of the specified workbook
Local $aResult = _Excel_RangeRead($oWorkbook, 1, Default, 1)

If @error Then Exit _ExtMsgBox(64,0, "Process Load Report", "Error loading Processes." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_ExtMsgBox(64,0, "Process Load Report", "Processes successfully loaded." & @CRLF & "Please click 'OK'")

_Excel_BookClose($oWorkbook, False)

If @error Then Exit _ExtMsgBox(16,0, "Process Close Report", "Error closing processes." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_ExtMsgBox(64,0, "Process Close Report", "Processes successfully closed.")

$oWorkbook = _Excel_BookNew($oExcel)
;Array Debugging Code - Comment out after testing
;Local $iRows = UBound($aResult, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10.
;Local $iCols = UBound($aResult, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20.
;Local $iDimension = UBound($aResult, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional.;
;
;MsgBox($MB_SYSTEMMODAL, "", "The array is a " & $iDimension & " dimensional array with " & _
;            $iRows & " row(s) & " & $iCols & " column(s).")
;

_ArrayInsert($aResult, 0, UBound($aResult) - 1)
$sModel = _ArrayToString($aResult, "|", 0, -1, "|", 0, 0)
;$stlModel = StringTrimLeft($sModel, 12)

;_ArrayDisplay($aModel, "$aModel Array")
;_ArrayDisplay($aResult, "$aResult Array")
;MsgBox($MB_SYSTEMMODAL, "$sModel String", $sModel)
;MsgBox($MB_SYSTEMMODAL, "$stlModel String", $stlModel)
;==========================================================================================================

; Gui =====================================================================================================
$Form1_1 = GUICreate("Process dB Manager", 653, 543, 740, 105)
GUISetBkColor(0xFFFBF0)
$Label1 = GUICtrlCreateLabel("Process:", 136, 16, 57, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label2 = GUICtrlCreateLabel("Rev:", 296, 16, 32, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label3 = GUICtrlCreateLabel("Type", 416, 16, 36, 20, $SS_CENTERIMAGE)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label4 = GUICtrlCreateLabel("ROHS:", 464, 72, 46, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label5 = GUICtrlCreateLabel("Copper:", 200, 112, 52, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label6 = GUICtrlCreateLabel("Electroless Nickel:", 136, 144, 115, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label7 = GUICtrlCreateLabel("Electrolytic Nickel:", 136, 176, 113, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label8 = GUICtrlCreateLabel("Silver:", 208, 208, 41, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label9 = GUICtrlCreateLabel("Gold:", 216, 240, 36, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label10 = GUICtrlCreateLabel("Drawing Control:", 152, 72, 101, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label11 = GUICtrlCreateLabel("Copper Ref:", 432, 112, 76, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label12 = GUICtrlCreateLabel("Electroless Ref:", 416, 144, 98, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label13 = GUICtrlCreateLabel("Electrolytic Ref:", 416, 176, 96, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label14 = GUICtrlCreateLabel("Silver Ref:", 440, 208, 65, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label15 = GUICtrlCreateLabel("Gold Ref:", 448, 240, 60, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label16 = GUICtrlCreateLabel("Gold Strike", 440, 272, 70, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label17 = GUICtrlCreateLabel("Notes:", 136, 304, 43, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$List1 = GUICtrlCreateList("", 8, 16, 113, 409, BitOr($WS_BORDER, $WS_VSCROLL))     ; 20
$iDummyStart = GUICtrlCreateDummy()
$Process = GUICtrlCreateInput("", 200, 16, 65, 24)  ; 21
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Rev = GUICtrlCreateInput("", 336, 16, 41, 24)      ; 22
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Type = GUICtrlCreateInput("", 464, 16, 73, 24)     ;23
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ROHS = GUICtrlCreateInput("", 512, 72, 25, 24, BitOR($GUI_SS_DEFAULT_INPUT, $ES_CENTER))   ;24
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$DrawingControl = GUICtrlCreateInput("", 264, 72, 185, 24)      ; 25
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Copper = GUICtrlCreateInput("", 264, 112, 121, 24) ;26
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolessN = GUICtrlCreateInput("", 264, 144, 121, 24)   ;27
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolyticN = GUICtrlCreateInput("", 264, 176, 121, 24)  ;28
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Silver = GUICtrlCreateInput("", 264, 208, 121, 24)     ; 29
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Gold = GUICtrlCreateInput("", 264, 240, 121, 24)       ; 30
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$CopperRef = GUICtrlCreateInput("", 520, 112, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolessRef = GUICtrlCreateInput("", 520, 144, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$ElectrolyticRef = GUICtrlCreateInput("", 520, 176, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$SilverRef = GUICtrlCreateInput("", 520, 208, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$GoldRef = GUICtrlCreateInput("", 520, 240, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$GoldStrike = GUICtrlCreateInput("", 520, 272, 25, 24, BitOR($GUI_SS_DEFAULT_INPUT, $ES_CENTER))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Edit1 = GUICtrlCreateEdit("", 184, 304, 457, 121)
GUICtrlSetData(-1, "Edit1")
$iDummyEnd = GUICtrlCreateDummy()
$UpdateRecord = GUICtrlCreateButton("Update Record", 88, 448, 99, 25) ; 38
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$DeleteRecord = GUICtrlCreateButton("Delete Record", 264, 448, 107, 25) ; 39
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$SavedB = GUICtrlCreateButton("Save Database", 464, 448, 107, 25) ; 40
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
;==========================================================================================================

; GUICtrlSet ==============================================================================================
GUISetState(@SW_SHOW)
GUICtrlSetData($List1, $sModel)
;==========================================================================================================

; While Loop / Cases ======================================================================================
While 1
    $idMsg = GUIGetMsg()


    Select
        Case $idMsg = $GUI_EVENT_CLOSE
            _Excel_BookClose($oWorkbook, False)
            If @error Then Exit _ExtMsgBox(16,0, "Process Close Report", "Error closing processes." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
            _ExtMsgBox(64,0, "Process Close Report", "Processes successfully closed.")

            WinClose($oWorkbook, "Microsoft Excel")
            If Not @error Then
                _ExtMsgBox(64,0, "Excel", "Excel has been successfully closed.")
            Else
                _ExtMsgBox(16,0, "Excel", "Excel has failed to be closed.")
            EndIf

            ExitLoop
        Case $idMsg = $GUI_EVENT_MINIMIZE
            _ExtMsgBox(64,0, "", "Process Manager minimized", 2)
        Case $idMsg = $GUI_EVENT_MAXIMIZE
            _ExtMsgBox(64,0, "", "Process Manager restored", 2)
        Case $idMsg = $List1
            _Select()
        Case $idMsg = $UpdateRecord
            _Update()
        Case $idMsg = $DeleteRecord
            _Delete()
        Case $idMsg = $SavedB
            _SavedB()
    EndSelect
WEnd
;==========================================================================================================

; Functions ===============================================================================================
Func _Select() ;$Combo1 - Selection of Process from Array to display in GUI
    $iIndex = _GUICtrlListBox_GetCurSel($List1)

    GUICtrlRead($Process, $aResult[$iIndex][0])                 ;0 / 21
    GUICtrlSetData($Process, $aResult[$iIndex][0])              ;0 / 21
    GUICtrlSetData($Rev, $aResult[$iIndex][1])                  ;1 / 22
    GUICtrlSetData($Type, $aResult[$iIndex][2])                 ;2 / 23
    GUICtrlSetData($ROHS, $aResult[$iIndex][3])                 ;3 / 24
    GUICtrlSetData($Copper, $aResult[$iIndex][4])               ;4 / 26
    GUICtrlSetData($ElectrolessN, $aResult[$iIndex][5])         ;5 / 27
    GUICtrlSetData($ElectrolyticN, $aResult[$iIndex][6])        ;6 / 28
    GUICtrlSetData($Silver, $aResult[$iIndex][7])               ;7 ; 29
    GUICtrlSetData($Gold, $aResult[$iIndex][8])                 ;8 / 30
    GUICtrlSetData($GoldStrike, $aResult[$iIndex][9])           ;9 / 36
    GUICtrlSetData($Edit1, $aResult[$iIndex][10])               ;10 / 37
    GUICtrlSetData($DrawingControl, $aResult[$iIndex][11])      ;11 / 25
    GUICtrlSetData($CopperRef, $aResult[$iIndex][12])           ;12 / 31
    GUICtrlSetData($ElectrolessRef, $aResult[$iIndex][13])      ;13 / 32
    GUICtrlSetData($ElectrolyticRef, $aResult[$iIndex][14])     ;14 / 33
    GUICtrlSetData($SilverRef, $aResult[$iIndex][15])           ;15 / 34
    GUICtrlSetData($GoldRef, $aResult[$iIndex][16])             ;16 / 35
EndFunc   ;==>_Select

Func _Update() ;$Combo1 - Selection of Process from Array to display in GUI
    $iIndex = _GUICtrlListBox_GetCurSel($List1)

    $aResult[$iIndex][0] = GUICtrlRead($Process)                ;0 / 21
    $aResult[$iIndex][1] = GUICtrlRead($Rev)                    ;1 / 22
    $aResult[$iIndex][2] = GUICtrlRead($Type)                   ;2 / 23
    $aResult[$iIndex][3] = GUICtrlRead($ROHS)                   ;3 / 24
    $aResult[$iIndex][4] = GUICtrlRead($Copper)                 ;4 / 26
    $aResult[$iIndex][5] = GUICtrlRead($ElectrolessN)           ;5 / 27
    $aResult[$iIndex][6] = GUICtrlRead($ElectrolyticN)          ;6 / 28
    $aResult[$iIndex][7] = GUICtrlRead($Silver)                 ;7 ; 29
    $aResult[$iIndex][8] = GUICtrlRead($Gold)                   ;8 / 30
    $aResult[$iIndex][9] = GUICtrlRead($GoldStrike)             ;9 / 36
    $aResult[$iIndex][10] = GUICtrlRead($Edit1)                ;10 / 37
    $aResult[$iIndex][11] = GUICtrlRead($DrawingControl)       ;11 / 25
    $aResult[$iIndex][12] = GUICtrlRead($CopperRef)            ;12 / 31
    $aResult[$iIndex][13] = GUICtrlRead($ElectrolessRef)       ;13 / 32
    $aResult[$iIndex][14] = GUICtrlRead($ElectrolyticRef)      ;14 / 33
    $aResult[$iIndex][15] = GUICtrlRead($SilverRef)            ;15 / 34
    $aResult[$iIndex][16] = GUICtrlRead($GoldRef)              ;16 / 35
EndFunc   ;==>_Select

Func _Delete()
    ;~ Clear all fields
    For $i = $iDummyStart To $iDummyEnd
        GUICtrlSetData($i, "")
    Next
    $iIndex = _GUICtrlListBox_GetCurSel($List1)
    _ArrayDelete($aResult, $iIndex)
    $aResult[0][0] = UBound($aResult) - 1
    _GUICtrlListBox_ReplaceString($List1, 0, $aResult[0][0])
    _GUICtrlListBox_DeleteString($List1, $iIndex)
    $iListCount = _GUICtrlListBox_GetCount($List1)
    If $iIndex >= $iListCount Then
        _GUICtrlListBox_ClickItem($List1, $iListCount - 1)
    Else
        _GUICtrlListBox_ClickItem($List1, $iIndex)
    EndIf
EndFunc

Func _SavedB()
    _ArrayDelete($aResult, 0)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aResult)
    _Excel_BookSaveAs($oWorkbook, $sSavebook, $xlWorkbookDefault, False)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel Error", "Error saving workbook to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel Message", "Workbook successfully saved as '" & $sWorkbook & "'.")
    ShellExecute($sWorkbook)
EndFunc

Fractured · September 20, 2018

Makes perfect sense..

I didnt want to have duplicates in the array, thats why I used the _ArrayUnique function(according to help it removes duplicates). Looking back should I have used a redim to fix the array after the _ArrayUnique function? Or does the _ArrayUnique function just "hide" the duplicates, since they still show in the count?

And thank you for the save function!! I was winging it since im still trying to learn Arrays and Excel functions!! It seemed no matter how I tried to save it, it would come back as an empty workbook!!

I will run this up and see how it all functions so I can get max understanding!! Thanks again Subz!! You rock!

Subz · September 20, 2018

Actually the ArrayUnique will always return a one dimensional array for what ever column you selected, in your case Column 0. What I would do is use Excel to remove the duplicates prior to copying the range, for example add the top three lines above the _Excel_RangeRead function:

Local $iRows = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
Local $aColumns[] = [1, 2, 3, 4, 5, 6, 7 , 8, 9, 10, 11, 12, 13, 14, 15, 16, 17]
$oWorkbook.ActiveSheet.Range("A1:Q" & $iRows).RemoveDuplicates($aColumns)

; Read the formulas of a cell range on sheet 2 of the specified workbook
Local $aResult = _Excel_RangeRead($oWorkbook, 1, Default, 1)
....

Fractured · December 21, 2018

Dont mean to resurrect this old thread but the script was working great then....it didnt. If I try to open the Excel file I have been using I get this error:

"K:\Work\AutoIT Scripts\Mine\Models\Excel DB\Final\ProcessdBManager.au3" (65) : ==> The requested action with this object has failed.:
$oWorkbook.ActiveSheet.Range("A1:Q" & $iRows).RemoveDuplicates($aColumns)
$oWorkbook.ActiveSheet.Range("A1:Q" & $iRows)^ ERROR
->12:32:01 AutoIt3.exe ended.rc:1

If I try to open the back-up of the Excel file I get this error:

error.png.7ca7cce2e1ac27f69f08478cabb46a60.png

Any thoughts? I have tried looking up the error code in the forum, wiki, google....but nothing jumps out... Im running autoit 3.3.14.5 on Win10 64bit

Writing changes back to array - over-write old data

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members