reading Excel cell value, writing to ini, and reading from ini.

[AndreasNWWWWW]

Hello, i where wondering if anyone could help me.

i'm trying to read the excel values into a ini file to later read them and create label buttons from it.

first part : reading excel goes well, however as soon as i have "=" or any equal signs in there it only takes the first value and writes it into the INI file.

like it stops after one go. and not until all values has been written.

Func hentemappe()
    $programbane =IniRead ( "programbane.INI", "programbane", "adresse", "" )
Local $oExcel = _Excel_Open()
Local $bReadOnly = False
Local $bVisible = True
Local $var = 1
Local $R = "D" & $var & ":" & "E" & $var
; ****************************************************************************
; Open an existing workbook and return its object identifier.
; *****************************************************************************

Local $sWorkbook = $programbane
local $inifilbane=@ScriptDir

Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, $bReadOnly, $bVisible)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A"), 1)

_ArrayDisplay($aResult)
;MsgBox(1,"",$aResult)
For $i = 1 To ($aResult)
    ConsoleWrite($aResult[$i] & @CRLF)
    IniWriteSection("prosjekter.INI","Prosjektnummer",$aResult[$i] &  "=" )

Next

EndFunc

second part is the reading section of the file based on the example script.

however that won't show any values unless there is a = sign after the values in the ini.file.

Func Example()
    ; Create a constant variable in Local scope of the filepath that will be read/written to.
    $x=0
    Local Const $sFilePath = @ScriptDir & "\prosjekter.ini"



    ; Read the INI section labelled 'General'. This will return a 2 dimensional array.
    Local $aArray = IniReadSection($sFilePath, "Prosjektnummer")

    ; Check if an error occurred.
    If not @error Then
        ; Enumerate through the array displaying the keys and their respective values.
        For $i = 1 To $aArray[0][0]+2
            MsgBox($MB_SYSTEMMODAL, "", "Key: " & $aArray[$i][0])

Next
    EndIf

    ; Delete the INI file.

EndFunc   ;==>Example

 

 

what am i missing here? is there a way to get the reading of the ini section to only look for one value in the array? and don't need the = sign?

Edited February 4, 2022 by AndreasNWWWWW

[big_daddy]

There is a reason that ini files are typically used for configuration and not for storing large amounts of data. Is there a reason you need to store this in a separate file instead of reading the data live from the Excel file ?

[Subz]

From IniWriteSection in helpfile

data = The data to write. The data can either be a string or an array. If the data is a string, then each key=value pair must be delimited by @LF. If the data is an array, the array must be 2-dimensional and the second dimension must be 2 elements. 
Since you're writing a string "$aResult[$i]", Each row should be formatted like so

Key1=Value1@LF@Key2=Value2@LF@Key3=Value3

Example:

Opt("ExpandVarStrings", 1)
Local $sExampleData = "Key1=Value1@LF@Key2=Value2@LF@Key3=Value3"
IniWriteSection("Example.ini", "Example", $sExampleData)

If you wanted to use an array then your Excel spreadsheet should have two columns A (Ini Key) and B (Ini Value), use something like

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:B"), 1)

You can then use:

IniWriteSection"Example.ini", "Example", $aResult)

 

[AndreasNWWWWW]

11 hours ago, big_daddy said:

There is a reason that ini files are typically used for configuration and not for storing large amounts of data. Is there a reason you need to store this in a separate file instead of reading the data live from the Excel file ?

The backstory of my project is that i'm a newly educated construction engineer, and at my workplace we keep hourtracking in an excel spreadsheet.
and i'm looking for a way to make this a little bit easier.

i have reversed engineered a script i found here https://www.autoitscript.com/forum/topic/187139-time-tracker/ to fit my need.
the layout it has atm is looking like this Image(A), however i am trying to make it like Image(B)

where the button name(s) is populated with the info from the Excelsheet cell value.

ie project 1 button has the name from excel A2 = 2022-001-001

and then i'm planing on having it so that once that button is pushed the timer starts tracking on that project number.
and so at the end of the day when i save the file it says the time used on each projectnumber.

so when a new project starts i just add it to the excel sheet, and then next time i open the timetracker a new button is populated ready to track time used on that project.

 

and since i'm rather new to Autoit, just used it every now and then, .INI writing is the only way i know of.

 

i am able to write it into the ini file, however i can only read the value with this script if i add a = sign after the name  in the ini file.

