calling the files from an array

[don00]

Hello;

I would appreciate your assistance, now after i chose the folder path, the array display will show all the excel files inside this folder, lets say there are 4 excel files inside this chosen folder, how i could pick and call each excel file inside this array to do specific tasks later on it?

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <File.au3>
#include <Array.au3>


MAIN()


Func MAIN()


    Local $sFolderPath = FileSelectFolder("Please choose your Folder", "")

    Local $aFileList = _FileListToArray($sFolderPath, "*.xls*")

    _ArrayDisplay($aFileList)

 

[Marc]

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <File.au3>
#include <Array.au3>


MAIN()


Func MAIN()
    Local $sFolderPath = FileSelectFolder("Please choose your Folder", "")
    Local $aFileList = _FileListToArray($sFolderPath, "*.xls*")

    for $i = 1 to UBound($aFileList)-1
        ConsoleWrite($sFolderPath & "\" & $aFileList[$i] & @CRLF)
        ShellExecute($sFolderPath & "\" & $aFileList[$i])
        Sleep(500)
    Next
EndFunc

for example, this way  

the "sleep(500)" is needed at least on my PC here, otherwise excel would not open all files.

[FrancescoDiMuro]

@don00

In this case, you have a 1-dimension array, which is a set of elements you can access specifying which element do you need to.

Array elements starts from 0 to the upper bound (-1) of the array, which you can obtain using UBound() function.

So, to pick up each Excel file and use it later on in your script, you should use a For...Next loop:

For $i = 1 To $aFileList[0] Step 1
    ; Process Excel file here, i.e. with _Excel* functions
    $oWorkbook = _Excel_BookOpen($aFileList[$i])
Next

In this specific case, the UBound() function is not needed since you already have the UBound() stored in the first element of the array.

Hope it makes the things clearer.

EDIT: @Marc

If you use UBound() - 1 you are not picking the last element in your array, which, in this case, is a filename
Sorry!
I made confusion with VBA UBound(), which returns the upper bound of the array starting from 0, instead of AutoIt UBound, which returns the number of elements (in this case)

Cheers

Edited March 9, 2021 by FrancescoDiMuro

[don00]

thank you for your kind reply, i just have an error. i have attached the screenshots for both, the error i got from the FOR loop, also the file list display array just to show how many files inside. to be able to call them correctly. these files are the once i need to call one by one to proceed specific tasks on them.

 

[Marc]

@FrancescoDiMuro

sorry, but I have to disagree. Having two xls-Files in my folder, Ubound() returns three. And both files get opened 

@don00

the help file says:

_Excel_BookOpen ( $oExcel, $sFilePath [, $bReadOnly = False [, $bVisible = True [, $sPassword = Default [, $sWritePassword = Default [, $bUpdateLinks = Default]]]]] )

So you'd need to use something like (just copied from the help file)

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Open an existing workbook and return its object identifier.
Local $sWorkbook = @ScriptDir & "\Extras\_Excel1.xls"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

 

Edited March 9, 2021 by Marc

[don00]

Thank you for your kind support.