Changing Variable by button press and write in excle

[detrauti]

Hello from  Sibiu

I try to change a variable by pressing a button. Unfortunately I don't understand why it doesn't and why the GUIs always closing. Before I work on the same script with only one button and it was fine. Please help me

changing variable by GUI-butto.au3

[water]

Because the loop was missing and the Case statement was wrong. I marked the changed lines with "<=="

expandcollapse popup

;~ #includes
#include <Excel.au3> ;reference that Excel UDF is included
#include <GUIConstantsEx.au3> ;Grafical User Interface
#include <WindowsConstants.au3> ;including window cosntants of the au3 script
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>

;~ $variables
Local $counter

;~ Read Only File
Local $sReadFile = @ScriptDir & "\5_Zeiterfassung mit GUI input.xlsx"
;~ Save File Name
Local $sSaveFile = @ScriptDir & "\SaveFile.xlsx"
;~ counting variable
$counter1 = 1
$counter2 = 1
$counter3 = 1

;~GUI desginged in Kodpe
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 350, 50, 192, 124)
$Button1 = GUICtrlCreateButton("A", 16, 16, 75, 25)
$Button2 = GUICtrlCreateButton("B", 112, 16, 75, 25)
$Button3 = GUICtrlCreateButton("C", 200, 16, 75, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

;~ Open Excel --- $oExcel=the variable standing for the open Excel-window
$oExcel = _Excel_Open()
;~ Open ReadFile.xlsx as Read Only --- $oWorkBook=the variable standing for the open Excel workbook
$oWorkBook = _Excel_BookOpen($oExcel, $sReadFile, False, True)

While 1 ; <== Added
    $nMsg = GUIGetMsg() ;opens the GUI
    Switch $nMsg ;conditional statement
        Case $Button1, $Button2, $Button3 ; <== Changed
            If $Button1 = "1" Then $machine = "A"
            If $Button2 = "1" Then $machine = "B"
            If $Button3 = "1" Then $machine = "C"
            Dim $time[8] = [$counter, $machine, @YEAR, @MON, @MDAY, @HOUR, @MIN, @SEC] ; time information
            $transposetime = _ArrayTranspose($time) ;transposed time inforamtion
            _Excel_RangeWrite($oWorkBook, 1, $time, "D10") ;write in B2
            $oExcel.Range("D2:L2").Select
            $oExcel.Selection.Copy
            $oExcel.Range("D10:L10").Select
            $oExcel.Selection.Insert(-4121)
;~ increase counter
            $counter1 = $counter1 + 1
        Case $GUI_EVENT_CLOSE ;pressing the close button in the corner
            Exit ;GUI is being closed
    EndSwitch
WEnd ; <== Added

 

[water]

Enhanced version so the line gets written to Excel.
The first button gets ID = 3, the second ID = 4 etc. That's why I store the counter in an array and use the ID of the pressed button as index.

expandcollapse popup

;~ #includes
#include <Excel.au3> ;reference that Excel UDF is included
#include <GUIConstantsEx.au3> ;Grafical User Interface

;~ $variables
Local $counter

;~ Read Only File
Local $sReadFile = @ScriptDir & "\5_Zeiterfassung mit GUI input.xlsx"
;~ Save File Name
Local $sSaveFile = @ScriptDir & "\SaveFile.xlsx"
;~ counting variable
Global $aCounter[3] = [0, 0, 0] ; <== Changed 2

;~GUI desginged in Kodpe
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 350, 50, 192, 124)
$Button1 = GUICtrlCreateButton("A", 16, 16, 75, 25) ; <== ID 3
$Button2 = GUICtrlCreateButton("B", 112, 16, 75, 25) ; <== ID 4
$Button3 = GUICtrlCreateButton("C", 200, 16, 75, 25) ; <== ID 5
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

;~ Open Excel --- $oExcel=the variable standing for the Excel application object
$oExcel = _Excel_Open()
;~ Open ReadFile.xlsx as Read Only --- $oWorkBook=the variable standing for the Excel workbook object
; $oWorkBook = _Excel_BookOpen($oExcel, $sReadFile, False, True) ; <== For testing purpose
$oWorkBook = _Excel_BookNew($oExcel) ; <== For testing purpose

While 1 ; <== Added
    $nMsg = GUIGetMsg() ;opens the GUI
    Switch $nMsg ;conditional statement
        Case $Button1, $Button2, $Button3 ; <== Changed
            If $nMsg = $Button1 Then $machine = "A" ; <== Changed
            If $nMsg = $Button2 Then $machine = "B" ; <== Changed
            If $nMsg = $Button3 Then $machine = "C" ; <== Changed
            Local $time[8] = [$aCounter[$nMsg - 3], $machine, @YEAR, @MON, @MDAY, @HOUR, @MIN, @SEC] ; time information  ; <== Changed
            $transposetime = _ArrayTranspose($time) ;transposed time information
            _Excel_RangeWrite($oWorkBook, 1, $time, "D10") ;write in B2
            $oExcel.Range("D2:L2").Select
            $oExcel.Selection.Copy
            $oExcel.Range("D10:L10").Select
            $oExcel.Selection.Insert(-4121)