Local Const $sFilePath = @ScriptDir & "\prosjekter.ini"



    ; Read the INI section labelled 'General'. This will return a 2 dimensional array.
    Local $aArray = IniReadSection($sFilePath, "Prosjektnummer")

    ; Check if an error occurred.
    If not @error Then
        ; Enumerate through the array displaying the keys and their respective values.
        For $i = 1 To $aArray[0][0]+2
            MsgBox($MB_SYSTEMMODAL, "", "Key: " & $aArray[$i][0])

Next
    EndIf

and since the first script posted don't go through all if i add a = i'm stuck

update: managed to get it to write the = sign i need to be able to read the values, with iniwrite instead of section

IniWrite("prosjekter.INI","Prosjektnummer",$aResult[$i],"")

IniWrite("prosjekter.INI","Prosjektnummer",$aResult[$i],"")

 

 

 

 

 

 

Image(A)

Image(B)

Edited February 5, 2022 by AndreasNWWWWW

[AndreasNWWWWW]

Now  that i have solved my initial problem, is there a way to use GUICtrlSetData based on how many arrays that is generated?
for example here i have 6 projects,a so i need it to update names on 6 buttons, all buttons are without text so white until something is written, planing on having 10-20 buttons in total.

 

my current code is looking like this, is there a way to have it update the buttons as needed?

ie $button + number, and have it to connect the result from the ini file

ini-file

 

 

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <Excel.au3>
#include <GuiListView.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <GuiEdit.au3>
#include <Constants.au3>
#include <Misc.au3>
Global $count = 0



#Region ### START Koda GUI section ### Form=
$Form1_1 = GUICreate("Tidsrapport", 1273, 433, 192, 124)
$Label1 = GUICtrlCreateLabel("Time Tracker", 142, 8, 120, 29)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Label4 = GUICtrlCreateLabel("00:00:00", 72, 40, 244, 84)
GUICtrlSetFont(-1, 50, 400, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0x000000)
$Label2 = GUICtrlCreateLabel("Prosjektnummer", 87, 90, 214, 29)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Input1 = GUICtrlCreateInput("", 96, 130, 201, 21)
$Label3 = GUICtrlCreateLabel("Prosjekter registrert", 120, 162, 130, 29)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$ListView1 = GUICtrlCreateListView("Prosjektnummer|Start tid|Slutt tid|Total tid", 8, 194, 385, 175)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 0, 50)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 1, 50)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 2, 50)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 3, 50)
$ListView1_0 = GUICtrlCreateListViewItem("|hour:mintm", $ListView1)
$Button1 = GUICtrlCreateButton("Start", 24, 376, 89, 41, $BS_NOTIFY)
GUICtrlSetCursor (-1, 0)
$Button2 = GUICtrlCreateButton("Eksporter til Excel", 152, 376, 89, 41, $BS_NOTIFY)
GUICtrlSetCursor (-1, 0)
$Button3 = GUICtrlCreateButton("Avbryt", 280, 376, 81, 41, $BS_NOTIFY)
GUICtrlSetCursor (-1, 0)
$SETTFIL = GUICtrlCreateButton("SETT FILBANE", 344, 40, 115, 25)
$OPPDATERFIL = GUICtrlCreateButton("OPPDATER FILBANE", 344, 72, 115, 25)
$FILBANE = GUICtrlCreateLabel("FILBANE:", 344, 24, 51, 17)
$Button4 = GUICtrlCreateButton("Prosjekt-1", 528, 152, 171, 25)
$Button5 = GUICtrlCreateButton("", 528, 184, 171, 25)
;$FILBANEVALGT = GUICtrlCreateLabel("sFileOpenDialog", 392, 16, 460, 25)
$Button6 = GUICtrlCreateButton("", 528, 216, 171, 25)
$Button7 = GUICtrlCreateButton("", 528, 248, 171, 25)
$Button8 = GUICtrlCreateButton("", 528, 280, 171, 25)
$Button9 = GUICtrlCreateButton("", 528, 312, 171, 25)
$Button10 = GUICtrlCreateButton("", 528, 344, 171, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
  $nMsg = GUIGetMsg()




While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit


Case $SETTFIL
mappevalg()

        Case $OPPDATERFIL

hentemappe()
EndSwitch
WEnd



Func hentemappe()
    $programbane =IniRead ( "programbane.INI", "programbane", "adresse", "" )
Local $oExcel = _Excel_Open()
Local $bReadOnly = False
Local $bVisible = True
Local $var = 1
Local $R = "D" & $var & ":" & "E" & $var
; ****************************************************************************
; Open an existing workbook and return its object identifier.
; *****************************************************************************

Local $sWorkbook = $programbane
local $inifilbane=@ScriptDir

Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, $bReadOnly, $bVisible)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A"),1)
;Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:B"), 1)
_ArrayDisplay($aResult)
;MsgBox(1,"",$aResult)
For $i = 1 to UBound($aResult) - 1

    ;ConsoleWrite($aResult[$i] & @CRLF)
    ;IniWriteSection("prosjekter.INI","Prosjektnummer",$aResult[$i])
    IniWrite("prosjekter.INI","Prosjektnummer",$aResult[$i],"")

