detrauti

Changing Variable by button press and write in excle

10 posts in this topic

#1 ·  Posted

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

Share this post


Link to post
Share on other sites



#2 ·  Posted

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

;~ #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

 

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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.

;~ #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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted

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

;~ #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

 

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted

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?

 

Share this post


Link to post
Share on other sites

#6 ·  Posted

  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?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#7 ·  Posted

_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?

Share this post


Link to post
Share on other sites

#8 ·  Posted

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#9 ·  Posted

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

 

Share this post


Link to post
Share on other sites

#10 ·  Posted

  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.
2 people like this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now