aArray headers Always transferred to existing Excel spreadsheet.

[JohnnyTries]

Hi Fellow Automators,

Long time listener, first time caller. I've resisted posting on the forums as long as possible for fear of public lynching, but I'm stuck and could really use some help.

Note: If this is the wrong side of the forum for this topic, I apologize.

I've built a GUI and script to make our lab data collection easier. The app has a number of input boxes and a 'record' button. The user fills out the input boxes with various notes and then presses 'record', which in turn presses 'record' on two other, separate apps simultaneously, pulls those recordings together into one folder, and then takes the text from the input boxes and adds it in a new row at the bottom of an existing .xlsx spreadsheet. Everything works great, except that every time I open the app to start collecting data for the day, the 'headers' for the $aArray are added to a new row and then the text is added below it. Now, if I don't close the app between collections, subsequent 'recordings' are added to the spreadsheet as expected. If I close the app and open it, the 'first' recording of the day adds the headers to a new row. I don't need new 'header' info because I've already got that in row 1 of the spreadsheet.

If someone could tell me where I'm !#$%ing up, I would greatly appreciate it. 
 

expandcollapse popup

#RequireAdmin
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <WindowsConstants.au3>
#include <String.au3>
#include <Process.au3>
#include <FileConstants.au3>
#include <WinAPIFiles.au3>
#include <DirConstants.au3>
#include <Array.au3>
#include <AutoItConstants.au3>
#include <File.au3>
#include <WinAPIShPath.au3>
#include <Excel.au3>

;Declaring the $aArray and location of the speadsheet at the top of the script
Local $aArray[1][9] = [["TestID","DateTimeStamp","Tamb_C","BGTemp_C", "GasType", "TrueFlow_slm", "Lens-BGDist_in", "Lens-LeakDist_In", "AddNotes"]]
Local $sDataFilePath = @ScriptDir & "\Notes\DualCaptureNotes.xlsx"


Func CaptureVideo()
    Global $TestID = GuiCtrlRead($TestIDInput)
    Global $timestamp = @YEAR & "-" & @MON & "-" & @MDAY & "-" & @HOUR & "-" & @MIN & "-" & @SEC
    Global $Cam1Dir = "C:\Archive"
    Global $Cam2Dir = "C:\ALD"

;Capture Cam1 Data
    WinActivate("Cam1 App")
    ControlFocus("Cam1 App", "Save",'WindowsForms10.Window.8.app.0.2099316_r7_ad114')
    ControlSend("Cam1 App", "Save", 'WindowsForms10.Window.8.app.0.2099316_r7_ad114', "{SPACE}")

;Capture Cam2 Data
    WinActivate("Cam2 App")
    ControlClick("Cam2 App", "", 'WindowsForms10.Window.8.app.0.1b0ed41_r7_ad122', '', 1, 10, 10)

;Wait to ensure data files have been fully written to their default locations
    Sleep(2000)

;Self-explanatorily named functions
    MoveData()
    RecordNotesToArray()
    RecordArraytoExcel()
    WinActivate("DualCapture")
EndFunc   ;==>CaptureVideo

;Skipping ahead to the .xlsx part....

Func RecordNotesToArray()
    _ArrayAdd($aArray, GUICtrlRead($TestIDInput) & "|" & $timestamp & "|" & GUICtrlRead($Tamb_CInput) & "|" & GUICtrlRead($BGTemp_CInput) & "|" & GUICtrlRead($GasTypeInput) & "|" & GUICtrlRead($TrueFlow_slmInput) & "|" & GUICtrlRead($Dist_BG_inInput) & "|" & GUICtrlRead($Dist_Leak_inInput) & "|" & GUICtrlRead($AddNotesInput))
EndFunc   ;==>RecordNotesToArray()

Func RecordArraytoExcel()
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    $oWorkBook.Worksheets("DataTable").Columns("A:I").AutoFit

    $LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row
    $Rowrange = "A"&$LastRow+1
    Consolewrite($Rowrange & @crlf)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, $Rowrange)



    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>RecordArrayToExcel()

I appreciate your time and any help you can provide.

 

Best,
Johnny

 

[Danp2]

I can't see it in the code you posted, but I'm guessing that you are clearing the contents of $aArray somewhere. Otherwise, the array would continually grow and the contents would be inserted over and over, correct?

If you don't want to insert the header, then delete the first row where you've preloaded the header text.

[JohnnyTries]

Hey Dan, thanks for replying!

49 minutes ago, Danp2 said:

Otherwise, the array would continually grow and the contents would be inserted over and over, correct?

From  my understanding - declaring $aArray as Local is what keeps the 'meaningful content' from growing with each subsequent run. If I just declared an $aArray variable or made it Static $aArray, it would keep growing. 
From the wiki:

Quote

A Local variable exists only within the function in which it is declared and is destroyed when the function terminates.

 

 

49 minutes ago, Danp2 said:

If you don't want to insert the header, then delete the first row where you've preloaded the header text.

I thought declaring the $aArray with headers at the beginning 'headers' was required.
So do I just get rid of everything after the [9] on the first line?

Also - I'm not 100% sure what the [1] [9] stands for - but based on the examples I've seen everywhere else on the forums, I assume it means something along the lines of "1 dimension with 9 parts (or whatever you call each section)". Am I in the ballpark with that?

 

Unrelated sidenote: #GeauxTigers 😜

[Danp2]

2 hours ago, JohnnyTries said:

From  my understanding - declaring $aArray as Local is what keeps the 'meaningful content' from growing with each subsequent run.

That's true. However, your declaration as shown wasn't within a function. Therefore, these variables are treated as if they were declared as Global.

2 hours ago, JohnnyTries said:

I thought declaring the $aArray with headers at the beginning 'headers' was required.
So do I just get rid of everything after the [9] on the first line?

No, you could just declare an empty array like this --

Local $aArray[0][9]

From the help file --

Quote

subscript

- The number of elements to create for the array dimension, indexed 0 to n-1.

So the above creates an empty, two dimensional array where the first dimension is 0 and the second dimension is 9. If you think of it like a spreadsheet, you start with zero rows and nine columns. After the _ArrayAdd, the row count would go up by 1.

There something off between the code you posted and the behavior you described. Hopefully this information will assist you in diagnosing the situation.

P.S. WDE! 😉

[JohnnyTries]

On 3/15/2023 at 1:51 PM, Danp2 said:

Otherwise, the array would continually grow and the contents would be inserted over and over, correct?

You're 100% right. The $aArray wasn't being destroyed between runs. Not sure how I didn't see it in the spreadsheet before, but every subsequent run was adding 'duplicates' of the header row as well as the previous runs PLUS the new (current) run. Don't I feel dumb. 🥴

 

Quote

So the above creates an empty, two dimensional array where the first dimension is 0 and the second dimension is 9. If you think of it like a spreadsheet, you start with zero rows and nine columns. After the _ArrayAdd, the row count would go up by 1.

Thank you! I've read through forums so many times that my brain turned to mush, but you've explained it in extremely relatable terms. Wow... Yeah, now it's making total sense and I'm catching on.

22 hours ago, Danp2 said:

There something off between the code you posted and the behavior you described. Hopefully this information will assist you in diagnosing the situation.

Correct. Something I read on another thread had me thinking the $aArray needed to be declared at the beginning of the script, at or before the GUI loaded--So that's where I put it, plus Nothing was emptying the array so it kept growing with each run. Oof.

The fix:

1. Move the declaration of the $aArray down to the function for recording the inputboxes to the $aArray, and set it as Global.
2. Change $aArray dimensions to [0] [9] with no headers because that's unnecessary with an existing sheet.
3. Once $aArray is passed to the function for writing the $aArray to the spreadsheet, I then simply make the $aArray variable = 0, thus destroying 'old' data.

Section of corrected code:

Func RecordNotesToArray()
    Global $aArray[0][9] ;= [["TestID","DateTimeStamp","Tamb_C","BGTemp_C", "GasType", "TrueFlow_slm", "Lens-BGDist_in", "Lens-LeakDist_In", "AddNotes"]]
    _ArrayAdd($aArray, GUICtrlRead($TestIDInput) & "|" & $timestamp & "|" & GUICtrlRead($Tamb_CInput) & "|" & GUICtrlRead($BGTemp_CInput) & "|" & GUICtrlRead($GasTypeInput) & "|" & GUICtrlRead($TrueFlow_slmInput) & "|" & GUICtrlRead($Dist_BG_inInput) & "|" & GUICtrlRead($Dist_Leak_inInput) & "|" & GUICtrlRead($AddNotesInput))
EndFunc   ;==>RecordNotesToArray()

Func RecordArraytoExcel()
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    $oWorkBook.Worksheets("DataTable").Columns("A:I").AutoFit

    $LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row
    $Rowrange = "A"&$LastRow+1
    Consolewrite($Rowrange & @crlf)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, $Rowrange)



    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
    $aArray = 0
EndFunc   ;==>RecordArrayToExcel()

Everything works like a champ!

I appreciate your time, help, and patience with me, @Danp2!

 

Now I'm going back to digging through the forum again. I'm going to go add some more functionality:

1. Add an option to auto-increment the "Test ID" input box after recording.
2. Have the form save the text from all input boxes so when you close and reopen the app, it will be pre-filled with the info from the previous data collection.

Thanks again and see you around!