;lager nye prosjektmapper
 GUICtrlSetData($Button4, $aResult[$i])
 GUICtrlSetData($Button5, $aResult[$i])
 GUICtrlSetData($Button6, $aResult[$i])
 GUICtrlSetData($Button7, $aResult[$i])
 GUICtrlSetData($Button8, $aResult[$i])
 GUICtrlSetData($Button9, $aResult[$i])
 GUICtrlSetData($Button10, $aResult[$i])


Next

EndFunc

Func mappevalg()
    ; Create a constant variable in Local scope of the message to display in FileOpenDialog.
    Local Const $sMessage = "Select a single file of any type."

    ; Display an open dialog to select a file.
    Local $sFileOpenDialog = FileOpenDialog($sMessage, @WindowsDir & "\", "All (*.*)", $FD_FILEMUSTEXIST)
    If @error Then
        ; Display the error message.
        MsgBox($MB_SYSTEMMODAL, "", "No file was selected.")

        ; Change the working directory (@WorkingDir) back to the location of the script directory as FileOpenDialog sets it to the last accessed folder.
        FileChangeDir(@ScriptDir)
    Else
        ; Change the working directory (@WorkingDir) back to the location of the script directory as FileOpenDialog sets it to the last accessed folder.
        FileChangeDir(@ScriptDir)

        ; Replace instances of "|" with @CRLF in the string returned by FileOpenDialog.
        $sFileOpenDialog = StringReplace($sFileOpenDialog, "|", @CRLF)

        ; Display the selected file.
        ;MsgBox($MB_SYSTEMMODAL, "", "You chose the following file:" & @CRLF & $sFileOpenDialog)
IniWrite ( "programbane.INI", "programbane", "adresse",$sFileOpenDialog  )
sleep(200)
$FILBANEVALGT = GUICtrlCreateLabel($sFileOpenDialog, 392, 16, 460, 25)
    EndIf




EndFunc   ;==>Example

 

 

 

 

 

image:

[Nine]

I suggest you create your buttons in an array with a loop, then you can easily show/hide and set data based on another array.  Example :

#include <GUIConstants.au3>
#include <Constants.au3>

Local $hGUI = GUICreate("Test")
Local $aButton[20]
For $i = 0 To UBound($aButton) - 1
  $aButton[$i] = GUICtrlCreateButton("", 10, 10 + $i * 25, 80, 20)
  GUICtrlSetState(-1, $GUI_HIDE)
Next

GUISetState()

For $i = 0 to 6
  GUICtrlSetData($aButton[$i], "Number " & $i +1)
  GUICtrlSetState($aButton[$i], $GUI_SHOW)
Next

Local $nMsg

While True
  $nMsg = GUIGetMsg()
  Switch $nMsg
    Case $GUI_EVENT_CLOSE
      ExitLoop
    Case $aButton[0] To $aButton[UBound($aButton) - 1]
      ConsoleWrite("Button " & $nMsg - $aButton[0] + 1 & " has been pressed" & @CRLF)
  EndSwitch
WEnd

 

Edited February 5, 2022 by Nine
added support to button press

[AndreasNWWWWW]

1 hour ago, Nine said:

I suggest you create your buttons in an array with a loop, then you can easily show/hide and set data based on another array.  Example :

#include <GUIConstants.au3>
#include <Constants.au3>

Local $hGUI = GUICreate("Test")
Local $aButton[20]
For $i = 0 To UBound($aButton) - 1
  $aButton[$i] = GUICtrlCreateButton("", 10, 10 + $i * 25, 80, 20)
  GUICtrlSetState(-1, $GUI_HIDE)
Next

GUISetState()

For $i = 0 to 6
  GUICtrlSetData($aButton[$i], "Number " & $i +1)
  GUICtrlSetState($aButton[$i], $GUI_SHOW)
Next

Local $nMsg

While True
  $nMsg = GUIGetMsg()
  Switch $nMsg
    Case $GUI_EVENT_CLOSE
      ExitLoop
    Case $aButton[0] To $aButton[UBound($aButton) - 1]
      ConsoleWrite("Button " & $nMsg - $aButton[0] + 1 & " has been pressed" & @CRLF)
  EndSwitch
WEnd

 

i'm trying to use you approach to creating buttons. i'm just unsure if it's easier to just the data i pull from the excel sheet instead of going through the same step with an .INI file.

now i'm trying to find a way to get the name of the buttons to correspond to the info in the Excel/ini file.

Wish i had more knowledge with autoit than i do made like 3-4 simple programs like a year ago xD

current code implemented your button solution.

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <Excel.au3>
#include <GuiListView.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <GuiEdit.au3>
#include <Constants.au3>
#include <Misc.au3>
Global $count = 0



#Region ### START Koda GUI section ### Form=
$Form1_1 = GUICreate("Tidsrapport", 1273, 433, 192, 124)
$Label1 = GUICtrlCreateLabel("Time Tracker", 142, 8, 120, 29)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Label4 = GUICtrlCreateLabel("00:00:00", 72, 40, 244, 84)
GUICtrlSetFont(-1, 50, 400, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0x000000)
$Label2 = GUICtrlCreateLabel("Prosjektnummer", 87, 90, 214, 29)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Input1 = GUICtrlCreateInput("", 96, 130, 201, 21)
$Label3 = GUICtrlCreateLabel("Prosjekter registrert", 120, 162, 130, 29)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$ListView1 = GUICtrlCreateListView("Prosjektnummer|Start tid|Slutt tid|Total tid", 8, 194, 385, 175)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 0, 50)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 1, 50)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 2, 50)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 3, 50)
$ListView1_0 = GUICtrlCreateListViewItem("|hour:mintm", $ListView1)
$Button1 = GUICtrlCreateButton("Start", 24, 376, 89, 41, $BS_NOTIFY)
GUICtrlSetCursor(-1, 0)
$Button2 = GUICtrlCreateButton("Eksporter til Excel", 152, 376, 89, 41, $BS_NOTIFY)
GUICtrlSetCursor(-1, 0)
$Button3 = GUICtrlCreateButton("Avbryt", 280, 376, 81, 41, $BS_NOTIFY)
GUICtrlSetCursor(-1, 0)
$SETTFIL = GUICtrlCreateButton("SETT FILBANE", 344, 40, 115, 25)
$OPPDATERFIL = GUICtrlCreateButton("OPPDATER FILBANE", 344, 72, 115, 25)
$FILBANE = GUICtrlCreateLabel("FILBANE:", 344, 24, 51, 17)

