test_XLSMNGMT

OnExit, CleanUp ; Runs the specified subroutine when the script closes
SetWorkingDir %A_ScriptDir%
; ==================================================
; GLOBAL VARIABLES
; ==================================================

xlApp := ComObjCreate("Excel.Application") ; Create an instance of Excel
Margin := 10, 10 ; Margin of the UI
; WorkDir := "D:\Perso\Taff\Scania\Scripts\CMC\test.xlsx" ; Where is the script located
WorkDir := A_ScriptDir "\test.xlsx" ; Where is the script located
Clients_List := "ALL||Client1|Cllient2|Client3" ; Will fill the DropDownList with clients IDs
Header_Range := "A1;B1;D1;F1;G1;H1;L1" ; Header's range in Excel file
Cell_Range := "A2;B2;D2;F2;G2;H2;L2" ; First row of value (temporary test)
Sheet_Name := "Sheet1" ; Sheet name, may change in the future, depends on the extracted Excel file

; Excel is invisible by default. Once you have tested the script this line can be removed.
xlApp.Visible := true
; xlApp.Visible := false


; ==================================================
; Gui
; ==================================================
; Create the GUI. Add text, edit boxes, and buttons, then show the GUI.
    Gui, Color, FFFFFF
    Gui, Font, s12 Bold c000000, Century Gothic
    Gui, Add, Button, Section xm w100 gLoad, Load
    ; Headers content
    Gui, Add, Button, Section xm y+10 w140, Keep it? ; Hidden to keep UI elements aligned
    Gui, Add, Button, ys wp vHeader_1, ; Header Title in cell A1
    Gui, Add, Button, ys wp vHeader_2, ; Header Change Number in cell B1
    Gui, Add, Button, ys wp vHeader_3, ; Header Change Type in cell D1
    Gui, Add, Button, ys wp vHeader_4, ; Header Planned Start in cell F1
    Gui, Add, Button, ys wp vHeader_5, ; Header Planned End in cell G1
    Gui, Add, Button, ys wp vHeader_6, ; Header Change open in cell H1
    Gui, Add, Button, ys wp vHeader_7 ; Header Main Altered CI list in cell L1
    Gui, Add, Button, ys wp, Clients ; Header Clients list in cell R1
    ; Rows content
    Gui, Add, Checkbox, Section xm y+10 w140 vKeep, ; Check box to remove line
    Gui, Font,
    Gui, Font, s8 c000000, Verdana
    Gui, Add, Edit, Hidden wp ys r3 vE1,
    Gui, Add, Edit, Hidden wp ys vE2,
    Gui, Add, Edit, Hidden wp ys vE3,
    Gui, Add, Edit, Hidden wp ys vE4,
    Gui, Add, Edit, Hidden wp ys vE5,
    Gui, Add, Edit, Hidden wp ys vE6,
    Gui, Add, Edit, Hidden wp ys vE7,
    Gui, Add, DDL, wp ys vE8, %Clients_List%
    Gui, Font, s12 Bold c000000, Century Gothic
    Gui, Add, Button, Section xm y+50 w80 h25 gGuiClose, Quit
    Gui, Show, ,CMC - Changes of the day
return ; End of auto-execute section

; ==================================================
; LABELS
; ==================================================
GuiEscape:
GuiClose:
    ; Gosub, CleanUp
    ExitApp
return


Load:
if (MyWorkbook) ; for simplicity, we don't want to be able to open multiple workbooks.
    return

; FileSelectFile, MyFile, 3,, Select an Excel workbook file., Excel (*.xls; *.xlsx)
; if (ErrorLevel) ; The user dismissed the file dialog without selecting anything.
    ; return

MyFile := WorkDir
MyWorkbook := xlApp.Workbooks.Open(MyFile) ; Open the file in Excel

; Counts total of not empty rows in column B (change number)
xlApp := ComObjActive("Excel.Application")
Row_Number := % xlApp.Worksheets("Sheet1").Range("B:M").Cells.SpecialCells(2).Count ; xlCellTypeConstants = 2


; Read headers in the range A1:B1;D1;F1:G1;L1. Update the GUI.
for MyRange, in MyWorkbook.Sheets(Sheet_Name).Range(Header_Range)
{
    GuiControl,, Header_%A_Index%, % MyRange.Text
}

for MyRange, in MyWorkbook.Sheets(Sheet_Name).Range(Cell_Range)
    {
    GuiControl, Show, E%A_Index%,
    GuiControl,, E%A_Index%, % MyRange.Text
    ; Gui, Add, Edit, wp ys vE%A_Index%, % MyRange.Text
    }
    ; Else
        ; Break
    ; }
Return



CleanUp: ; This will cleanup before the script exits
MyWorkbook.Close(0) ; 0 = don't save changes. Changes should have already been saved.
xlApp.Quit()
ExitApp




; ==================================================
; FUNCTIONS
; ==================================================



; ==================================================
; HOTKEYS
; ==================================================
F11::ListVars
Return