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.
#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
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. 🥴 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. 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: Move the declaration of the $aArray down to the function for recording the inputboxes to the $aArray, and set it as Global. Change $aArray dimensions to [0] [9] with no headers because that's unnecessary with an existing sheet. 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: Add an option to auto-increment the "Test ID" input box after recording. 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!