;$FILBANEVALGT = GUICtrlCreateLabel("sFileOpenDialog", 392, 16, 460, 25)

GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

;setter mappestruktur fra INI.filen
$nMsg = GUIGetMsg()
Local $aButton[20]
For $i = 0 To UBound($aButton) - 1
    $aButton[$i] = GUICtrlCreateButton("", 528, 60 + $i * 40, 200, 40)
    GUICtrlSetState(-1, $GUI_HIDE)
Next

GUISetState()

For $i = 0 To 6
    GUICtrlSetData($aButton[$i], "Number " & $i + 1)
    GUICtrlSetState($aButton[$i], $GUI_SHOW)
Next
;startfil()


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit


        Case $SETTFIL
            mappevalg()

        Case $OPPDATERFIL
            hentemappe()

            ;Project button pressed
        Case $aButton[0] To $aButton[UBound($aButton) - 1]
            ConsoleWrite("Button " & $nMsg - $aButton[0] + 1 & " has been pressed" & @CRLF)
    EndSwitch

WEnd




Func hentemappe()
    $programbane = IniRead("programbane.INI", "programbane", "adresse", "")
    Local $oExcel = _Excel_Open()
    Local $bReadOnly = False
    Local $bVisible = True
    Local $bsave = True
    Local $var = 1
    Local $R = "D" & $var & ":" & "E" & $var
    ; ****************************************************************************
    ; Open an existing workbook and return its object identifier.
    ; *****************************************************************************

    Local $sWorkbook = $programbane
    Local $inifilbane = @ScriptDir

    Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, $bReadOnly, $bVisible)
    _Excel_BookClose($oExcel, $bsave)
    Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A"), 1)
    ;Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:B"), 1)
    _ArrayDisplay($aResult)
    ;MsgBox(1,"",$aResult)
    For $i = 1 To UBound($aResult) - 1

        ;ConsoleWrite($aResult[$i] & @CRLF)
        ;IniWriteSection("prosjekter.INI","Prosjektnummer",$aResult[$i])
        IniWrite("prosjekter.INI", "Prosjektnummer", $aResult[$i], "")




    Next

