benners Posted January 9, 2018 Share Posted January 9, 2018 There are over 400 controls. do you mean the check boxes and remarks as well?. Seems like a waste but if you use the sheet I attached in post 6, create a new sheet and add the text you want in the sheet, I'll have a look. The text must be in the same cell on sheet 2 as the would be control is on sheet 1. By this I mean if you want a tip for Quote · Casing then put the text for the tip in Sheets 2 Cell A2. I will take a look and there may be better options than all the tips. I would assume that tips are only needed for the items listed in sheet1, as the checkboxes and inputs do the same thing. Is this correct? Link to comment Share on other sites More sharing options...
KickStarter15 Posted January 10, 2018 Author Share Posted January 10, 2018 @benners, Thanks, it's just this below function. Checkboxes and remarks input are not included. $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateInput($s_Value, $i_Left - 50, $i_Top, 155, 20, BitOR($GUI_SS_DEFAULT_INPUT, $ES_READONLY), $WS_EX_TRANSPARENT) ; use an input GUICtrlSetBkColor(-1, $COLOR_WHITE) 12 hours ago, benners said: create a new sheet and add the text you want in the sheet, I'll have a look. The text must be in the same cell on sheet 2 as the would be control is on sheet 1. I have attached a sample excel following your instructions. Sample.xlsx Programming is "To make it so simple that there are obviously no deficiencies" or "To make it so complicated that there are no obvious deficiencies" by C.A.R. Hoare. Link to comment Share on other sites More sharing options...
KickStarter15 Posted January 10, 2018 Author Share Posted January 10, 2018 @benners, I tried using this GUICtrlSetTip(-1, $s_Value) but it shows only the sheet 1 and I don't know how to show sheet 2 using this code. $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateInput($s_Value, $i_Left - 50, $i_Top, 155, 20, BitOR($GUI_SS_DEFAULT_INPUT, $ES_READONLY), $WS_EX_TRANSPARENT) ; use an input GUICtrlSetBkColor(-1, $COLOR_WHITE) GUICtrlSetTip(-1, $s_Value) ; it can tip entries in sheet1 but not in sheet2. Kind of hard to read your code but slowly catching up in it. Programming is "To make it so simple that there are obviously no deficiencies" or "To make it so complicated that there are no obvious deficiencies" by C.A.R. Hoare. Link to comment Share on other sites More sharing options...
benners Posted January 10, 2018 Share Posted January 10, 2018 Yep if you do it that way it will add the same tooltip for all the input controls. It needs modding expandcollapse popup#Region #### Includes ################################ #include <ButtonConstants.au3> #include <ColorConstants.au3> #include <EditConstants.au3> #include <Excel.au3> #include <FontConstants.au3> #include <GUIConstantsEx.au3> #include <GUIScrollbars_Ex.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <Array.au3> #EndRegion #### Includes ################################ Opt('GUIOnEventMode', 1) HotKeySet('{ESC}', 'CloseProgram') #Region #### Globals ################################# ; these are integers that relate to where the controliDs are located in $g_aidControls. ; The control ID can be retrieved like so, $g_aidControls[$EMPPROCESS_INP] Global Enum _ ; values are 0 to 13 $FRONTMATTERGUI, _ ;_ gui 1 $BODYMATTERGUI, _ ;_ gui 2 $BACKMATTERGUI, _ ;_ gui 3 $TABCOUNT, _ ; #### IMPORTANT #### this must be after the last gui. it signifies how many tabs there will be and is used in other functions $EMPNUMBER_INP, _ ;_ employee number input id $EMPNAME_INP, _ ;_ employee name input id $EMPPROCESS_INP, _ ;_ employee process input id $AID_LBL, _ ;_ some label I don't know if you use id $AGREE_CHK, _ ;_ I agree checkbox id $ACCEPT_CHK, _ ;_ accept checkbox id $REJECT_CHK, _ ;_ reject checkbox id $LAYOUTONE_RDO, _ ;_ radio button for layout 1 selection $SUBMIT_BTN, _ ;_ submit button id $MAIN_TAB, _ ;_ main tab id $ARRAYMAX_ROWS ;_ number of rows to give the array its 0 based ; create the array to hold the remaining controlIDs. These can be referenced ; later when writing their values to the excel sheet Global $g_aidControls[$ARRAYMAX_ROWS] ; create the arrays to hold the all controlIDs that will be created on the tab guis by CreateTabGUIs() called by Draw_Tabs() function ; These can be referenced later when writing their values to the excel sheet Global _ $aid_FrontMatterGUI = '', _ $aid_BodyMatterGUI = '', _ $aid_BackMatterGUI = '' ; these are the tab names and will be used as the spreadsheet names when the report is submitted ; these have the same value as the Global Enum $FRONTMATTERGUI to $BACKMATTERGUI and are used in varios functions Global $g_asTabNames[$TABCOUNT] = [ _ ; values are 0 to 2 'Front Matter', _ 'Body Matter', _ 'Back Matter'] ; these are integers that relate to where the rows are for each set of controls in the $aid_FrontMatterGUI array etc ; they are also the same values as their matching strings index in the $g_asTabHeaderText array Global Enum _ ; values are 0 to 5 $ENTRYS_COL, _ $NA_COL, _ $NOERROR_COL, _ $WITHERROR_COL, _ $REMARKS_COL, _ $ARRAYMAX_COLS ; this is the array that holds the tab header text strings. These are written to the first row of the array that matches the tab and gui ; I.E - ; Tab = 'Front Matter' ; Array with the controlIDs = $aid_FrontMatterGUI Global $g_asTabHeaderText[$ARRAYMAX_COLS] = [ _ ; 0 based array 'Entry(s)', _ 'N/A', _ 'No Error', _ 'With Error', _ 'Remarks'] ; this keeps track of if the report layout is one or two Global $g_iMultipleSheets = 1 ; these keep track of the cells that need tweaking in the report Global _ $g_sHeaderCells = '', _ ;_ These are the strings above the inline controls such as Entry(s) N/A etc $g_sSectionCells = '', _ ;_ Sections, these are upper case strings like CATEGORY, HISTORY DATES $g_sTabNameCells = '' ;_ tab names #EndRegion #### Globals ################################# Global $hWnd_Previous Draw_GUI() While 1 ;~ $tGetMousePos = _WinAPI_GetMousePos() ;~ $hWnd = _WinAPI_WindowFromPoint($tGetMousePos) ;~ If $hWnd <> $hWnd_Previous Then ;~ $hWnd_Previous = $hWnd ;~ ConsoleWrite('ControlID: ' & _WinAPI_GetDlgCtrlID($hWnd) & @CRLF) ;~ EndIf Sleep(50) WEnd ; #### you need to fix this #### ; i have guessed the line as you are trying to use $sUserName but in the uncommented line it is used as a string ; still returns blank for me Func GetFullName($sUserName) ; I can't test this Local $strComputer = 'localhost' Local $objWMIService = ObjGet('winmgmts:\\' & $strComputer & '\root\CIMV2') Local $colItems = $objWMIService.ExecQuery("SELECT * FROM Win32_UserAccount WHERE Name = '" & $sUserName & "'", "WQL", 0x10 + 0x20) If Not IsObj($colItems) Then Return SetError(1, 0, '') For $objItem In $colItems Return $objItem.FullName Next EndFunc ;==>GetFullName #Region #### CheckBox actions ######################## ; get the state of the checkbox Func CheckBox_ReturnStringState($id_ControlID) If GUICtrlRead($id_ControlID) = $GUI_CHECKED Then Return 'Checked' Return '' ;~ Return 'Unchecked' ; use this if you want it to return unchecked instead of blank EndFunc ;==>CheckBox_ReturnStringState ; inline check boxes Func NaChk_SetState() ; get the controlID of the checkbox that was clicked Local $i_ControlID = @GUI_CtrlId GUICtrlSetState(@GUI_CtrlId + 1, $GUI_UNCHECKED) ; uncheck the No Error checkbox GUICtrlSetState(@GUI_CtrlId + 2, $GUI_UNCHECKED) ; uncheck the With Error checkbox EndFunc ;==>NaChk_SetState Func NoErrorChk_SetState() ; get the controlID of the checkbox that was clicked Local $i_ControlID = @GUI_CtrlId GUICtrlSetState(@GUI_CtrlId - 1, $GUI_UNCHECKED) ; uncheck the NA checkbox GUICtrlSetState(@GUI_CtrlId + 1, $GUI_UNCHECKED) ; uncheck the With Error checkbox EndFunc ;==>NoErrorChk_SetState Func WithErrorChk_SetState() ; get the controlID of the checkbox that was clicked Local $i_ControlID = @GUI_CtrlId GUICtrlSetState(@GUI_CtrlId - 1, $GUI_UNCHECKED) ; uncheck the No Error checkbox GUICtrlSetState(@GUI_CtrlId - 2, $GUI_UNCHECKED) ; uncheck the NA checkbox EndFunc ;==>WithErrorChk_SetState ; accept, reject Func AcceptChk_Clicked() ; uncheck the reject control GUICtrlSetState($g_aidControls[$REJECT_CHK], $GUI_UNCHECKED) ; check if the submit button is to be enabled SubmitBtn_SetState() EndFunc ;==>AcceptChk_Clicked Func RejectChk_Clicked() ; uncheck the accept control GUICtrlSetState($g_aidControls[$ACCEPT_CHK], $GUI_UNCHECKED) SubmitBtn_SetState() EndFunc ;==>RejectChk_Clicked #EndRegion #### CheckBox actions ######################## #Region #### GUI Actions ############################# Func CloseProgram() Exit EndFunc ;==>CloseProgram ; create the scrolling guis and their controls that go on each of the tabs Func CreateTabGUIs(ByRef $as_Controls, ByRef $as_ToolTips, $i_ArrayRow, $h_MainGUI) ; create the gui for the controls and add it to the global controls array $g_aidControls[$i_ArrayRow] = GUICreate('', 770, 370, 23, 270, $WS_POPUP, $WS_EX_MDICHILD, $h_MainGUI) GUISetBkColor($COLOR_WHITE) ; create the temp array. This will hold all the controlIDs Local _ $aid_Temp[UBound($as_Controls)][$ARRAYMAX_COLS] = [[0]] ; this is a 0 based array with the same rows as the Local _ $s_Value = '' ; value to give the control Local _ $i_ArrayRowCount = 0 ; keeps track of number of array rows (controls rows added) Local _ ; variables for the control spacing $i_Left = 0, _ ;_ starting left position $i_Rows = 60, _ ;_ initial number of rows $i_Spacing = 60, _ ;_ control padding $i_Top = 18 ;_ initial top position For $i = 0 To UBound($aid_Temp) - 1 ; 0 to 70 rows of controls . if you add more this number will need to change ; get the value to add to the control from the array returned by InputControls_GetProperties() $s_Value = $as_Controls[$i][$i_ArrayRow] ; array is 0 based and has 53 elements filled ; don't create anymore controls if the value is blanks If Not $s_Value Then ExitLoop ; calculate additional spacing $i_Left = (Int($i / $i_Rows)) + $i_Spacing $i_Top = (30.7 * Mod($i, $i_Rows)) ; - 30 ; check if the string is upper case. Strip any none letter characters out for testing ; as digits/punctuation/whitespace will cause StringIsUpper() to return 0. If StringIsUpper(StringRegExpReplace($s_Value, '[^A-Za-z]', '')) Then ; assume its a section ; draw the input and add the controlID to the correct gui array $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateLabel($s_Value, $i_Left - 50, $i_Top, 730, 20, $WS_EX_CLIENTEDGE) GUICtrlSetFont(-1, 8, 800) GUICtrlSetColor(-1, $COLOR_BLACK) GUICtrlSetBkColor(-1, $COLOR_SILVER) ; set the other columns to 0 as there are no other controls being added $aid_Temp[$i][$NA_COL] = 0 $aid_Temp[$i][$NOERROR_COL] = 0 $aid_Temp[$i][$WITHERROR_COL] = 0 $aid_Temp[$i][$REMARKS_COL] = 0 Else ; assume it's a sub ; draw the input $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateInput($s_Value, $i_Left - 50, $i_Top, 155, 20, BitOR($GUI_SS_DEFAULT_INPUT, $ES_READONLY), $WS_EX_TRANSPARENT) ; use an input GUICtrlSetBkColor(-1, $COLOR_WHITE) If $as_ToolTips[$i][$i_ArrayRow] <> '' Then GUICtrlSetTip(-1, $as_ToolTips[$i][$i_ArrayRow], 'Instructions:', $TIP_INFOICON) ;~ $aid_Temp[$i][0] = GUICtrlCreateLabel($s_Value, $i_Left - 50, $i_Top, 155, 40) ; use a label if you want ; draw the N/A check box $aid_Temp[$i][$NA_COL] = GUICtrlCreateCheckbox('', $i_Left + 165, $i_Top, 15, 20) GUICtrlSetOnEvent(-1, 'NaChk_SetState') ; draw the no error check box $aid_Temp[$i][$NOERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 260, $i_Top, 15, 20) GUICtrlSetOnEvent(-1, 'NoErrorChk_SetState') ; draw the with error check box $aid_Temp[$i][$WITHERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 355, $i_Top, 15, 20) GUICtrlSetOnEvent(-1, 'WithErrorChk_SetState') ; draw the edit control $aid_Temp[$i][$REMARKS_COL] = GUICtrlCreateEdit('', $i_Left + 440, $i_Top, 240, 24, BitOR($ES_AUTOVSCROLL, $ES_WANTRETURN)) EndIf $i_ArrayRowCount += 1 ; increase the number of rows added to the array Next ; add scroll bars to the current gui _GUIScrollbars_Generate($g_aidControls[$i_ArrayRow], 0, 2000) ; redim the array to remove any blanks ReDim $aid_Temp[$i_ArrayRowCount][$ARRAYMAX_COLS] ;~ _ArrayDisplay($aid_Temp, 'CreateTabGUIs ($aid_Temp)') ; for debugging ; returns a 0 based array of controlIDs Return $aid_Temp EndFunc ;==>CreateTabGUIs Func Draw_GUI() Local $h_MainGUI = GUICreate('Test Form', 820, 740, -1, -1) GUISetBkColor(0x38A7D2) GUISetOnEvent($GUI_EVENT_CLOSE, 'CloseProgram') GUICtrlCreatePic(@ScriptDir & '\Image.jpg', 690, 15, 120, 120) GUICtrlCreateLabel('Welcome: ' & GetFullName(@UserName), 17, 135, 500, 16) GUICtrlSetColor(-1, $COLOR_WHITE) GUICtrlSetFont(-1, 9, $FW_BOLD) #Region #### Main Menu ########################### Local $id_Menu = GUICtrlCreateMenu('File') GUICtrlCreateMenuItem('Close', $id_Menu) GUICtrlSetOnEvent(-1, 'CloseProgram') GUICtrlCreateMenuItem('Export', $id_Menu) GUICtrlSetOnEvent(-1, 'MainMenu_FileExport') $id_Menu = GUICtrlCreateMenu('Edit') GUICtrlSetState(-1, $GUI_DISABLE) $id_Menu = GUICtrlCreateMenu('Option') GUICtrlSetState(-1, $GUI_DISABLE) $id_Menu = GUICtrlCreateMenu('View') GUICtrlCreateMenuItem('Exit', $id_Menu) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlSetOnEvent(-1, 'MainMenu_ViewExit') $id_Menu = GUICtrlCreateMenu('Help') GUICtrlCreateMenuItem('About', $id_Menu) GUICtrlSetOnEvent(-1, 'MainMenu_HelpAbout') #EndRegion #### Main Menu ########################### #Region #### Employee Info ####################### GUICtrlCreateGroup('Employee Information', 16, 8, 281, 121) GUICtrlCreateLabel('Emp#:', 33, 35, 35, 17) $g_aidControls[$EMPNUMBER_INP] = GUICtrlCreateInput('1234567', 72, 32, 217, 21) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlCreateLabel('Name:', 33, 59, 35, 17) $g_aidControls[$EMPNAME_INP] = GUICtrlCreateInput(GetFullName(@UserName), 72, 56, 217, 21) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlCreateLabel('Process:', 24, 83, 45, 17) $g_aidControls[$EMPPROCESS_INP] = GUICtrlCreateInput('First Process', 72, 80, 217, 21) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlCreateGroup('', -99, -99, 1, 1) #EndRegion #### Employee Info ####################### #Region #### whatever this is #################### GUICtrlCreateLabel('AID:', 25, 170, 25, 17) GUICtrlSetFont(-1, 10, $FW_BOLD) $g_aidControls[$AID_LBL] = GUICtrlCreateLabel('ABZ123456', 60, 170, 90, 17) ; remove this if you don't alter the text GUICtrlSetColor(-1, $COLOR_WHITE) GUICtrlSetFont(-1, 10, $FW_BOLD) #EndRegion #### whatever this is #################### #Region #### Confirmation Checkboxes ############# $g_aidControls[$AGREE_CHK] = GUICtrlCreateCheckbox(' I agree that I have checked the entry(s) above.', 25, 655, 265, 17) GUICtrlSetOnEvent(-1, 'SubmitBtn_SetState') $g_aidControls[$ACCEPT_CHK] = GUICtrlCreateCheckbox(' Accept', 25, 675, 60, 17) GUICtrlSetOnEvent(-1, 'AcceptChk_Clicked') GUICtrlCreateLabel('*', 85, 680, 40, 17) GUICtrlSetColor(-1, $COLOR_RED) GUICtrlSetFont(-1, 10, $FW_BOLD) $g_aidControls[$REJECT_CHK] = GUICtrlCreateCheckbox(' Reject', 25, 695, 60, 17) GUICtrlSetOnEvent(-1, 'RejectChk_Clicked') GUICtrlCreateLabel('*', 85, 695, 40, 17) GUICtrlSetColor(-1, $COLOR_RED) GUICtrlSetFont(-1, 10, $FW_BOLD) #EndRegion #### Confirmation Checkboxes ############# #Region #### Submit Report Group ################# $Group1 = GUICtrlCreateGroup("Submit Report", 600, 647, 200, 65) $g_aidControls[$LAYOUTONE_RDO] = GUICtrlCreateRadio("Layout 1", 615, 665, 70, 17) GUICtrlSetState(-1, $GUI_CHECKED) GUICtrlSetOnEvent(-1, 'Layout1_Selected') GUICtrlCreateRadio("Layout 2", 615, 685, 70, 17) GUICtrlSetOnEvent(-1, 'Layout2_Selected') $g_aidControls[$SUBMIT_BTN] = GUICtrlCreateButton('Submit Report', 690, 667, 100, 33) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlSetOnEvent(-1, 'Report_Submit') GUICtrlCreateGroup("", -99, -99, 1, 1) #EndRegion #### Submit Report Group ################# #Region #### Create Tabs ######################### Draw_Tabs($h_MainGUI) #EndRegion #### Create Tabs ######################### GUISetState(@SW_SHOW, $h_MainGUI) ; show the main gui Sleep(105) ; add a sleep to try and get them to show at the same time GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI]) ; show the first tab gui EndFunc ;==>Draw_GUI ; draw the group on controls that sit above the tab guis Func Draw_TabHeaders() ; this draws the headers over the columns on the tab gui. The values are pulled from the $g_asTabHeaderText array GUICtrlCreateGroup('', 30, 215, 755, 50) GUICtrlCreateLabel($g_asTabHeaderText[$ENTRYS_COL], 50, 232, 80, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER)) GUICtrlCreateLabel($g_asTabHeaderText[$NA_COL], 238, 232, 40, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER)) GUICtrlCreateLabel($g_asTabHeaderText[$NOERROR_COL], 315, 232, 70, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER)) GUICtrlCreateLabel($g_asTabHeaderText[$WITHERROR_COL], 405, 232, 85, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER)) GUICtrlCreateLabel($g_asTabHeaderText[$REMARKS_COL], 590, 232, 90, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER)) EndFunc ;==>Draw_TabHeaders Func Draw_Tabs($h_MainGUI) ; create the main tab control and add it's ID to the control array $g_aidControls[$MAIN_TAB] = GUICtrlCreateTab(20, 190, 780, 455, $WS_EX_MDICHILD) GUICtrlSetOnEvent(-1, 'TabChange') ; do summat on tab change ; declare arrays for the input properties Local $as_Controls = 0 Local $as_ToolTips = 0 ; get the strings that will populate the gui controls from the excel sheet. These will be the "Entry{s}" ; set the range as the first column 'A', to the last column that contains the layout for the guis (currently 'C') ; ### add some error checking for InputControls_GetProperties #### InputControls_GetProperties( _ $as_Controls, _ ; load the array for the input control strings $as_ToolTips, _ ; load the array for the input controls tooltips CellRange_CreateFromInteger($FRONTMATTERGUI + 1, $TABCOUNT)) ; set the cell range to read ; loop to create the required number of tabs, guis and their controls For $i = $FRONTMATTERGUI To $TABCOUNT - 1 ; - 1 because $g_asTabNames is a 0 based array GUICtrlCreateTabItem($g_asTabNames[$i]) ; create the headers for each tab. These are listed in $g_asTabHeaderText Draw_TabHeaders() Switch $i Case $FRONTMATTERGUI ; gui 1 ; create the array containing the controlIDs. returns a 0 based array of controlIDs $aid_FrontMatterGUI = CreateTabGUIs($as_Controls, $as_ToolTips, $i, $h_MainGUI) Case $BODYMATTERGUI ; gui 2 ; create the array containing the controlIDs $aid_BodyMatterGUI = CreateTabGUIs($as_Controls, $as_ToolTips, $i, $h_MainGUI) Case $BACKMATTERGUI ; gui 3 ; create the array containing the controlIDs $aid_BackMatterGUI = CreateTabGUIs($as_Controls, $as_ToolTips, $i, $h_MainGUI) EndSwitch ; switch to the main gui and tab after creating the new guis or else the control creation gets bollocksed Up ; and the tabs or controls are not visible GUISwitch($h_MainGUI, $g_aidControls[$MAIN_TAB]) Next GUICtrlCreateTabItem('') EndFunc ;==>Draw_Tabs Func TabChange() Switch GUICtrlRead($g_aidControls[$MAIN_TAB]) Case $FRONTMATTERGUI ; Front Matter tab GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI]) GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI]) GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI]) Case $BODYMATTERGUI ; body Matter tab GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI]) GUISetState(@SW_SHOW, $g_aidControls[$BODYMATTERGUI]) GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI]) Case $BACKMATTERGUI ; back matter tab GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI]) GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI]) GUISetState(@SW_SHOW, $g_aidControls[$BACKMATTERGUI]) EndSwitch EndFunc ;==>TabChange #EndRegion #### GUI Actions ############################# #Region #### Main Menu Action ######################## Func MainMenu_FileExport() ; add commands later EndFunc ;==>MainMenu_FileExport Func MainMenu_HelpAbout() ; add commands later EndFunc ;==>MainMenu_HelpAbout Func MainMenu_ViewExit() ; add commands later EndFunc ;==>MainMenu_ViewExit #EndRegion #### Main Menu Action ######################## #Region #### Spreadsheet Actions ##################### ; create an array of values that are read from the controls on the tabs GUI ; these will be written to the spreadsheet later Func ArrayRow_SetValues(ByRef $aid_ControlID, $i_Row) Local $v_Value = '' Local $s_BoldCells = '' ; loop through the array columns For $i = $ENTRYS_COL To $REMARKS_COL ; get the value from the supplied array $v_Value = $aid_ControlID[$i_Row][$i] ; check if the value is a controlID If IsNumber($v_Value) Then If Not $v_Value Then ; it's 0 so no control to read $v_Value = '' ; set a blank string Else $v_Value = GUICtrlRead($aid_ControlID[$i_Row][$i]) ; assume it's a header or heading indentifier string If StringIsUpper(StringRegExpReplace($v_Value, '[^A-Za-z]', '')) Then $g_sSectionCells &= 'A' & $i_Row + 1 & ',' If StringInStr($v_Value, $g_asTabHeaderText[0]) Then $g_sHeaderCells &= 'A' & $i_Row + 1 & ',' EndIf Else ; assume it's a tab name $aid_ControlID[$i_Row][$i] = $v_Value If StringInStr($v_Value, $g_asTabHeaderText[0]) Then $g_sHeaderCells &= 'A' & $i_Row + 1 & ',' If StringInStr(_ArrayToString($g_asTabNames, '|'), $v_Value) Then $g_sTabNameCells &= 'A' & $i_Row + 1 & ',' ExitLoop EndIf ; do different things based on the specified control If $i = $ENTRYS_COL Or $i = $REMARKS_COL Then ; these are input controls ; write the value to the array $aid_ControlID[$i_Row][$i] = $v_Value Else ; these are checkbox controls ; get the checked state of the control $aid_ControlID[$i_Row][$i] = CheckBox_ReturnStringState($aid_ControlID[$i_Row][$i]) EndIf Next EndFunc ;==>ArrayRow_SetValues ; create an excel range from 2 numbers Func CellRange_CreateFromInteger($i_Start, $i_End = 0) ; assumes there is only one number to convert to a range If Not $i_End Then Return _Excel_ColumnToLetter($i_Start) ; convert any numbers to excel column letters Return _Excel_ColumnToLetter($i_Start) & ':' & _Excel_ColumnToLetter($i_End) EndFunc ;==>CellRange_CreateFromInteger Func CellRange_CreateFromString($s_Range, $v_AddRange) ; if $v_AddRange is a number then convert the number to a letter for the range If IsNumber($v_AddRange) Then $v_AddRange = _Excel_ColumnToLetter($v_AddRange) ; trim any leading\trailing comma symbols before splitting $s_Range = StringRegExpReplace($s_Range, "\A[,]+|[,]+\Z", "") ; split the comma delimited range Local $as_Split = StringSplit($s_Range, ',') ; loop through the array For $i = 1 To $as_Split[0] ; update the array element $as_Split[$i] = $as_Split[$i] & ':' & StringRegExpReplace($as_Split[$i], '[[:alpha:]]+', $v_AddRange) Next ; return a string that will be used as a cell range Return _ArrayToString($as_Split, ',', 1) EndFunc ;==>CellRange_CreateFromString Func CellRange_ResetGlobalValues() $g_sHeaderCells = '' $g_sSectionCells = '' $g_sTabNameCells = '' EndFunc ;==>CellRange_ResetGlobalValues Func GetEmployeeInfo() Local $as_Employee[6] = [ _ ; 0 based array 'Emp# : ' & GUICtrlRead($g_aidControls[$EMPNUMBER_INP]), _ 'Name : ' & GUICtrlRead($g_aidControls[$EMPNAME_INP]), _ 'Process: ' & GUICtrlRead($g_aidControls[$EMPPROCESS_INP]), _ 'AID : ' & GUICtrlRead($g_aidControls[$AID_LBL]), _ 'I agree that I have checked the entry(s) above.', _ 'Accepted'] ; update the last array element if the reject checkbox is checked If GUICtrlRead($g_aidControls[$REJECT_CHK]) = $GUI_CHECKED Then $as_Employee[5] = 'Rejected' ; return thte array for writing to the spreadsheet Return $as_Employee EndFunc ;==>GetEmployeeInfo Func InputControls_GetProperties(ByRef $as_Controls, ByRef $as_ToolTips, $s_Range = 'A') ; set the path to the file that has the values for the inputs Local $s_ExcelFile = @ScriptDir & '\Sample.xlsx' ; #### add some error checking for these excel functions #### Local $o_Excel = _Excel_Open(False) Local $o_WorkBook = _Excel_BookOpen($o_Excel, $s_ExcelFile) $as_Controls = _Excel_RangeRead($o_WorkBook, Default, $o_WorkBook.Sheets('Controls').Usedrange.Columns($s_Range), 2) $as_ToolTips = _Excel_RangeRead($o_WorkBook, Default, $o_WorkBook.Sheets('ToolTips').Usedrange.Columns($s_Range), 2) _Excel_Close($o_Excel) EndFunc ;==>InputControls_GetProperties ; go through the tabs and controls and write the values to a spreadsheet Func WriteArrayValuesToSpreadsheet($o_WorkBook, $i_Layout = Report_GetLayout()) Local _ ; $aa_ControlArrays is an array of arrays. The arrays have the controlIDs for the relevant tab gui controls $aa_ControlArrays[$TABCOUNT + 1] = [$TABCOUNT, $aid_FrontMatterGUI, $aid_BodyMatterGUI, $aid_BackMatterGUI], _ $as_Values = 0, _ ; the array that will hold the values of the gui controls $as_Temp = 0 ;_ a temp array for modding ; set the number of loop to perform when writing. ; If layout 1 is used, there will be a seperate spreadsheet for each tab so 3 loops ; if layout 2 is used all the tabs are written to one spreadsheet so only 1 loop is needed Local _ $i_Total = $aa_ControlArrays[0], _ ;_ set as default layout 1 and loop through the all the arrays $i_Concatenated = 0 ;_ Set the state of the array being processed 0 = multiple 1 = 1 concatenated array ; Change if the layout is 2 and just loop through the concatenated array If $i_Layout > 1 Then $i_Total = 1 ; layout 2 selected #Region #### only needed for cell outside borders if not interested the code can be deleted ### Local _ ; Excel constants not in ExcelConstants.au3 $i_xlAutomatic = -4105, _ $i_xlContinuous = 1, _ $i_xlThin = 2, _ $i_xlEdgeLeft = 7, _ $i_xlEdgeTop = 8, _ $i_xlEdgeBottom = 9, _ $i_xlEdgeRight = 10 #EndRegion #### only needed for cell outside borders if not interested the code can be deleted ### ; loop through the arrays For $i = 1 To $i_Total ; on each loop copy the nested array to a temp one, this will be filled with the values of the controls $as_Values = $aa_ControlArrays[$i] ; set a starting array _ArrayInsert($as_Values, 0, _ArrayToString($g_asTabHeaderText, '|')) ; add the headers to each array #Region #### This determines which array(s) to use for the write #### If $i_Layout = 2 Then _ArrayInsert($as_Values, 0, $g_asTabNames[0]) ; add the first tab name to the array ; loop through the remaining arrays For $j = 2 To $TABCOUNT ; $TABCOUNT = 3 $as_Temp = $aa_ControlArrays[$j] ; copy to a temp array so we can add the tab names and headet text _ArrayInsert($as_Temp, 0, $g_asTabNames[$j - 1]) ; add the next tab name to the array _ArrayInsert($as_Temp, 1, _ArrayToString($g_asTabHeaderText, '|')) ; add the next header text ; joining them $as_Values _ArrayConcatenate($as_Values, $as_Temp) Next EndIf #EndRegion #### This determines which array(s) to use for the write #### ; loop through the temp array For $j = 0 To UBound($as_Values) - 1 ; update the array row with the control values ArrayRow_SetValues($as_Values, $j) Next ; activate the sheet $o_WorkBook.Sheets($i).Activate ; write the array to an excel spreadsheet _Excel_RangeWrite($o_WorkBook, $i, $as_Values) If @error Then Return SetError(@error, @extended, 'Error Writing ' & $g_asTabNames[$i - 1] & ' to sheet') ; write the employee info If $i = 1 Then _Excel_RangeWrite($o_WorkBook, $i, GetEmployeeInfo(), CellRange_CreateFromInteger($ARRAYMAX_COLS + 2) & '1') If @error Then Return SetError(@error, @extended, 'Error Writing Employee info to sheet') ; adjust the sheet properties whilst it's active With $o_WorkBook.ActiveSheet If $g_iMultipleSheets Then ; we are writing multiple sheets ; set the sheet name .Name = $g_asTabNames[$i - 1] EndIf ; set the column letters that will auto size to fit the text .Columns(CellRange_CreateFromInteger($ENTRYS_COL + 1, $ARRAYMAX_COLS + 2)).AutoFit ; this is where the cells that have the tab names in get nodified #Region #### Tab Name Cells #### If $g_sTabNameCells Then $g_sTabNameCells = CellRange_CreateFromString($g_sTabNameCells, $ARRAYMAX_COLS) ; set the font weight as bold .Range($g_sTabNameCells).Font.FontStyle = 'Bold' ; set the tab name cells back colour .Range($g_sTabNameCells).Interior.Color = 0xBFBFBF ; merge the cells .Range($g_sTabNameCells).Merge ; centre align the cells .Range($g_sTabNameCells).HorizontalAlignment = $xlCenter EndIf #EndRegion #### Tab Name Cells #### ; this is where the cells that have Entry(s), N/A, No Error etc get modified #Region #### Header Cells #### If $g_sHeaderCells Then $g_sHeaderCells = CellRange_CreateFromString($g_sHeaderCells, $ARRAYMAX_COLS) ; set the font weight as bold .Range($g_sHeaderCells).Font.FontStyle = 'Bold' ; centre align the cells .Range($g_sHeaderCells).HorizontalAlignment = $xlCenter ; set the header cells back colour .Range($g_sHeaderCells).Interior.Color = 0xFFE6CC EndIf #EndRegion #### Header Cells #### ; this is where the cells that have the section text like CATEGORY, HISTORY DATES etc get modified #Region #### Section Cells #### If $g_sSectionCells Then $g_sSectionCells = CellRange_CreateFromString($g_sSectionCells, $ARRAYMAX_COLS) ; set the font weight as bold .Range($g_sSectionCells).Font.FontStyle = 'Bold' ; set the header cells back colour .Range($g_sSectionCells).Interior.Color = 0xDAEFE2 ; add outside borders .Range($g_sSectionCells).Borders($i_xlEdgeTop).LineStyle = $i_xlContinuous .Range($g_sSectionCells).Borders($i_xlEdgeLeft).LineStyle = $i_xlContinuous .Range($g_sSectionCells).Borders($i_xlEdgeRight).LineStyle = $i_xlContinuous .Range($g_sSectionCells).Borders($i_xlEdgeBottom).LineStyle = $i_xlContinuous EndIf #EndRegion #### Section Cells #### EndWith CellRange_ResetGlobalValues() Sleep(20) Next $o_WorkBook.Sheets(1).Activate ; activate the first sheet CellRange_ResetGlobalValues() EndFunc ;==>WriteArrayValuesToSpreadsheet #EndRegion #### Spreadsheet Actions ##################### #Region #### Submit Report Actions ################### Func Layout1_Selected() $g_iMultipleSheets = 1 EndFunc ;==>Layout1_Selected Func Layout2_Selected() $g_iMultipleSheets = 0 EndFunc ;==>Layout2_Selected ; gets the currently selected layout type for the report Func Report_GetLayout() ; return the state of the layout 1 radio button If GUICtrlRead($g_aidControls[$LAYOUTONE_RDO]) = $GUI_CHECKED Then Return 1 Return 2 EndFunc ;==>Report_GetLayout ; submit your report Func Report_Submit() SplashTextOn("submit", "Submitting report...", 300, 50, -1, -1, $DLG_NOTITLE + $DLG_TEXTVCENTER, '', '', $FW_BOLD) ;~ Local $o_WorkBook = '' ; open an excel instance Local $o_Excel = _Excel_Open(False) If @error Then Return SetError(1, _ MsgBox($MB_SYSTEMMODAL, _ '_Excel_Open', _ 'Error creating the Excel application object' & @CRLF & '@error = ' & @error & ', @extended = ' & @extended), 0) ; set the number of sheets to add when opening a new workbook Local $iSheets = $TABCOUNT If Not $g_iMultipleSheets Then $iSheets = 1 ; open a new excel book Local $o_WorkBook = _Excel_BookNew($o_Excel, $iSheets) ; $TABCOUNT is the number of tabs in your program and so the number of sheets we create for submitting If @error Then Return SetError(2, _ MsgBox($MB_SYSTEMMODAL, _ '_Excel_BookNew', _ 'Error creating new workbook' & @CRLF & '@error = ' & @error & ', @extended = ' & @extended), _Excel_Close($o_Excel)) ;~ ; this will read the control values to an array and write the array to an excel file WriteArrayValuesToSpreadsheet($o_WorkBook) If Not @error Then ; create a file path based on time. If you want to keep overwriting the files the create a definite file name ; and use the commented line Local $s_FilePath = @ScriptDir & '\Report_' & @YEAR & '-' & @MON & '-' & @MDAY & '_' & @HOUR & @MIN & @SEC & '.xlsx' ; ; save the created report and overwrite the previous ;~ _Excel_BookSaveAs($o_WorkBook, $s_FilePath, $xlWorkbookDefault, True) ; save the created report to a new file _Excel_BookSaveAs($o_WorkBook, $s_FilePath, $xlWorkbookDefault) If @error Then MsgBox($MB_SYSTEMMODAL, _ "_Excel_BookSaveAs", _ "Error saving workbook to '" & $s_FilePath & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf _Excel_Close($o_Excel) ; update the splash text ControlSetText("submit", "", "Static1", 'Opening report...') ; open the report, remove if you don't need to ShellExecute($s_FilePath) SplashOff() EndFunc ;==>Report_Submit ; enables or disables the submit report button Func SubmitBtn_SetState() If BitAND( _ ; check if two checkxoes are checked GUICtrlRead($g_aidControls[$AGREE_CHK]), _ ; if the agree checkbox GUICtrlRead($g_aidControls[$ACCEPT_CHK]), _ ; and the accept checkbox $GUI_CHECKED) = $GUI_CHECKED _ ; are checked Or _ ; they may not both be checked so check the reject checkbox BitAND( _ GUICtrlRead($g_aidControls[$AGREE_CHK]), _ ; if the agree checkbox GUICtrlRead($g_aidControls[$REJECT_CHK]), _ ; and the reject checkbox $GUI_CHECKED) = $GUI_CHECKED _ ; are checked Then ; enable the button GUICtrlSetState($g_aidControls[$SUBMIT_BTN], $GUI_ENABLE) Else ; disable the button GUICtrlSetState($g_aidControls[$SUBMIT_BTN], $GUI_DISABLE) EndIf EndFunc ;==>SubmitBtn_SetState #EndRegion #### Submit Report Actions ################### Sample.xlsx KickStarter15 1 Link to comment Share on other sites More sharing options...
KickStarter15 Posted January 17, 2018 Author Share Posted January 17, 2018 @benners, Sorry for the late response I just came back from our vacation trip for 6 days. Anyway, It is indeed working perfectly benners as desired and very satisfied with it. Thank you so much benners for this, I really don't know how to manage this coding if your not here, it's so complicated and beyond my skills (for now ). I'll keep it up and someday be like you. Thank you, thank you, thank you so much!!! I only have this one last issue. Is it normal that when running this script, it took a little more seconds before it will open-up totally the interface? Just asking, maybe because if it will be converted to .exe, the program might take a while to open. Programming is "To make it so simple that there are obviously no deficiencies" or "To make it so complicated that there are no obvious deficiencies" by C.A.R. Hoare. Link to comment Share on other sites More sharing options...
benners Posted January 17, 2018 Share Posted January 17, 2018 Well it is doing a lot of stuff to get the controls and tooltips. If you comment out the line below it loads faster. Draw_Tabs($h_MainGUI) The main slow down points will be the excel reads and the response from the full name query. When compiled it will run at the same speed. The time needed for the extra steps like copying the script to memory and using the interpreter are negligible. To give the user some feedback you could add a splash screen like the one used in the Report_Submit() function, place it in the Draw_GUI() function. I have made some more tweaks like altering\renaming functions, cell colouring in the report etc. Not much else to do. expandcollapse popup#Region #### Includes ################################ #include <ButtonConstants.au3> #include <ColorConstants.au3> #include <EditConstants.au3> #include <Excel.au3> #include <FontConstants.au3> #include <GUIConstantsEx.au3> #include <GUIScrollbars_Ex.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <Array.au3> #EndRegion #### Includes ################################ Opt('GUIOnEventMode', 1) HotKeySet('{ESC}', 'CloseProgram') #Region #### Globals ################################# ; these are integers that relate to where the controliDs are located in $g_aidControls. ; The control ID can be retrieved like so, $g_aidControls[$EMPPROCESS_INP] Global Enum _ ; values are 0 to 14 $FRONTMATTERGUI, _ ;_ gui 1 $BODYMATTERGUI, _ ;_ gui 2 $BACKMATTERGUI, _ ;_ gui 3 $TABCOUNT, _ ; #### IMPORTANT #### this must be after the last gui. it signifies how many tabs there will be and is used in other functions $EMPNUMBER_INP, _ ;_ employee number input id $EMPNAME_INP, _ ;_ employee name input id $EMPPROCESS_INP, _ ;_ employee process input id $AID_LBL, _ ;_ some label I don't know if you use id $AGREE_CHK, _ ;_ I agree checkbox id $ACCEPT_CHK, _ ;_ accept checkbox id $REJECT_CHK, _ ;_ reject checkbox id $LAYOUTONE_RDO, _ ;_ radio button for layout 1 selection $SUBMIT_BTN, _ ;_ submit button id $MAIN_TAB, _ ;_ main tab id $ARRAYMAX_ROWS ;_ number of rows to give the array its 0 based ; create the array to hold the remaining controlIDs. These can be referenced ; later when writing their values to the excel sheet Global $g_aidControls[$ARRAYMAX_ROWS] ; create the arrays to hold the all controlIDs that will be created on the tab guis by CreateTabGUIs() called by Draw_Tabs() function ; These can be referenced later when writing their values to the excel sheet Global _ $aid_FrontMatterGUI = '', _ $aid_BodyMatterGUI = '', _ $aid_BackMatterGUI = '' ; these are the tab names and will be used as the spreadsheet names when the report is submitted ; these have the same value as the Global Enum $FRONTMATTERGUI to $BACKMATTERGUI and are used in varios functions Global $g_asTabNames[$TABCOUNT] = [ _ ; values are 0 to 2 'Front Matter', _ 'Body Matter', _ 'Back Matter'] ; these are integers that relate to where the rows are for each set of controls in the $aid_FrontMatterGUI array etc ; they are also the same values as their matching strings index in the $g_asTabHeaderText array Global Enum _ ; values are 0 to 8 $ENTRYS_COL, _ $NA_COL, _ $NOERROR_COL, _ $WITHERROR_COL, _ $REMARKS_COL, _ $ARRAYMAX_COLS, _ $SPACERCELL, _ $EMPLOYEESTART, _ $EMPLOYEEFINISH ; this is the array that holds the tab header text strings. These are written to the first row of the array that matches the tab and gui ; I.E - ; Tab = 'Front Matter' ; Array with the controlIDs = $aid_FrontMatterGUI Global $g_asTabHeaderText[$ARRAYMAX_COLS] = [ _ ; 0 based array 'Entry(s)', _ 'N/A', _ 'No Error', _ 'With Error', _ 'Remarks'] ; this keeps track of if the report layout style is 1 or 2 Global $g_iMultipleSheets = 1 ; these keep track of the cells that need tweaking in the report Global _ $g_sHeaderCells = '', _ ;_ These are the strings above the inline controls such as Entry(s) N/A etc $g_sSectionCells = '', _ ;_ Sections, these are upper case strings like CATEGORY, HISTORY DATES $g_sTabNameCells = '' ;_ tab names #EndRegion #### Globals ################################# Draw_GUI() While 1 Sleep(50) WEnd ; this works as long as the Full Name is entered in the users acoount properties page Func GetFullName($sUserName) Local $objWMIService = ObjGet('winmgmts:\\localhost\root\CIMV2') Local $colItems = $objWMIService.ExecQuery("SELECT * FROM Win32_UserAccount WHERE Name = '" & $sUserName & "'", "WQL", 0x10 + 0x20) If Not IsObj($colItems) Then Return SetError(1, 0, '') For $objItem In $colItems Return $objItem.FullName Next EndFunc ;==>GetFullName #Region #### CheckBox actions ######################## ; get the state of the checkbox Func CheckBox_ReturnStateAsString($id_ControlID) If GUICtrlRead($id_ControlID) = $GUI_CHECKED Then Return 'Checked' Return '' ;~ Return 'Unchecked' ; use this if you want it to return unchecked instead of blank EndFunc ;==>CheckBox_ReturnStateAsString ; inline check boxes Func NaChk_SetState() GUICtrlSetState(@GUI_CtrlId + 1, $GUI_UNCHECKED) ; uncheck the No Error checkbox GUICtrlSetState(@GUI_CtrlId + 2, $GUI_UNCHECKED) ; uncheck the With Error checkbox EndFunc ;==>NaChk_SetState Func NoErrorChk_SetState() GUICtrlSetState(@GUI_CtrlId - 1, $GUI_UNCHECKED) ; uncheck the NA checkbox GUICtrlSetState(@GUI_CtrlId + 1, $GUI_UNCHECKED) ; uncheck the With Error checkbox EndFunc ;==>NoErrorChk_SetState Func WithErrorChk_SetState() GUICtrlSetState(@GUI_CtrlId - 1, $GUI_UNCHECKED) ; uncheck the No Error checkbox GUICtrlSetState(@GUI_CtrlId - 2, $GUI_UNCHECKED) ; uncheck the NA checkbox EndFunc ;==>WithErrorChk_SetState ; confirmation checkboxes Func AcceptChk_Clicked() ; accept checkbox ; uncheck the reject control GUICtrlSetState($g_aidControls[$REJECT_CHK], $GUI_UNCHECKED) ; check if the submit button is to be enabled SubmitBtn_SetState() EndFunc ;==>AcceptChk_Clicked Func AgreeChk_Clicked() ; I agree checkbox Local $i_State = $GUI_DISABLE If GUICtrlRead(@GUI_CtrlId) = $GUI_CHECKED Then $i_State = $GUI_ENABLE ; set the state of the accept\reject checkboxes ConfirmationChks_SetState($i_State) ; check if the submit button is to be enabled SubmitBtn_SetState() EndFunc ;==>AgreeChk_Clicked Func ConfirmationChks_SetState($i_State, $i_SetAll = 0) ; reset the i agree checkbox so people have to click it each time to submit a report If $i_SetAll Then GUICtrlSetState($g_aidControls[$AGREE_CHK], $GUI_UNCHECKED) ; update the state if we are disabling to uncheck the box as well as disabling If $i_State = $GUI_DISABLE Then $i_State += $GUI_UNCHECKED ; set the state of the accept reject boxes GUICtrlSetState($g_aidControls[$ACCEPT_CHK], $i_State) GUICtrlSetState($g_aidControls[$REJECT_CHK], $i_State) EndFunc ;==>ConfirmationChks_SetState Func RejectChk_Clicked() ; reject checkbox ; uncheck the accept control GUICtrlSetState($g_aidControls[$ACCEPT_CHK], $GUI_UNCHECKED) SubmitBtn_SetState() EndFunc ;==>RejectChk_Clicked #EndRegion #### CheckBox actions ######################## #Region #### GUI Actions ############################# Func CloseProgram() Exit EndFunc ;==>CloseProgram ; create the scrolling guis and their controls that go on each of the tabs Func CreateTabGUIs(ByRef $as_Controls, ByRef $as_ToolTips, $i_ArrayRow, $h_MainGUI) ; create the gui for the controls and add it to the global controls array $g_aidControls[$i_ArrayRow] = GUICreate('', 770, 370, 23, 270, $WS_POPUP, $WS_EX_MDICHILD, $h_MainGUI) GUISetBkColor($COLOR_WHITE) Local $s_Value = '' ; value to give the control Local _ ; variables for the control spacing $i_ControlCount = UBound($as_Controls), _ ;_ number of rows in the array $i_Left = 0, _ ;_ starting left position $i_Spacing = 60, _ ;_ control padding $i_Top = 18 ;_ initial top position ; create the temp array. This will hold all the controlIDs Local $aid_Temp[$i_ControlCount][$ARRAYMAX_COLS] = [[0]] ; this is a 0 based array with the same rows as the controls array For $i = 0 To $i_ControlCount - 1 ; 0 to number of controls ; get the value to add to the control from the array returned by GUIControls_GetProperties() $s_Value = $as_Controls[$i][$i_ArrayRow] ; array is 0 based ; don't create anymore controls if the value is blank If Not $s_Value Then ExitLoop ; calculate additional spacing $i_Left = (Int($i / $i_ControlCount)) + $i_Spacing $i_Top = (30.7 * Mod($i, $i_ControlCount)) ; ; check if the string is upper case. Strip any none letter characters out for testing ; as digits/punctuation/whitespace will cause StringIsUpper() to return 0. If StringIsUpper(StringRegExpReplace($s_Value, '[^A-Za-z]', '')) Then ; assume its a section ; draw the input and add the controlID to the correct gui array $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateLabel($s_Value, $i_Left - 50, $i_Top, 730, 20, $WS_EX_CLIENTEDGE) GUICtrlSetFont(-1, 8, 800) GUICtrlSetColor(-1, $COLOR_BLACK) GUICtrlSetBkColor(-1, $COLOR_SILVER) ; set the other columns to 0 as there are no other controls being added $aid_Temp[$i][$NA_COL] = 0 $aid_Temp[$i][$NOERROR_COL] = 0 $aid_Temp[$i][$WITHERROR_COL] = 0 $aid_Temp[$i][$REMARKS_COL] = 0 Else ; assume it's a sub ; draw the input $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateInput($s_Value, $i_Left - 50, $i_Top, 155, 20, BitOR($GUI_SS_DEFAULT_INPUT, $ES_READONLY), $WS_EX_TRANSPARENT) ; use an input GUICtrlSetBkColor(-1, $COLOR_WHITE) If $as_ToolTips[$i][$i_ArrayRow] <> '' Then GUICtrlSetTip(-1, $as_ToolTips[$i][$i_ArrayRow], 'Instructions:', $TIP_INFOICON) ;~ $aid_Temp[$i][0] = GUICtrlCreateLabel($s_Value, $i_Left - 50, $i_Top, 155, 40) ; use a label if you want ; draw the N/A check box $aid_Temp[$i][$NA_COL] = GUICtrlCreateCheckbox('', $i_Left + 165, $i_Top, 15, 20) GUICtrlSetOnEvent(-1, 'NaChk_SetState') ; draw the no error check box $aid_Temp[$i][$NOERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 260, $i_Top, 15, 20) GUICtrlSetOnEvent(-1, 'NoErrorChk_SetState') ; draw the with error check box $aid_Temp[$i][$WITHERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 355, $i_Top, 15, 20) GUICtrlSetOnEvent(-1, 'WithErrorChk_SetState') ; draw the edit control $aid_Temp[$i][$REMARKS_COL] = GUICtrlCreateEdit('', $i_Left + 440, $i_Top, 240, 24, BitOR($ES_AUTOVSCROLL, $ES_WANTRETURN)) EndIf Next ; add scroll bars to the current gui and set the maximum amout tne gui will scroll _GUIScrollbars_Generate($g_aidControls[$i_ArrayRow], 0, $i_Top + 30) ;~ _ArrayDisplay($aid_Temp, 'CreateTabGUIs ($aid_Temp)') ; for debugging ; returns a 0 based array of controlIDs with any blank rows trimmed ; same as Redim but don't need to keep a row count (maybe slower?) Return Array_DeleteEmptyRows($aid_Temp) EndFunc ;==>CreateTabGUIs Func Draw_GUI() SplashTextOn("startup", "Loading...", 300, 50, -1, -1, $DLG_NOTITLE + $DLG_TEXTVCENTER, '', '', $FW_BOLD) Local $h_MainGUI = GUICreate('Test Form', 820, 760, -1, -1) GUISetBkColor(0x38A7D2) GUISetOnEvent($GUI_EVENT_CLOSE, 'CloseProgram') GUICtrlCreatePic(@ScriptDir & '\Image.jpg', 690, 15, 120, 120) GUICtrlCreateLabel('Welcome:', 25, 130, 60, 16) GUICtrlSetFont(-1, 9, $FW_BOLD) GUICtrlCreateLabel(GetFullName(@UserName), 90, 130, 250, 16) GUICtrlSetColor(-1, $COLOR_WHITE) GUICtrlSetFont(-1, 9, $FW_BOLD) #Region #### Main Menu ########################### Local $id_Menu = GUICtrlCreateMenu('File') GUICtrlCreateMenuItem('Close', $id_Menu) GUICtrlSetOnEvent(-1, 'CloseProgram') GUICtrlCreateMenuItem('Export', $id_Menu) GUICtrlSetOnEvent(-1, 'MainMenu_FileExport') $id_Menu = GUICtrlCreateMenu('Edit') GUICtrlSetState(-1, $GUI_DISABLE) $id_Menu = GUICtrlCreateMenu('Option') GUICtrlSetState(-1, $GUI_DISABLE) $id_Menu = GUICtrlCreateMenu('View') GUICtrlCreateMenuItem('Exit', $id_Menu) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlSetOnEvent(-1, 'MainMenu_ViewExit') $id_Menu = GUICtrlCreateMenu('Help') GUICtrlCreateMenuItem('About', $id_Menu) GUICtrlSetOnEvent(-1, 'MainMenu_HelpAbout') #EndRegion #### Main Menu ########################### #Region #### Employee Info ####################### GUICtrlCreateGroup('Employee Information', 15, 15, 290, 105) GUICtrlCreateLabel('Name:', 24, 40, 35, 17, $SS_RIGHT) $g_aidControls[$EMPNAME_INP] = GUICtrlCreateInput(GetFullName(@UserName), 75, 37, 217, 21) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlCreateLabel('Number:', 24, 64, 45, 17, $SS_RIGHT) $g_aidControls[$EMPNUMBER_INP] = GUICtrlCreateInput('1234567', 75, 61, 217, 21) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlCreateLabel('Process:', 24, 88, 45, 17, $SS_RIGHT) $g_aidControls[$EMPPROCESS_INP] = GUICtrlCreateInput('First Process', 75, 85, 217, 21) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlCreateGroup('', -99, -99, 1, 1) #EndRegion #### Employee Info ####################### #Region #### whatever this is #################### GUICtrlCreateLabel('AID:', 25, 170, 25, 17) GUICtrlSetFont(-1, 10, $FW_BOLD) $g_aidControls[$AID_LBL] = GUICtrlCreateLabel('ABZ123456', 60, 170, 90, 17) ; remove this if you don't alter the text GUICtrlSetColor(-1, $COLOR_WHITE) GUICtrlSetFont(-1, 10, $FW_BOLD) #EndRegion #### whatever this is #################### #Region #### Create Tabs ######################### Draw_Tabs($h_MainGUI) #EndRegion #### Create Tabs ######################### #Region #### Confirmation Checkboxes ############# GUICtrlCreateGroup("Confirmations", 20, 655, 280, 65) $g_aidControls[$AGREE_CHK] = GUICtrlCreateCheckbox(' I agree that I have checked the entry(s) above.', 30, 672, 265, 17) GUICtrlSetOnEvent(-1, 'AgreeChk_Clicked') $g_aidControls[$ACCEPT_CHK] = GUICtrlCreateCheckbox(' Accept', 50, 693, 65, 17) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlSetOnEvent(-1, 'AcceptChk_Clicked') GUICtrlCreateLabel('*', 115, 693, 10, 17) GUICtrlSetColor(-1, $COLOR_RED) GUICtrlSetFont(-1, 10, $FW_BOLD) $g_aidControls[$REJECT_CHK] = GUICtrlCreateCheckbox(' Reject', 130, 693, 65, 17) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlSetOnEvent(-1, 'RejectChk_Clicked') GUICtrlCreateLabel('*', 195, 693, 10, 17) GUICtrlSetColor(-1, $COLOR_RED) GUICtrlSetFont(-1, 10, $FW_BOLD) GUICtrlCreateGroup("", -99, -99, 1, 1) #EndRegion #### Confirmation Checkboxes ############# #Region #### Submit Report Group ################# GUICtrlCreateGroup("Submit Report", 600, 655, 200, 65) $g_aidControls[$LAYOUTONE_RDO] = GUICtrlCreateRadio("Layout 1", 615, 672, 70, 17) GUICtrlSetState(-1, $GUI_CHECKED) GUICtrlSetOnEvent(-1, 'Layout1_Selected') GUICtrlCreateRadio("Layout 2", 615, 693, 70, 17) GUICtrlSetOnEvent(-1, 'Layout2_Selected') $g_aidControls[$SUBMIT_BTN] = GUICtrlCreateButton('Submit Report', 690, 674, 100, 33) GUICtrlSetState(-1, $GUI_DISABLE) GUICtrlSetOnEvent(-1, 'Report_Submit') GUICtrlCreateGroup("", -99, -99, 1, 1) #EndRegion #### Submit Report Group ################# SplashOff() GUISetState(@SW_SHOW, $h_MainGUI) ; show the main gui Sleep(90) ; add a sleep to try and get them to show at the same time GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI]) ; show the first tab gui EndFunc ;==>Draw_GUI ; draw the group on controls that sit above the tab guis Func Draw_TabHeaders() ; this draws the headers over the columns on the tab gui. The values are pulled from the $g_asTabHeaderText array GUICtrlCreateGroup('', 30, 215, 760, 50) GUICtrlCreateLabel($g_asTabHeaderText[$ENTRYS_COL], 50, 232, 80, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER)) GUICtrlCreateLabel($g_asTabHeaderText[$NA_COL], 238, 232, 40, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER)) GUICtrlCreateLabel($g_asTabHeaderText[$NOERROR_COL], 315, 232, 70, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER)) GUICtrlCreateLabel($g_asTabHeaderText[$WITHERROR_COL], 405, 232, 85, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER)) GUICtrlCreateLabel($g_asTabHeaderText[$REMARKS_COL], 590, 232, 90, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER)) EndFunc ;==>Draw_TabHeaders Func Draw_Tabs($h_MainGUI) ; create the main tab control and add it's ID to the control array $g_aidControls[$MAIN_TAB] = GUICtrlCreateTab(20, 190, 780, 455, $WS_EX_MDICHILD) GUICtrlSetOnEvent(-1, 'TabChange') ; do summat on tab change ; create a label to hide the unused tab space GUICtrlCreateLabel('', 230, 188, 565570, 20) GUICtrlSetBkColor(-1, 0x38A7D2) ; declare arrays for the input properties Local $as_Controls = 0 Local $as_ToolTips = 0 ; get the strings that will populate the gui controls from the excel sheet. These will be the "Entry{s}" ; set the range as the first column 'A', to the last column that contains the layout for the guis (currently 'C') ; ### add some error checking for GUIControls_GetProperties #### GUIControls_GetProperties( _ $as_Controls, _ ; load the array for the input control strings $as_ToolTips, _ ; load the array for the input controls tooltips CellRange_Create($FRONTMATTERGUI + 1, '', $TABCOUNT)) ; set the cell range to read ; loop to create the required number of tabs, guis and their controls For $i = $FRONTMATTERGUI To $TABCOUNT - 1 ; - 1 because $g_asTabNames is a 0 based array so 0 to 2 GUICtrlCreateTabItem($g_asTabNames[$i]) ; create the headers for each tab. These are listed in $g_asTabHeaderText Draw_TabHeaders() Switch $i Case $FRONTMATTERGUI ; gui 1 ; create the array containing the controlIDs. returns a 0 based array of controlIDs $aid_FrontMatterGUI = CreateTabGUIs($as_Controls, $as_ToolTips, $i, $h_MainGUI) Case $BODYMATTERGUI ; gui 2 ; create the array containing the controlIDs $aid_BodyMatterGUI = CreateTabGUIs($as_Controls, $as_ToolTips, $i, $h_MainGUI) Case $BACKMATTERGUI ; gui 3 ; create the array containing the controlIDs $aid_BackMatterGUI = CreateTabGUIs($as_Controls, $as_ToolTips, $i, $h_MainGUI) EndSwitch ; switch to the main gui and tab after creating the new guis or else the control creation gets bollocksed Up ; and the tabs or controls are not visible GUISwitch($h_MainGUI, $g_aidControls[$MAIN_TAB]) Next GUICtrlCreateTabItem('') EndFunc ;==>Draw_Tabs ; enables or disables the submit report button Func SubmitBtn_SetState() If BitAND( _ ; check if two checkxoes are checked GUICtrlRead($g_aidControls[$AGREE_CHK]), _ ; if the agree checkbox GUICtrlRead($g_aidControls[$ACCEPT_CHK]), _ ; and the accept checkbox $GUI_CHECKED) = $GUI_CHECKED _ ; are checked Or _ ; they may not both be checked so check the reject checkbox BitAND( _ GUICtrlRead($g_aidControls[$AGREE_CHK]), _ ; if the agree checkbox GUICtrlRead($g_aidControls[$REJECT_CHK]), _ ; and the reject checkbox $GUI_CHECKED) = $GUI_CHECKED _ ; are checked Then ; enable the button GUICtrlSetState($g_aidControls[$SUBMIT_BTN], $GUI_ENABLE) Else ; disable the button GUICtrlSetState($g_aidControls[$SUBMIT_BTN], $GUI_DISABLE) EndIf EndFunc ;==>SubmitBtn_SetState Func TabChange() Switch GUICtrlRead($g_aidControls[$MAIN_TAB]) Case $FRONTMATTERGUI ; Front Matter tab GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI]) GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI]) GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI]) Case $BODYMATTERGUI ; body Matter tab GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI]) GUISetState(@SW_SHOW, $g_aidControls[$BODYMATTERGUI]) GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI]) Case $BACKMATTERGUI ; back matter tab GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI]) GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI]) GUISetState(@SW_SHOW, $g_aidControls[$BACKMATTERGUI]) EndSwitch EndFunc ;==>TabChange #EndRegion #### GUI Actions ############################# #Region #### Main Menu Action ######################## Func MainMenu_FileExport() ; add commands later EndFunc ;==>MainMenu_FileExport Func MainMenu_HelpAbout() ; add commands later EndFunc ;==>MainMenu_HelpAbout Func MainMenu_ViewExit() ; add commands later EndFunc ;==>MainMenu_ViewExit #EndRegion #### Main Menu Action ######################## #Region #### Spreadsheet Actions ##################### ; by weaponx https://www.autoitscript.com/forum/topic/97637-remove-empty-rows-in-2d-array/ ; delete any empty rows in the rangeread array, seems quicker than trying to get ; the first empty row from the spreadsheet Func Array_DeleteEmptyRows($av_Array) Local $i_Rows = UBound($av_Array, $UBOUND_ROWS) Local $i_Cols = UBound($av_Array, $UBOUND_COLUMNS) Local $as_Temp[$i_Rows][$i_Cols] Local $i_NotEmpty Local $i_Count = 0 ; Loop through rows For $Y = 0 To $i_Rows - 1 $i_NotEmpty = 0 ; Loop through columns For $X = 0 To $i_Cols - 1 ; Copy all columns to temp array even if they are all empty $as_Temp[$i_Count][$X] = $av_Array[$Y][$X] ; If even one column contains data, make sure it doesn't get deleted If $av_Array[$Y][$X] <> "" Then $i_NotEmpty = BitOR($i_NotEmpty, 1) Next ; If the row has any data, increment, else keep overwriting last row until it contains something If $i_NotEmpty Then $i_Count += 1 Next ReDim $as_Temp[$i_Count][$i_Cols] Return $as_Temp EndFunc ;==>Array_DeleteEmptyRows ; create an array of values that are read from the controls on the tabs GUI ; these will be written to the spreadsheet later Func ArrayRow_SetValues(ByRef $aid_ControlID, $i_Row) Local $v_Value = '' ; loop through the array columns For $i = $ENTRYS_COL To $REMARKS_COL ; get the value from the supplied array $v_Value = $aid_ControlID[$i_Row][$i] ; check if the value is a controlID If IsNumber($v_Value) Then If Not $v_Value Then ; it's 0 so no control to read $v_Value = '' ; set a blank string Else ; read the data or state of the control $v_Value = GUICtrlRead($aid_ControlID[$i_Row][$i]) ; assume it's a header or heading indentifier string If StringIsUpper(StringRegExpReplace($v_Value, '[^A-Za-z]', '')) Then $g_sSectionCells &= 'A' & $i_Row + 1 & ',' If StringInStr($v_Value, $g_asTabHeaderText[0]) Then $g_sHeaderCells &= 'A' & $i_Row + 1 & ',' EndIf Else ; assume it's a tab header, Entry(s) etc $aid_ControlID[$i_Row][$i] = $v_Value ; if the value is in the If StringInStr($v_Value, $g_asTabHeaderText[0]) Then $g_sHeaderCells &= 'A' & $i_Row + 1 & ',' If StringInStr(_ArrayToString($g_asTabNames, '|'), $v_Value) Then $g_sTabNameCells &= 'A' & $i_Row + 1 & ',' ExitLoop EndIf ; do different things based on the specified control If $i = $ENTRYS_COL Or $i = $REMARKS_COL Then ; these are input controls ; write the value to the array $aid_ControlID[$i_Row][$i] = $v_Value Else ; these are checkbox controls ; get the checked state of the control $aid_ControlID[$i_Row][$i] = CheckBox_ReturnStateAsString($aid_ControlID[$i_Row][$i]) EndIf Next EndFunc ;==>ArrayRow_SetValues ; create a cell range from letters or numbers Func CellRange_Create($v_StartCol, $v_StartRow, $v_EndCol = '', $v_EndRow = '') ; change any numbers passed as parameters to letters If IsNumber($v_StartCol) Then $v_StartCol = StringUpper(_Excel_ColumnToLetter($v_StartCol)) If IsNumber($v_EndCol) Then $v_EndCol = StringUpper(_Excel_ColumnToLetter($v_EndCol)) ; return the cell range If Not $v_EndCol Then Return $v_StartCol & $v_StartRow Return $v_StartCol & $v_StartRow & ':' & $v_EndCol & $v_EndRow EndFunc ;==>CellRange_Create Func CellRange_JoinRanges($s_Range, $v_AddRange) ; if $v_AddRange is a number then convert the number to a letter for the range If IsNumber($v_AddRange) Then $v_AddRange = _Excel_ColumnToLetter($v_AddRange) ; trim any leading\trailing comma symbols before splitting $s_Range = StringRegExpReplace($s_Range, "\A[,]+|[,]+\Z", "") ; split the comma delimited range Local $as_Split = StringSplit($s_Range, ',') ; loop through the array For $i = 1 To $as_Split[0] ; update the array element $as_Split[$i] = $as_Split[$i] & ':' & StringRegExpReplace($as_Split[$i], '[[:alpha:]]+', $v_AddRange) Next ; return a string that will be used as a cell range Return _ArrayToString($as_Split, ',', 1) EndFunc ;==>CellRange_JoinRanges Func CellRange_ResetGlobalValues() $g_sHeaderCells = '' $g_sSectionCells = '' $g_sTabNameCells = '' EndFunc ;==>CellRange_ResetGlobalValues Func GetEmployeeInfo() Local $as_Employee[7][2] = [ _ ; 0 based array ['Employee Information', ''], _ ['Number', GUICtrlRead($g_aidControls[$EMPNUMBER_INP])], _ ['Name', GUICtrlRead($g_aidControls[$EMPNAME_INP])], _ ['Process', GUICtrlRead($g_aidControls[$EMPPROCESS_INP])], _ ['AID', GUICtrlRead($g_aidControls[$AID_LBL])], _ ['Verification', 'I agree that I have checked the entry(s) above'], _ ['Status', 'Accepted']] ; update the last array element if the reject checkbox is checked If GUICtrlRead($g_aidControls[$REJECT_CHK]) = $GUI_CHECKED Then $as_Employee[6][1] = 'Rejected' ; return thte array for writing to the spreadsheet Return $as_Employee EndFunc ;==>GetEmployeeInfo Func GUIControls_GetProperties(ByRef $as_Controls, ByRef $as_ToolTips, $s_Range = 'A') ; set the path to the file that has the values for the inputs Local $s_ExcelFile = @ScriptDir & '\Sample.xlsx' ; #### add some error checking for these excel functions #### Local $o_Excel = _Excel_Open(False) Local $o_WorkBook = _Excel_BookOpen($o_Excel, $s_ExcelFile) ; read the controls from the sheet $as_Controls = _Excel_RangeRead($o_WorkBook, 'Controls') ; remove blank rows $as_Controls = Array_DeleteEmptyRows($as_Controls) ; read the controls tooltip from the sheet. Set the range as 'A1' to '$TABCOUNT UBound($as_Controls)' ; $TABCOUNT will be the number of tabs, currently 3 or Column C, and the last cell will be the number of controls read from the controls sheet ; currently 56 so the range is 'A1:C56' $as_ToolTips = _Excel_RangeRead($o_WorkBook, 'ToolTips', CellRange_Create('A', 1, $TABCOUNT, UBound($as_Controls))) _Excel_Close($o_Excel) EndFunc ;==>GUIControls_GetProperties ; go through the tabs and controls and write the values to a spreadsheet Func WriteArrayValuesToSpreadsheet($o_WorkBook, $i_Layout = Report_GetLayout()) Local _ ; $aa_ControlArrays is an array of arrays. The arrays have the controlIDs for the relevant tab gui controls $aa_ControlArrays[$TABCOUNT + 1] = [$TABCOUNT, $aid_FrontMatterGUI, $aid_BodyMatterGUI, $aid_BackMatterGUI], _ $as_Values = 0, _ ; the array that will hold the values of the gui controls $as_Temp = 0, _ ;_ a temp array for modding $as_EmployeeInfo = GetEmployeeInfo() ;_ array holding the employee information ; set the number of loop to perform when writing. ; If layout 1 is used, there will be a seperate spreadsheet for each tab so 3 loops ; if layout 2 is used all the tabs are written to one spreadsheet so only 1 loop is needed Local _ $i_Total = $aa_ControlArrays[0], _ ;_ set as default layout 1 and loop through the all the arrays $i_Concatenated = 0 ;_ Set the state of the array being processed 0 = multiple 1 = 1 concatenated array Local $s_EmployeeInfoCells = '' ; Change if the layout is 2 and just loop through the concatenated array If $i_Layout > 1 Then $i_Total = 1 ; layout 2 selected #Region #### only needed for cell outside borders if not interested the code can be deleted ### Local _ ; Excel constants not in ExcelConstants.au3 $i_xlAutomatic = -4105, _ $i_xlContinuous = 1, _ $i_xlThin = 2, _ $i_xlEdgeLeft = 7, _ $i_xlEdgeTop = 8, _ $i_xlEdgeBottom = 9, _ $i_xlEdgeRight = 10 #EndRegion #### only needed for cell outside borders if not interested the code can be deleted ### ; loop through the arrays For $i = 1 To $i_Total ; on each loop copy the nested array to a temp one, this will be filled with the values of the controls $as_Values = $aa_ControlArrays[$i] ; set a starting array _ArrayInsert($as_Values, 0, _ArrayToString($g_asTabHeaderText, '|')) ; add the headers to each array #Region #### This determines which array(s) to use for the write #### If $i_Layout = 2 Then _ArrayInsert($as_Values, 0, $g_asTabNames[0]) ; add the first tab name to the array ; loop through the remaining arrays For $j = 2 To $TABCOUNT ; $TABCOUNT = 3 $as_Temp = $aa_ControlArrays[$j] ; copy to a temp array so we can add the tab names and headet text _ArrayInsert($as_Temp, 0, $g_asTabNames[$j - 1]) ; add the next tab name to the array _ArrayInsert($as_Temp, 1, _ArrayToString($g_asTabHeaderText, '|')) ; add the next header text ; joining them $as_Values _ArrayConcatenate($as_Values, $as_Temp) Next EndIf #EndRegion #### This determines which array(s) to use for the write #### ; loop through the temp array For $j = 0 To UBound($as_Values) - 1 ; update the array row with the control values ArrayRow_SetValues($as_Values, $j) Next ; activate the sheet $o_WorkBook.Sheets($i).Activate ; write the array to an excel spreadsheet _Excel_RangeWrite($o_WorkBook, $i, $as_Values) If @error Then Return SetError(@error, @extended, 'Error Writing ' & $g_asTabNames[$i - 1] & ' to sheet') ; adjust the sheet properties whilst it's active With $o_WorkBook.ActiveSheet If $g_iMultipleSheets Then ; we are writing multiple sheets ; set the sheet name .Name = $g_asTabNames[$i - 1] EndIf If $i = 1 Then ; if we are on the first sheet then write the employee info _Excel_RangeWrite($o_WorkBook, $i, $as_EmployeeInfo, CellRange_Create($ARRAYMAX_COLS + 2, 1)) ; currently 'G1' If @error Then Return SetError(@error, @extended, 'Error Writing Employee info to sheet') ; this is where the cells that have the employee Information get modified #Region #### Employee Information #### #Region #### Title Cells #### ; get the range of the employee title cells $s_EmployeeInfoCells = CellRange_Create($EMPLOYEESTART, 1, $EMPLOYEEFINISH, 1) ; currently 'G1:H1' ; merge the title cells .Range($s_EmployeeInfoCells).Merge ; center the text .Range($s_EmployeeInfoCells).HorizontalAlignment = $xlCenter ; set the font weight as bold .Range($s_EmployeeInfoCells).Font.FontStyle = 'Bold' ; set the tab name cells back colour .Range($s_EmployeeInfoCells).Interior.Color = 0xFFE6CC #EndRegion #### Title Cells #### #Region #### Employee Description Cells ; get the range of the employee title cells $s_EmployeeInfoCells = CellRange_Create($EMPLOYEESTART, 2, $EMPLOYEESTART, UBound($as_EmployeeInfo)) ; currently 'G2:G7' ; set the header cells back colour .Range($s_EmployeeInfoCells).Interior.Color = 0xDAEFE2 #EndRegion #### Employee Description Cells #Region #### Accepted\Rejected Cell #### ; set the accepted\rejected colour $s_EmployeeInfoCells = CellRange_Create($EMPLOYEEFINISH, UBound($as_EmployeeInfo)) .Range($s_EmployeeInfoCells).Font.Color = $COLOR_GREEN If $as_EmployeeInfo[UBound($as_EmployeeInfo) - 1][1] = 'Rejected' Then .Range($s_EmployeeInfoCells).Font.Color = 0x0000FF #EndRegion #### Accepted\Rejected Cell #### ; all employee information cell range $s_EmployeeInfoCells = CellRange_Create($EMPLOYEESTART, 1, $EMPLOYEEFINISH, UBound($as_EmployeeInfo)) ; currently 'G1:H7' ; add all borders to cells .Range($s_EmployeeInfoCells).Borders.LineStyle = $i_xlContinuous #EndRegion #### Employee Information #### EndIf ; this is where the cells that have the tab names in get nodified #Region #### Tab Name Cells #### If $g_sTabNameCells Then $g_sTabNameCells = CellRange_JoinRanges($g_sTabNameCells, $ARRAYMAX_COLS) ; set the font weight as bold .Range($g_sTabNameCells).Font.FontStyle = 'Bold' ; set the tab name cells back colour .Range($g_sTabNameCells).Interior.Color = 0xBFBFBF ; merge the cells .Range($g_sTabNameCells).Merge ; centre align the cells .Range($g_sTabNameCells).HorizontalAlignment = $xlCenter EndIf #EndRegion #### Tab Name Cells #### ; this is where the cells that have Entry(s), N/A, No Error etc get modified #Region #### Header Cells #### If $g_sHeaderCells Then $g_sHeaderCells = CellRange_JoinRanges($g_sHeaderCells, $ARRAYMAX_COLS) ; set the font weight as bold .Range($g_sHeaderCells).Font.FontStyle = 'Bold' ; centre align the cells .Range($g_sHeaderCells).HorizontalAlignment = $xlCenter ; set the header cells back colour .Range($g_sHeaderCells).Interior.Color = 0xFFE6CC EndIf #EndRegion #### Header Cells #### ; this is where the cells that have the section text like CATEGORY, HISTORY DATES etc get modified #Region #### Section Cells #### If $g_sSectionCells Then $g_sSectionCells = CellRange_JoinRanges($g_sSectionCells, $ARRAYMAX_COLS) ; set the font weight as bold .Range($g_sSectionCells).Font.FontStyle = 'Bold' ; set the header cells back colour .Range($g_sSectionCells).Interior.Color = 0xDAEFE2 ; add outside borders .Range($g_sSectionCells).Borders($i_xlEdgeTop).LineStyle = $i_xlContinuous .Range($g_sSectionCells).Borders($i_xlEdgeLeft).LineStyle = $i_xlContinuous .Range($g_sSectionCells).Borders($i_xlEdgeRight).LineStyle = $i_xlContinuous .Range($g_sSectionCells).Borders($i_xlEdgeBottom).LineStyle = $i_xlContinuous EndIf #EndRegion #### Section Cells #### ; set the column letters that will auto size to fit the text currently 'A:H:' .Columns(CellRange_Create($ENTRYS_COL + 1, '', $ARRAYMAX_COLS + 3)).AutoFit EndWith CellRange_ResetGlobalValues() ;~ Sleep(20) Next $o_WorkBook.Sheets(1).Activate ; activate the first sheet CellRange_ResetGlobalValues() EndFunc ;==>WriteArrayValuesToSpreadsheet #EndRegion #### Spreadsheet Actions ##################### #Region #### Submit Report Actions ################### Func Layout1_Selected() $g_iMultipleSheets = 1 EndFunc ;==>Layout1_Selected Func Layout2_Selected() $g_iMultipleSheets = 0 EndFunc ;==>Layout2_Selected ; gets the currently selected layout type for the report Func Report_GetLayout() ; return the state of the layout 1 radio button If GUICtrlRead($g_aidControls[$LAYOUTONE_RDO]) = $GUI_CHECKED Then Return 1 Return 2 EndFunc ;==>Report_GetLayout ; submit your report Func Report_Submit() SplashTextOn("submit", "Submitting report...", 300, 50, -1, -1, $DLG_NOTITLE + $DLG_TEXTVCENTER, '', '', $FW_BOLD) ;~ Local $o_WorkBook = '' ; open an excel instance Local $o_Excel = _Excel_Open(False) If @error Then Return SetError(1, _ MsgBox($MB_SYSTEMMODAL, _ '_Excel_Open', _ 'Error creating the Excel application object' & @CRLF & '@error = ' & @error & ', @extended = ' & @extended), 0) ; set the number of sheets to add when opening a new workbook Local $iSheets = $TABCOUNT If Not $g_iMultipleSheets Then $iSheets = 1 ; open a new excel book Local $o_WorkBook = _Excel_BookNew($o_Excel, $iSheets) ; $TABCOUNT is the number of tabs in your program and so the number of sheets we create for submitting If @error Then Return SetError(2, _ MsgBox($MB_SYSTEMMODAL, _ '_Excel_BookNew', _ 'Error creating new workbook' & @CRLF & '@error = ' & @error & ', @extended = ' & @extended), _Excel_Close($o_Excel)) ;~ ; this will read the control values to an array and write the array to an excel file WriteArrayValuesToSpreadsheet($o_WorkBook) If Not @error Then ; create a file path based on time. If you want to keep overwriting the files the create a definite file name ; and use the commented line Local $s_FilePath = @ScriptDir & '\Report_' & @YEAR & '-' & @MON & '-' & @MDAY & '_' & @HOUR & @MIN & @SEC & '.xlsx' ; ; save the created report and overwrite the previous ;~ _Excel_BookSaveAs($o_WorkBook, $s_FilePath, $xlWorkbookDefault, True) ; save the created report to a new file _Excel_BookSaveAs($o_WorkBook, $s_FilePath, $xlWorkbookDefault) If @error Then MsgBox($MB_SYSTEMMODAL, _ "_Excel_BookSaveAs", _ "Error saving workbook to '" & $s_FilePath & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf _Excel_Close($o_Excel) ; reset the confirmation checkboxes ConfirmationChks_SetState($GUI_DISABLE, 1) ; disable the submit button SubmitBtn_SetState() ; update the splash text ControlSetText("submit", "", "Static1", 'Opening report...') ; open the report, remove if you don't need to ShellExecute($s_FilePath) SplashOff() EndFunc ;==>Report_Submit #EndRegion #### Submit Report Actions ################### Link to comment Share on other sites More sharing options...
KickStarter15 Posted January 18, 2018 Author Share Posted January 18, 2018 13 hours ago, benners said: Well it is doing a lot of stuff to get the controls and tooltips. If you comment out the line below it loads faster. Yup, got it... thanks a lot. I knew that would be the cause. 13 hours ago, benners said: I have made some more tweaks like altering\renaming functions, cell colouring in the report etc. Not much else to do. Well, not much do you think but it's amazing benners. Very satisfied and very very fine with it. Thank you, Thank you so much benners. Programming is "To make it so simple that there are obviously no deficiencies" or "To make it so complicated that there are no obvious deficiencies" by C.A.R. Hoare. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now