Need help in reading from an excel sheet and then store it in an array. Need to loop through an array to execute programs listed

[Arun-Emmanuel]

Hello Experts,

I have an excel sheet called Scripts.xlsx and I have a script called run.exe. I need to write a script which needs to read through the excel and store all the list of programs in an Array. I need to run each AutoIT script using RunWait("Program Name"). Whenever I ran my script "Run.exe", I got "Subscript used on non-accessible variable".

This was my old script which did work back in 2015.

#include <Excel.au3>
#include <Array.au3>
$sFilePath1 = @ScriptDir &"\scripts.xls"
$oExcel1 = _ExcelBookOpen($sFilePath1,0)
 Local $aArray = _ExcelReadSheetToArray($oExcel1) ;Using Default Parameters
_ExcelBookClose($oExcel1)
 For $i=1 To $aArray[0][0]

RunWait($aArray[$i][1])
Next
MsgBox(0,"","Automation Completed")

New script: Error: "Subscript used on non-accessible variable".

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Open Workbook 1
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\Scripts.xlsx")
WinActivate($oWorkbook1)

Local $aArray [0]= _Excel_RangeRead($oExcel, Default)
_Excel_Close($oExcel )

For $i=1 To $aArray[0][0]
    RunWait($aArray[$i][1])
Next
MsgBox(0, "Running Scripts", "Automation Completed")

 

[SOLVE-SMART]

Hi @Arun-Emmanuel,

change this line:

Local $aArray [0]= _Excel_RangeRead($oExcel, Default)

to this:

Local $aArray = _Excel_RangeRead($oExcel, Default)

and try again 😀 .

Best regards
Sven

[water]

Local $aArray = _Excel_RangeRead($oExcel, Default)

should be

Local $aArray = _Excel_RangeRead($oWorkbook1, Default)

as the function requires the workbook object as first parameter

Plus

For $i=1 To $aArray[0][0]
    RunWait($aArray[$i][1])
Next

should be

For $i=0 To UBound($aArray) - 1
    RunWait($aArray[$i][1])
Next

as the new Excel UDF now returns a 0-based array.

Since 2015 the Excel UDF has been completely rewritten. What has changed can be found here.

Edited January 27, 2023 by water

[SOLVE-SMART]

You're right @water . I only looked at the first obvious error without having time to test the script.
That's why I love it to work in teams and communities, there is always a sheriff who will review your stuff 😅 .

Best regards
Sven

Edited January 27, 2023 by SOLVE-SMART

[SOLVE-SMART]

As I simply cannot ignore my incomplete suggestion, thank you @water for the appeal to my conscience 😅, I have to post a complete script:

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7
#AutoIt3Wrapper_AU3Check_Stop_OnWarning=y
#AutoIt3Wrapper_Run_Au3Stripper=y
#AutoIt3Wrapper_UseUpx=n
#Au3Stripper_Parameters=/sf /sv /mo /rm /rsln

#include-once
#include "Excel.au3"

_Actions()

Func _Actions()
    Local $oExcel              = _StartExcel()
    Local Const $oWorkbook     = _OpenWorkbook($oExcel)
    Local Const $aContentTable = _GetExcelContent($oExcel, $oWorkbook)

    _Excel_Close($oExcel)
    _RunProgramsFromExcelContent($aContentTable)

    MsgBox(64, 'Running Scripts', 'Automation Completed')
EndFunc

Func _StartExcel()
    Local Const $oExcel = _Excel_Open()

    If @error Then
        MsgBox(16, 'Excel UDF', '@error = "' & @error & '", @extended = "' & @extended)
        Exit
    EndIf

    Return $oExcel
EndFunc