EndFunc   ;==>hentemappe

Func mappevalg()
    ; Create a constant variable in Local scope of the message to display in FileOpenDialog.
    Local Const $sMessage = "Select a single file of any type."

    ; Display an open dialog to select a file.
    Local $sFileOpenDialog = FileOpenDialog($sMessage, @WindowsDir & "\", "All (*.*)", $FD_FILEMUSTEXIST)
    If @error Then
        ; Display the error message.
        MsgBox($MB_SYSTEMMODAL, "", "No file was selected.")

        ; Change the working directory (@WorkingDir) back to the location of the script directory as FileOpenDialog sets it to the last accessed folder.
        FileChangeDir(@ScriptDir)
    Else
        ; Change the working directory (@WorkingDir) back to the location of the script directory as FileOpenDialog sets it to the last accessed folder.
        FileChangeDir(@ScriptDir)

        ; Replace instances of "|" with @CRLF in the string returned by FileOpenDialog.
        $sFileOpenDialog = StringReplace($sFileOpenDialog, "|", @CRLF)

        ; Display the selected file.
        ;MsgBox($MB_SYSTEMMODAL, "", "You chose the following file:" & @CRLF & $sFileOpenDialog)
        IniWrite("programbane.INI", "programbane", "adresse", $sFileOpenDialog)
        Sleep(200)
        $FILBANEVALGT = GUICtrlCreateLabel($sFileOpenDialog, 392, 16, 460, 25)
    EndIf



EndFunc   ;==>mappevalg



Func startfil()
    ;trigger innlastning av data til arket ved åpning


    Local $aButton[20]
    For $i = 0 To UBound($aButton) - 1
        $aButton[$i] = GUICtrlCreateButton("", 10, 10 + $i * 25, 80, 20)
        GUICtrlSetState(-1, $GUI_HIDE)
    Next

    GUISetState()

    For $i = 0 To 6
        GUICtrlSetData($aButton[$i], "Number " & $i + 1)
        GUICtrlSetState($aButton[$i], $GUI_SHOW)
    Next

    Local $nMsg

    While True
        $nMsg = GUIGetMsg()
        Switch $nMsg
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $aButton[0] To $aButton[UBound($aButton) - 1]
                ConsoleWrite("Button " & $nMsg - $aButton[0] + 1 & " has been pressed" & @CRLF)
        EndSwitch
    WEnd
EndFunc   ;==>startfil

 

Edited February 5, 2022 by AndreasNWWWWW

[Nine]

Agree with you, it would be easier to pull the information directly from the excel sheet, instead of managing a redondant ini file.  Only question, is it possible that someone would want to access the xls file while your script is running ?

ps. please tidy your code before posting (Ctrl+t), it will make your script easier to read.  Thanks.

Edited February 5, 2022 by Nine

[AndreasNWWWWW]

17 minutes ago, Nine said:

Agree with you, it would be easier to pull the information directly from the excel sheet, instead of managing a redondant ini file.  Only question, is it possible that someone would want to access the xls file while your script is running ?

ps. please tidy your code before posting (Ctrl+t), it will make your script easier to read.  Thanks.

cleaned up the script in previous post as suggested. 😃

and no, the Excel sheet is local only.

[Nine]

Thanks for the clean up.  Then use Excel sheet instead of ini.

[AndreasNWWWWW]

59 minutes ago, Nine said:

Thanks for the clean up.  Then use Excel sheet instead of ini.

but then i have a small question, how can i connect the excel array towards naming and the amount of buttons?

i have tried using the

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A"), 1)

and then use the $aResult to name them one by one, but without luck.

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <Excel.au3>
#include <GuiListView.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <GuiEdit.au3>
#include <Constants.au3>
#include <Misc.au3>
Global $count = 0



#Region ### START Koda GUI section ### Form=
$Form1_1 = GUICreate("Tidsrapport", 1273, 433, 192, 124)
$Label1 = GUICtrlCreateLabel("Time Tracker", 142, 8, 120, 29)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Label4 = GUICtrlCreateLabel("00:00:00", 72, 40, 244, 84)
GUICtrlSetFont(-1, 50, 400, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0x000000)
$Label2 = GUICtrlCreateLabel("Prosjektnummer", 87, 90, 214, 29)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Input1 = GUICtrlCreateInput("", 96, 130, 201, 21)
$Label3 = GUICtrlCreateLabel("Prosjekter registrert", 120, 162, 130, 29)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$ListView1 = GUICtrlCreateListView("Prosjektnummer|Start tid|Slutt tid|Total tid", 8, 194, 385, 175)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 0, 50)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 1, 50)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 2, 50)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 3, 50)
$ListView1_0 = GUICtrlCreateListViewItem("|hour:mintm", $ListView1)
$Button1 = GUICtrlCreateButton("Start", 24, 376, 89, 41, $BS_NOTIFY)
GUICtrlSetCursor(-1, 0)
$Button2 = GUICtrlCreateButton("Eksporter til Excel", 152, 376, 89, 41, $BS_NOTIFY)
GUICtrlSetCursor(-1, 0)
$Button3 = GUICtrlCreateButton("Avbryt", 280, 376, 81, 41, $BS_NOTIFY)
GUICtrlSetCursor(-1, 0)
$SETTFIL = GUICtrlCreateButton("SETT FILBANE", 344, 40, 115, 25)
$OPPDATERFIL = GUICtrlCreateButton("OPPDATER FILBANE", 344, 72, 115, 25)
$FILBANE = GUICtrlCreateLabel("FILBANE:", 344, 24, 51, 17)

