Jump to content

Recommended Posts

Posted

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:

 

image.png.820e358739f43ca953b495ddd5167132.png

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

 

Posted

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.

Posted

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.

 

#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

 

Posted

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

 

Posted

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.

 

image.thumb.png.f2f9e58b302d44e136a7e6fa6fbbbf7d.png

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