;~ increase counter
            $aCounter[$nMsg - 3] += 1 ; <== Changed
        Case $GUI_EVENT_CLOSE ;pressing the close button in the corner
            Exit ;GUI is being closed
    EndSwitch
WEnd ; <== Added

 

Edited March 7, 2017 by water

[water]

Next version using a function of the Excel UDF to insert the blank line:

expandcollapse popup

;~ #includes
#include <Excel.au3> ;reference that Excel UDF is included
#include <GUIConstantsEx.au3> ;Grafical User Interface

;~ $variables
Local $counter

;~ Read Only File
Local $sReadFile = @ScriptDir & "\5_Zeiterfassung mit GUI input.xlsx"
;~ Save File Name
Local $sSaveFile = @ScriptDir & "\SaveFile.xlsx"
;~ counting variable
Global $aCounter[3] = [0, 0, 0]

;~GUI desginged in Kodpe
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 350, 50, 192, 124)
$Button1 = GUICtrlCreateButton("A", 16, 16, 75, 25) ; <== ID 3
$Button2 = GUICtrlCreateButton("B", 112, 16, 75, 25) ; <== ID 4
$Button3 = GUICtrlCreateButton("C", 200, 16, 75, 25) ; <== ID 5
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

;~ Open Excel --- $oExcel=the variable standing for the open Excel-window
$oExcel = _Excel_Open()
;~ Open ReadFile.xlsx as Read Only --- $oWorkBook=the variable standing for the open Excel workbook
; $oWorkBook = _Excel_BookOpen($oExcel, $sReadFile, False, True) ; <== For testing purpose
$oWorkBook = _Excel_BookNew($oExcel) ; <== For testing purpose

While 1 ; <== Added
    $nMsg = GUIGetMsg() ;opens the GUI
    Switch $nMsg ;conditional statement
        Case $Button1, $Button2, $Button3 ; <== Changed
            If $nMsg = $Button1 Then $machine = "A"
            If $nMsg = $Button2 Then $machine = "B"
            If $nMsg = $Button3 Then $machine = "C"
            Local $time[8] = [$aCounter[$nMsg - 3], $machine, @YEAR, @MON, @MDAY, @HOUR, @MIN, @SEC] ; time information
            $transposetime = _ArrayTranspose($time) ;transposed time information
            _Excel_RangeInsert($oWorkbook.Activesheet, "11:11", $xlShiftDown) ; insert empty row and shift data to the end <== added
            _Excel_RangeWrite($oWorkBook, 1, $time, "D11") ; write line
;~ increase counter
            $aCounter[$nMsg - 3] += 1
        Case $GUI_EVENT_CLOSE ;pressing the close button in the corner
            Exit ;GUI is being closed
    EndSwitch
WEnd ; <== Added

 

[detrauti]

Thank you Water.

It amazes me that you seem to completely understand the task I am studying on.

I have to further question regarding you second answer

14 minutes ago, water said:

Enhanced version so the line gets written to Excel.
The first button gets ID = 3, the second ID = 4 etc. That's why I store the counter in an array and use the ID of the pressed button as index.

 

1.) can you explain me the line:

$aCounter[$nMsg - 3] += 1 ; <== Changed

what would it mean in words?

2.) how can you insert the images that they appear colored?

 

[water]

1. Button 1 has the ID = 3. The counters are stored in an array. The first element of an array needs to be accessed with index = 0. Hence I need to subtract 3 to calculate the index from the button ID.
2. Which images?

[detrauti]

_Excel_RangeInsert($oWorkbook.Activesheet, "11:11", $xlShiftDown) ; insert empty row and shift data to the end <== added

This is also a good idea and I had it the solution before. But this causes follow problem:

some cells next to the ones where in input the information contains formulars. If I inseart a whole row the the formular will disappear

--> as a solution I could have copied the formular and insert it in the cell in the new row or does there exist a more elegant way?

[water]

You could copy the format of the line below to the new line:

_Excel_RangeInsert($oWorkbook.Activesheet, "11:11", $xlShiftDown, $xlFormatFromRightOrBelow) ; insert empty row and shift data to the end <== added

 

[detrauti]

7 minutes ago, water said:

1. Button 1 has the ID = 3. The counters are stored in an array. The first element of an array needs to be accessed with index = 0. Hence I need to subtract 3 to calculate the index from the button ID.
2. Which images?

to 1. why does Button1 has the ID = 3? How can I find out? only with AU3info? or is this obvious and I simply can't see? (why subtracting 3 after knowing the IDs i understand :-P --> it refers on array cell 1/2/3 and +=1 adds one to the writen falue

 

to 2. I inseart my programcode as an image because when I just copy and paste everything is black --> yours are colored and looks like in scite/ editor

 

[water]

1. That's an internal Autoit ID. To find out simply write variable $Button1 to the Console.
2. I see. Use the AutoIt code tag in the editor (button "<>") to insert code.