;$FILBANEVALGT = GUICtrlCreateLabel("sFileOpenDialog", 392, 16, 460, 25)

GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

;create buttons and name them
$nMsg = GUIGetMsg()
Local $aButton[20]
For $i = 0 To UBound($aButton) - 1
    $aButton[$i] = GUICtrlCreateButton("", 528, 60 + $i * 40, 200, 40)
    GUICtrlSetState(-1, $GUI_HIDE)
Next

GUISetState()

For $i = 0 To 6
    GUICtrlSetData($aButton[$i], "Number " & $i + 1)
    GUICtrlSetState($aButton[$i], $GUI_SHOW)
Next
;startfil()


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit


        Case $SETTFIL
            mappevalg()

        Case $OPPDATERFIL
            hentemappe()

            ;Project button pressed
        Case $aButton[0] To $aButton[UBound($aButton) - 1]
            ConsoleWrite("Button " & $nMsg - $aButton[0] + 1 & " has been pressed" & @CRLF)
    EndSwitch

WEnd




Func hentemappe()
    $programbane = IniRead("programbane.INI", "programbane", "adresse", "")
    Local $oExcel = _Excel_Open()
    Local $bReadOnly = False
    Local $bVisible = True
    Local $bsave = True
    Local $var = 1
    Local $R = "D" & $var & ":" & "E" & $var
    ; ****************************************************************************
    ; Open an existing workbook and return its object identifier.
    ; *****************************************************************************

    Local $sWorkbook = $programbane
    Local $inifilbane = @ScriptDir

    Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, $bReadOnly, $bVisible)
    _Excel_BookClose($oExcel, $bsave)
    Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A"), 1)
    ;Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:B"), 1)
    _ArrayDisplay($aResult)
    ;MsgBox(1,"",$aResult)
    For $i = 1 To UBound($aResult) - 1

        ;ConsoleWrite($aResult[$i] & @CRLF)
        ;IniWriteSection("prosjekter.INI","Prosjektnummer",$aResult[$i])
        IniWrite("prosjekter.INI", "Prosjektnummer", $i, $aResult[$i])




    Next

EndFunc   ;==>hentemappe

