Jump to content

aArray headers Always transferred to existing Excel spreadsheet.


Go to solution Solved by Danp2,

Recommended Posts

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

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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 😜

Link to post
Share on other sites
  • Solution
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! 😉

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

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By LeeSG
      (If translated with a translator, it may be written a little awkwardly. I would be grateful if you could understand my situation)
      As mentioned in the title, the icon file, which was applied well when compiled with exe, does not apply when compiled with a3x, and is displayed as the default autoit icon.
       
      #AutoIt3Wrapper_Icon=icon.ico
       
      I specified the icon file at the top, but why can't it be applied? Is there any way to compile with a3x including icons?
    • By HoangDung
      This is the function that returns the result from cmd, initially i connect to the network wait then i make a call to the above _GetDOSOutput($sCommand) function i want to wait 1 period of time netsh wlan connect name="name" actually but after starting to execute the netsh wlan show interfaces command i tried adding a timeout command it seems to have ignored the timeout command?
      #include <WindowsConstants.au3> #include <Constants.au3> Func _GetDOSOutput($sCommand) Run('"' & @ComSpec & '" /c ' & $sCommand, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) Local $sOutput = '' Local $iPID = Run('"' & @ComSpec & '" /c ' & $sCommand, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) While 1 $sOutput &= StdoutRead($iPID, False, False) If @error Then ExitLoop EndIf Sleep(10) WEnd Return $sOutput EndFunc Local $sCommand= 'netsh wlan delete profile name="wait" & netsh wlan connect name="name" interface="Wi-fi" & netsh wlan show interfaces' MsgBox(0,0,_GetDOSOutput($sCommand))  
    • By D3fr0s7
      I'm trying to make one tray item delete another, but when I do this, all tray items that were created after the deleted item don't work as intended, as if their controlID's were all shifted down one value, and their corresponding tray items now (after deletion) run the code of the tray item before it. Am I missing something? Is there a better way to accomplish what I'm trying to do?
      #include <TrayConstants.au3> #include <Array.au3> HotKeySet ( "{ESC}", "Abort" ) Opt ( "TrayMenuMode", 3 ) TraySetState($TRAY_ICONSTATE_SHOW) ; Show the tray menu. Global $aTray[8] ; Defines array to hold tray items. $aTray[0] = TrayCreateItem ( "Test 1 (Name Test 5)" ) $aTray[1] = TrayCreateItem ( "Test 2 (Delete Test 5)" ) $aTray[2] = TrayCreateItem ( "Test 3 (Restore Test 5)" ) $aTray[3] = TrayCreateItem ( "Test 4 (Check if Test 5 is blank or space)" ) $aTray[4] = TrayCreateItem ( "Test 5 Delete Me" ) $aTray[5] = TrayCreateItem ( "Test 6 (Check Test 5 Text)" ) $aTray[6] = TrayCreateItem ( "Test 7 (Read Values)" ) $aTray[7] = TrayCreateItem ( "Test 8 (Count Blanks)" ) While 1 Switch TrayGetMsg() Case $aTray[0] ; "Test 1" Change Test 5 Text. If TrayItemGetText ( $aTray[0] ) <> "" Then Global $TrayText = InputBox ( "Test", "Choose text for Test 5", "Test 5 Delete Me" ) TrayItemSetText ( $aTray[4], $TrayText) EndIf Case $aTray[1] ; "Test 2" Deletes "Test 5". If TrayItemGetText ( $aTray[1] ) <> "" Then Global $TrayDeletedName = TrayItemGetText ( $aTray[4] ) TrayItemDelete ( $aTray[4] ) _ArrayInsert ( $aTray, 4 ) EndIf Case $aTray[2] ; "Test 3" Restores "Test 5". If TrayItemGetText ( $aTray[2] ) <> "" Then $aTray[4] = TrayCreateItem ( $TrayDeletedName ) EndIf Case $aTray[3] ; "Test 4" Check if Test 5 value is blank, space, or filled. If TrayItemGetText ( $aTray[3] ) <> "" Then If TrayItemGetText ( $aTray[4] ) = "" Then MsgBox ( 0, "Test", "Test 5 is blank" ) ElseIf TrayItemGetText ( $aTray[4] ) = " " Then MsgBox ( 0, "Test", "Test 5 is not blank (space)" ) Else MsgBox ( 0, "Test", "Test 5 is assigned a value" ) EndIf EndIf Case $aTray[4] ; "Test 5" (Item to test for, during, and after deletion). If TrayItemGetText ( $aTray[4] ) <> "" Then MsgBox ( 0, "Test", "I'm here!" ) EndIf Case $aTray[5] ; "Test 6" Displays Text from Test 5 item. If TrayItemGetText ( $aTray[5] ) <> "" Then $Test5Text = TrayItemGetText ( $aTray[4] ) MsgBox ( 0, "Test", "Test 5 Text: " & $Test5Text ) EndIf Case $aTray[6] ; "Test 7" Displays all item values. If TrayItemGetText ( $aTray[6] ) <> "" Then MsgBox ( 0, "Test", "$aTray[0]: " & $aTray[0] & @CRLF & _ "$aTray[1]: " & $aTray[1] & @CRLF & _ "$aTray[2]: " & $aTray[2] & @CRLF & _ "$aTray[3]: " & $aTray[3] & @CRLF & _ "$aTray[4]: " & $aTray[4] & @CRLF & _ "$aTray[5]: " & $aTray[5] & @CRLF & _ "$aTray[6]: " & $aTray[6] & @CRLF & _ "$aTray[7]: " & $aTray[7] & @CRLF ) EndIf Case $aTray[7] ; "Test 8" Counts all blanks in tray values. If TrayItemGetText ( $aTray[7] ) <> "" Then Global $blankCount = _ArrayFindAll ( $aTray, "" ) If $blankCount = -1 Then If @error = 6 Then MsgBox ( 0, "Test", "Error, No blanks present") EndIf Else MsgBox ( 0, "Test", "# of blanks: " & $blankCount ) EndIf EndIf EndSwitch WEnd Func Abort() Exit EndFunc Here is a test script I created to try to troubleshoot the problem on my own, with no luck. pay specific attention to "Test 2" ($aTray[1]), "Test 5" ($aTray[4]), and how every tray item after "Test 5" ($aTray[4]) behaves after deletion. Clicking "Test 2" will delete tray item "Test 5", after deletion every item runs the code of the tray item that was established before it (ex. "Test 3" and "Test 4" run their respective code, "Test 5" no longer exists, "Test 6" runs "Test 7", "Test 7" runs "Test 8"), and the last item ("Test 8" $aTray[7]) has no effect when the tray item is clicked. 

      I understand that deleting the tray item changes the controlID, but I don't know in what way it does, and therefore how I can fix it to be able to achieve what I want it to. I appreciate any help or guidance with this problem.

      To clarify, what I'm ultimately trying to do is create a 'while' loop with switch case functions that can exist without necessarily being linked to a tray item, so that I can add and delete them at liberty using the script's functions, without having to differentiate switch case functions with if functions (if $aTray[x] exists, then use this set of switch case functions, etc.). 

      Please, I am in pain. Water come school me again pls
    • By PeterVerbeek
      This topic give you access to an AutoIt functions library I maintain which is called PAL, Peter's AutoIt Library. The latest version 1.26 contains 214 functions divided into these topics:
      window, desktop and monitor GUI, mouse and color GUI controls including graphical buttons (jpg, png) GUI numberbox controls for integer, real, binary and hexadecimal input logics and mathematics include constants string, xml string and file string dialogues and progress bars data lists: lists, stacks, shift registers and key maps (a.ka. dictionaries) miscellaneous: logging/debugging, process and system info Change log and files section  on the PAL website (SourceForge).
      A lot of these functions were created in the development of Peace, Peter's Equalizer APO Configuration Extension, which is a user interface for the system-wide audio driver called Equalizer APO.
    • By Zaoka
      HI,
      for couple of years I'm using  Jos script for sending reports, email with excel attachment. But from last week i'm getting this error when sending excel or word attachment
      message has lines too long for transport jpeg, pdf works with no problems, any sugestion ?
×
×
  • Create New...