Func _OpenWorkbook($oExcel)
    Local Const $sWorkbook = @ScriptDir & '\Scripts.xlsx'
    Local Const $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

    If @error Then
        MsgBox(16, 'Excel UDF', '@error = "' & @error & '", @extended = "' & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    Return $oWorkbook
EndFunc

Func _GetExcelContent($oExcel, $oWorkbook)
    Local Const $aContentTable = _Excel_RangeRead($oWorkbook)

    If @error Then
        MsgBox(16, 'Excel UDF', '@error = "' & @error & '", @extended = "' & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    Return $aContentTable
EndFunc

Func _RunProgramsFromExcelContent($aTable)
    For $i = 0 To UBound($aTable) - 1
        RunWait($aTable[$i][1])
    Next
EndFunc

Best regards
Sven

Edited January 27, 2023 by SOLVE-SMART

[Arun-Emmanuel]

@water @SOLVE-SMART,

Thanks for your guidance and suggestions. However, when I ran the code written by @SOLVE-SMART I got this error:

Edited January 30, 2023 by Arun-Emmanuel

[Arun-Emmanuel]

@water @SOLVE-SMART,

I also tried with this code and got the similar result as mentioned above.

#include <GUIConstants.au3>
#include <GuiTreeView.au3>
#include <TreeViewConstants.au3>
#include <SendMessage.au3>
#include <WinAPI.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>


#include <GUIConstants.au3>
#include <GuiTreeView.au3>
#include <TreeViewConstants.au3>
#include <SendMessage.au3>
#include <WinAPI.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>


Local $oPenExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    
Local $oPenWorkbook = _Excel_BookOpen($oPenExcel, @ScriptDir & "\Scripts.xlsx")

Local $ListOfPrograms = _Excel_RangeRead($oPenWorkbook)
_ArrayDisplay($ListOfPrograms)

For $i=0 To UBound($ListOfPrograms) - 1
    RunWait($ListOfPrograms[$i][0]) 
    MsgBox(1, "Debug", "Program to run:", $ListOfPrograms[$i][0])

Next

I could verify the items being added to the array using _ArrayDisplay($ListOfPrograms). However, unable to verify the same using MsgBox().
I was also not sure if RunWait() is working. I am expecting RunWait("Program1.exe") to run Program.exe script.

 

 

 

[mistersquirrle]

36 minutes ago, Arun-Emmanuel said:

I could verify the items being added to the array using _ArrayDisplay($ListOfPrograms). However, unable to verify the same using MsgBox().

It would probably be helpful if you provided the output that you get from your xslx range read (what does $ListOfPrograms look like? Can you send a screenshot of the _ArrayDisplay?)

 

Another option would be to just use a simple text file, and read that to an array instead of involving the whole excel stack of things. I also can't help you test your provided code as I don't have Excel and the functions don't work without it.

 

Here's my suggestion, using just a text file:

#include <Array.au3>

Local $hRunTimer = -1
Local $aListOfPrograms = FileReadToArray(@ScriptDir & '\test.txt')
_ArrayDisplay($aListOfPrograms)

For $i = 0 To UBound($aListOfPrograms) - 1
    If FileExists($aListOfPrograms[$i]) = 0 Then
        __cLog('File does not exist: ' & $aListOfPrograms[$i])
        ContinueLoop
    EndIf
    $hRunTimer = TimerInit()
    __cLog('Program to run: ' & $aListOfPrograms[$i])
    RunWait($aListOfPrograms[$i])
    __cLog('    Finished in ' & Round(TimerDiff($hRunTimer), 2) & 'ms')
Next

Func __cLog($sMsg = '')
    ConsoleWrite($sMsg & @CRLF)
EndFunc   ;==>__cLog

 

and the test.txt file:

script1.exe
"c:\script2.exe"
meow.asdf

 

Unless you provide an example .xslx for people to use I don't think there's a lot of help you can get with the Excel functions since we don't know what format you're using. I would still recommend using just a simple text file however. If you need more 'columns' of data, such as parameters for the scripts, I would use a CSV format or something similar and use StringSplit to break it up and get your parameters or other information.

[Arun-Emmanuel]

Hi @mistersquirrle,

Thank you for your suggestion and I will try to use text per your input and see if it fulfils my purpose. 

I have attached the screen capture of the xlsx file and the out of _arrayDisplay()

 

Edited January 30, 2023 by Arun-Emmanuel

[mistersquirrle]

54 minutes ago, Arun-Emmanuel said:

I have attached the screen capture of the xlsx file and the out of _arrayDisplay()

Thanks, I can see now what your problem is. You're using a 2D array syntax for a 1D array. Notice in the ArrayDisplay there's only Col 0. That means it's just 1D, if there was Col 1, Col 2... etc, then you'd have a 2D array (and ArrayDisplay is incapable of displaying 3+D arrays). Here's your code:

Quote

 

For $i=0 To UBound($ListOfPrograms) - 1
    RunWait($ListOfPrograms[$i][0]) 
    MsgBox(1, "Debug", "Program to run:", $ListOfPrograms[$i][0])

Next

 

You should just need to change $ListOfPrograms[$i][0] to: $ListOfPrograms[$i]. It should also be easier to debug this kind of issue if you don't run the script compiled (as an exe). If you run it as the script itself in SciTE it should point out that error a lot more clearly than the compiled exe error message.
 

However, I would still recommend a simple txt file, since it has basically all the functionality that you should need with what you've provided, it's a lot simpler syntax and lighter code, and it's more compatible if you try to run this on a system without Excel.

Edited January 30, 2023 by mistersquirrle

[ioa747]

 

i thing you must give the full path in a1 column e.g.  D:\Pro\Launch-Pronto-Temp.exe

or in  RunWait(@ScriptDir & "\" & $ListOfPrograms[$i][0])

 

Edited January 30, 2023 by ioa747

[ioa747]

@mistersquirrle you missed something i still see 2d in your example

  false alarm

I read carefully

 

Edited January 30, 2023 by ioa747

[mistersquirrle]

Are you referring to my previous comment before this one? I was just quoting the code that @Arun-Emmanuel had posted, with the suggestion of how to fix it below the code snippet. I don't see and 2D array references in the example code that I provided in the comment before that. But I'll update the previous post and put the code snippet inside a quote box to make it more apparent that I was just referencing the example code previous provided.