Func mappevalg()
    ; Create a constant variable in Local scope of the message to display in FileOpenDialog.
    Local Const $sMessage = "Select a single file of any type."

    ; Display an open dialog to select a file.
    Local $sFileOpenDialog = FileOpenDialog($sMessage, @WindowsDir & "\", "All (*.*)", $FD_FILEMUSTEXIST)
    If @error Then
        ; Display the error message.
        MsgBox($MB_SYSTEMMODAL, "", "No file was selected.")

        ; Change the working directory (@WorkingDir) back to the location of the script directory as FileOpenDialog sets it to the last accessed folder.
        FileChangeDir(@ScriptDir)
    Else
        ; Change the working directory (@WorkingDir) back to the location of the script directory as FileOpenDialog sets it to the last accessed folder.
        FileChangeDir(@ScriptDir)

        ; Replace instances of "|" with @CRLF in the string returned by FileOpenDialog.
        $sFileOpenDialog = StringReplace($sFileOpenDialog, "|", @CRLF)

        ; Display the selected file.
        ;MsgBox($MB_SYSTEMMODAL, "", "You chose the following file:" & @CRLF & $sFileOpenDialog)
        IniWrite("programbane.INI", "programbane", "adresse", $sFileOpenDialog)
        Sleep(200)
        $FILBANEVALGT = GUICtrlCreateLabel($sFileOpenDialog, 392, 16, 460, 25)
    EndIf



EndFunc   ;==>mappevalg



Func startfil()
    ;trigger innlastning av data til arket ved åpning


    Local $aButton[20]
    For $i = 0 To UBound($aButton) - 1
        $aButton[$i] = GUICtrlCreateButton("", 10, 10 + $i * 25, 80, 20)
        GUICtrlSetState(-1, $GUI_HIDE)
    Next

    GUISetState()

    For $i = 0 To 6
        GUICtrlSetData($aButton[$i], "Number " & $i + 1)
        GUICtrlSetState($aButton[$i], $GUI_SHOW)
    Next

    Local $nMsg

    While True
        $nMsg = GUIGetMsg()
        Switch $nMsg
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $aButton[0] To $aButton[UBound($aButton) - 1]
                ConsoleWrite("Button " & $nMsg - $aButton[0] + 1 & " has been pressed" & @CRLF)
        EndSwitch
    WEnd
EndFunc   ;==>startfil

 

[Nine]

Hard to say without seeing the excel file.  Add it on your next post, so we can have a look.  Please make a small script just reading that portion of the sheet instead of having your full script with too much not needed.  Thanks.

[AndreasNWWWWW]

8 minutes ago, Nine said:

Hard to say without seeing the excel file.  Add it on your next post, so we can have a look.  Please make a small script just reading that portion of the sheet instead of having your full script with too much not needed.  Thanks.

;Trying to read ini file - Start
Local Const $sFilePath = @ScriptDir & "\prosjekter.ini"
$givename=IniRead($sFilePath, "Prosjektnummer","1","")
;tried using the ini file - Stops

;create buttons and name them
$nMsg = GUIGetMsg()
Local $aButton[20]
For $i = 0 To UBound($aButton) - 1
    $aButton[$i] = GUICtrlCreateButton("", 528, 60 + $i * 40, 200, 40)
    GUICtrlSetState(-1, $GUI_HIDE)
Next

GUISetState()

For $i = 0 To 6
    GUICtrlSetData($aButton[$i], "Number " & $givename & $i+1)
    GUICtrlSetState($aButton[$i], $GUI_SHOW)
Next

The section above is what i need to be able to read the excel sheet, atm i tried reading the ini file but without luck.

The Excel file is simple, one title cell and then all projects are written down below. atm only 6. but that will increase in time (so it the buttons needs to be dynamic)

the code i'm using to read the excel file is connected to a button as a function,and then writes it into an ini file(first thing i tried)

 

-> in general: all i need is a way to read the excel sheet from A2 and below, create as many buttons as needed, then update the name on the buttons to the corresponding cell name, and have say 8 buttons per row like the image attached below.
 

Func hentemappe()
    $programbane = IniRead("programbane.INI", "programbane", "adresse", "")
    Local $oExcel = _Excel_Open()
    Local $bReadOnly = False
    Local $bVisible = True
    Local $bsave = True
    Local $var = 1
    Local $R = "D" & $var & ":" & "E" & $var
    ; ****************************************************************************
    ; Open an existing workbook and return its object identifier.
    ; *****************************************************************************

    Local $sWorkbook = $programbane
    Local $inifilbane = @ScriptDir

    Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, $bReadOnly, $bVisible)
    _Excel_BookClose($oExcel, $bsave)
    Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A"), 1)
    ;Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:B"), 1)
    _ArrayDisplay($aResult)
    ;MsgBox(1,"",$aResult)
    For $i = 1 To UBound($aResult) - 1

        ;ConsoleWrite($aResult[$i] & @CRLF)
        ;IniWriteSection("prosjekter.INI","Prosjektnummer",$aResult[$i])
        IniWrite("prosjekter.INI", "Prosjektnummer", $aResult[$i],"")




    Next

EndFunc   ;==>hentemappe

Nytt Microsoft Excel-regneark.xlsx

