Calling files to do functions inside an array

[don00]

I have a folder, which contains excel files, all the aim is to call every excel file in the array, and do on them specific tasks (i,e , open the workbook check certain data,  add new sheet...etc). and then save it and close the file,  the only issue is how to combine the (FOR) loop inside the array to execute functions inside each file separately.

 

here is screenshot of the excel files inside the folder which needs to perform the tasks on them:

 

so after i called them in the (FOR) loop , now i need to assign the functions to each file, your advise is greatly appreciated

#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

    local $sFileName= $aFileList[$i]

;~    >> here i need to call each file inside this array, open the workbook, add new worksheet, then save it and close


    Next

 

[dmob]

Create the code to open the file and perform the task for one file; it should then be easier to repeat for all files.

Have a look at _Excel_xxx functions and examples in help file.

[FrancescoDiMuro]

@don00

You already asked for this, and you've given an answer.

[don00]

The  problem is that,  how to combine both together (For loop) inside the array. not just to open the files, for example, here is the full Code for each separate file. but dont know how to enter it in for loop inside the array. apply it, and then save the files before closing.

 

expandcollapse popup

#include <Excel.au3>
#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

    local $sFileName= $aFileList[$i]

 >>> here i need to enter the functions below,, to be able to applied for each file in this folder

    Next



; Create application object
Local $oExcel = _Excel_Open()

_Excel_SheetAdd($_oWorkbook, Default, False, 3, "Target Green|Target Orange|Target Red")

Local $oWsTargetGreen = $_oWorkbook.Worksheets("Target Green")
$oWsTargetGreen.Tab.ColorIndex = 10

Local $oWsTargetOrange = $_oWorkbook.Worksheets("Target Orange")
$oWsTargetOrange.Tab.ColorIndex = 45

Local $oWsTargetRed = $_oWorkbook.Worksheets("Target Red")
$oWsTargetRed.Tab.ColorIndex = 3

Local $oWsSource = $_oWorkbook.Worksheets("Source")


    Local $iLastRow = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Row
    Local $iLastColumn = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Column
    Local $sLastColumn = _Excel_ColumnToLetter($iLastColumn)
    Local $sColumnComment = _Excel_ColumnToLetter($iLastColumn + 1)


    Local $SortData = _Excel_RangeSort($_oWorkbook, $oWsSource, "A2:" & $sLastColumn & $iLastRow, "B:B", $xlDescending)


    Local $iNumberOfRed, $iNumberOfGreen, $iNumberOfOrange

    Local $oRange
    For $i = 2 To $iLastRow

        Local $sTransactionDateTime = $oWsSource.Range("B" & $i).value
        Local $aTransactionDateTime = StringSplit($sTransactionDateTime, " ", 2)

        Local $sSystemcode = $oWsSource.Range("A" & $i).Value
        Local $sExemptcode = $oWsSource.Range($sLastColumn & $i).Value

        $oWsSource.Range("B" & $i).value = $aTransactionDateTime[0]

        If StringInStr($sSystemcode, "SLS", $STR_CASESENSE) Then
            MoveLinesToCorrespondingWorksheet($oWsTargetRed, $sLastColumn, $oWsSource, $i)
        ElseIf StringInStr($sSystemcode, "VNO", $STR_CASESENSE) And $sExemptcode = "" Then
            MoveLinesToCorrespondingWorksheet($oWsTargetGreen, $sLastColumn, $oWsSource, $i)
            _Excel_RangeWrite($_oWorkbook, $oWsSource, "Ok", $sColumnComment & $i)
        Else
            MoveLinesToCorrespondingWorksheet($oWsTargetOrange, $sLastColumn, $oWsSource, $i)
        EndIf
    Next



    MsgBox(0, @ScriptName, "Number of the green items: " & $iNumberOfGreen & @LF & "Number of the red items: " & $iNumberOfRed & @LF & "Number of the red items: " & $iNumberOfRed)



  EndFunc




Func MoveLinesToCorrespondingWorksheet($oWsTarget, $sLastColumn, $oWsSource, $i)
    Local $iLastRow = $oWsTarget.Range("A1").SpecialCells($xlCellTypeLastCell).Row
    Local $oRange = $oWsTarget.Range("A" & $iLastRow + 1 & ":" & $sLastColumn & $iLastRow + 1)

    _Excel_RangeCopyPaste($oWsSource, "A" & $i & ":" & $sLastColumn & $i, $oRange, False, $xlPasteAll)
    $oWsSource.Range("A" & $i & ":" & $sLastColumn & $i).Interior.ColorIndex = $oWsTarget.Tab.ColorIndex
EndFunc   ;==>MoveLinesToCorrespondingWorksheet

 

[FrancescoDiMuro]

@don00
The Excel object doesn't need to be created each time, so you can leave it outside the loop.
For the rest of the code, you can put inside the loop and see what happens.
You need to experiment what you do; only doing that you'll know how your code works

[don00]

Thank you for your kind reply, I'm just new in this field and i try to learn from your advise, also by practicing, i have tried to combine both (FOR) loop inside the array, the only issue now is how to define the filepath (which is supposed to be string) using the file names what has been called from the FileListToArray? for example, i have 4 file names. (Excel1 , Excel2, Excel3 , Excel4). any advise?

 

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


    For $i = 1 To UBound($aFileList) - 1

       ;~ Local $sFilePath= $aFileList[$i] ??  >>> need to get the file path <<<<


         Local $oExcel= _Excel_Open()
         Local $_oWorkbook=_Excel_BookOpen($oExcel,$oFilepath)
         Local $addsheet = _Excel_SheetAdd($_oWorkbook, Default, False, 3, "Target Green|Target Orange|Target Red")

 

[FrancescoDiMuro]

@don00
If you look at the _FileListToArray() function in the Help file, you'll see a third parameter which is $bReturnPath, which will return the full file path of the file(s) found

Edited March 22, 2021 by FrancescoDiMuro

[don00]

oh, dont know why i didnot think about this, thanks alot.

Could you also advise what is wrong here? it says variable must be type of object.

 

[FrancescoDiMuro]

@don00
The function _Excel_SheetAdd() is probably failing to add those sheets.
Put some error checking and see what's going on in your script.
Nice to see that you listened to the suggestion "Leave _Excel_Open() outside the loop".