Jump to content

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


Recommended Posts

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")

 

Link to post
Share on other sites

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

Stay innovative!

Spoiler

🌍 Au3Forums

🎲 AutoIt (en) Cheat Sheet

📊 AutoIt limits/defaults

💎 Code Katas: [...] (comming soon)

🎭 Collection of GitHub users with AutoIt projects

🐞 False-Positives

🔮 Me on GitHub

💬 Opinion about new forum sub category

📑 UDF wiki list

✂ VSCode-AutoItSnippets

📑 WebDriver FAQs

👨‍🏫 WebDriver Tutorial (coming soon)

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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 by SOLVE-SMART

Stay innovative!

Spoiler

🌍 Au3Forums

🎲 AutoIt (en) Cheat Sheet

📊 AutoIt limits/defaults

💎 Code Katas: [...] (comming soon)

🎭 Collection of GitHub users with AutoIt projects

🐞 False-Positives

🔮 Me on GitHub

💬 Opinion about new forum sub category

📑 UDF wiki list

✂ VSCode-AutoItSnippets

📑 WebDriver FAQs

👨‍🏫 WebDriver Tutorial (coming soon)

Link to post
Share on other sites

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

#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 by SOLVE-SMART

Stay innovative!

Spoiler

🌍 Au3Forums

🎲 AutoIt (en) Cheat Sheet

📊 AutoIt limits/defaults

💎 Code Katas: [...] (comming soon)

🎭 Collection of GitHub users with AutoIt projects

🐞 False-Positives

🔮 Me on GitHub

💬 Opinion about new forum sub category

📑 UDF wiki list

✂ VSCode-AutoItSnippets

📑 WebDriver FAQs

👨‍🏫 WebDriver Tutorial (coming soon)

Link to post
Share on other sites

@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.

 

 

 

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

We ought not to misbehave, but we should look as though we could.

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

We ought not to misbehave, but we should look as though we could.

Link to post
Share on other sites

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.

We ought not to misbehave, but we should look as though we could.

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.

×
×
  • Create New...