Edited February 5, 2022 by AndreasNWWWWW

[Nine]

When I say make a small script so we can test it easily, this is what I meant :

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Nytt Microsoft Excel-regneark.xlsx", True)
Local $iLast = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeLastCell).row
ConsoleWrite($iLast & @CRLF)

Local $aResult = _Excel_RangeRead($oWorkbook, Default, "A1:A" & $iLast)
_ArrayDisplay($aResult)

_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

So everyone can run and test it without rewriting it from scratch.  Please do similar next time, it is saving us time and effort.

Now you have an array with the content of your excel, you just need to copy it into the button array.

ps.  just noticed that you could simply use :

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange)

Since there is nothing else around...

Edited February 5, 2022 by Nine

[AndreasNWWWWW]

1 hour ago, Nine said:

When I say make a small script so we can test it easily, this is what I meant :

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Nytt Microsoft Excel-regneark.xlsx", True)
Local $iLast = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeLastCell).row
ConsoleWrite($iLast & @CRLF)

Local $aResult = _Excel_RangeRead($oWorkbook, Default, "A1:A" & $iLast)
_ArrayDisplay($aResult)

_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

So everyone can run and test it without rewriting it from scratch.  Please do similar next time, it is saving us time and effort.

Now you have an array with the content of your excel, you just need to copy it into the button array.

ps.  just noticed that you could simply use :

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange)

Since there is nothing else around...

That's noted!   and sorry for posting 60 lines of code, with notes in a different language

So now i have the buttons done, i can read the number of lines with $iLast.
but there is something i am missing, if i change the $aresult[1,2,3,etc] i can read the different values, but it seems it wont go through the list.

i have read on the wikki for arrays, but i can't make head or tails from it (https://www.autoitscript.com/wiki/Arrays)

For $i = 0 to $iLast-1
  GUICtrlSetData($aButton[$i], $aresult[0] )
  GUICtrlSetState($aButton[$i], $GUI_SHOW)

Next

 

[Nine]

Probably this :

For $i = 0 to Ubound($aResult) - 1
  GUICtrlSetData($aButton[$i], $aresult[$i])
  GUICtrlSetState($aButton[$i], $GUI_SHOW)
Next

See my edit above, you can simplify the reading of the excel sheet...

[AndreasNWWWWW]

10 minutes ago, Nine said:

Probably this :

For $i = 0 to Ubound($aResult) - 1
  GUICtrlSetData($aButton[$i], $aresult[$i])
  GUICtrlSetState($aButton[$i], $GUI_SHOW)
Next

See my edit above, you can simplify the reading of the excel sheet...

That worked like a charm, thanks alot

 

[Nine]

[AndreasNWWWWW]

30 minutes ago, Nine said:

i know i marked it as solved, but just had a little tiny question i have incorperated the script into the timer and looks like this now.

 

but i have the same problem as before when trying to input the text into the input bar. it only takes one value for all buttons.

this is the top of the script, so it creates the buttons and  name them correctly according to the excelsheethowever when i push the corresponding button
(last line) it only inputs one value and not the corresponding, i know i could make a case button1,2,3 up to 20, but is there a simpler way? like the $i way you posted earlier?

 

last question, i promise

For $i = 0 to Ubound($aResult) - 1

Local $aButton[20]
For $i = 0 To UBound($aButton) - 1
  $aButton[$i] = GUICtrlCreateButton("", 518, 70 + $i * 40, 80, 40)
  GUICtrlSetState(-1, $GUI_HIDE)
Next

GUISetState()

For $i = 1 to Ubound($aResult) - 1
  GUICtrlSetData($aButton[$i], $aresult[$i])
  GUICtrlSetState($aButton[$i], $GUI_SHOW)
Next





While 1
    $x = Int(Stopwatch() / 100)
    $nMsg = GUIGetMsg()
    Switch $nMsg
                    ;Project button pressed
        Case $aButton[0] To $aButton[UBound($aButton) - 1]

            ConsoleWrite("Button " & $nMsg - $aButton[0] + 1 & " has been pressed" & @CRLF)
            GUICtrlSetData ( $Input1,$aresult[$i])

 

[Nine]

Case $aButton[0] To $aButton[UBound($aButton) - 1]
            ConsoleWrite("Button " & $nMsg - $aButton[0] + 1 & " has been pressed" & @CRLF)
            GUICtrlSetData($Input1,$aResult[$nMsg - $aButton[0]]) ; not sure if you need + 1 here

?