Jump to content
detrauti

Changing Variable by button press and write in excle

Recommended Posts

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

Share this post


Link to post
Share on other sites
water

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

 

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

;~ #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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

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

 

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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?

 

Share this post


Link to post
Share on other sites
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?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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.
  • Like 2

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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

×