Loop through excel rows and delete rows that meet the condition

[AIisKing]

Hello,

I want to loop through multiple excel files(dynamic) and delete rows that match the status "Inactive" in $obj_wb excel file(Static). However, the code does not work as intended.

expandcollapse popup

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

Global $g_iCounter = 0


Example()
MsgBox("", "", "Total items deleted: " & $g_iCounter & ".")
Func Example()
    Local $obj_Excel = _Excel_Open()
    Local $sPath = "Removed path"
    Local $obj_wb = _Excel_BookOpen($obj_Excel, $sPath)
    $obj_wb = _Excel_BookAttach($sPath)
    Local $obj_ws = $obj_wb.Worksheets("Sheet1")
    With $obj_ws
        Local $iLastRow = .Cells(.Rows.Count, "A").End(-4162).Row
        Local $aISIN = _Excel_RangeRead($obj_wb, $obj_ws, "A2:A" & $ilastrow)
        Local $aStatus = _Excel_RangeRead($obj_wb, $obj_ws, "B2:B" & $ilastrow)
    EndWith
    Local $sFileSelectFolder = "Removed path"
    $aFileList = _FileListToArray($sFileSelectFolder, "*.xls")
    If @error = 1 Then
        MsgBox(0, "", "No Files\Folders Found.")
        Exit
    EndIf

    For $i = 1 To $aFileList[0]
        Local $iCounter = 0
        $sFile = $sFileSelectFolder & "\" & $aFileList[$i]
        Local $obj_excel_files = _Excel_Open()
        Local $obj_file_wb = _Excel_BookOpen($obj_excel_files, $sFile)
        $obj_file_wb = _Excel_BookAttach($sFile)
        Local $obj_file_ws = $obj_file_wb.Worksheets("Sheet1")

        With $obj_file_ws
            Local $ilastrowFile = .Cells(.Rows.Count, "A").End(-4162).Row
            Local $aISINfile = _Excel_RangeRead($obj_file_wb, $obj_file_ws, "A2:A" & $ilastrowFile)
        EndWith
        For $iRowIndex = UBound($aISINfile)-1 To 1 Step -1
            $k = _ArraySearch($aISIN, $aISINfile[$iRowIndex])
            If $aStatus[$k] = "Inactive" Then
            $obj_file_wb.Activesheet.Range("A" &$iRowIndex+1).EntireRow.Delete
            $g_iCounter += 1
            EndIf
        Next 
        _Excel_BookClose($obj_wb_file, True)
     Next
    _Excel_BookClose($obj_wb, False)
EndFunc

Please advise why the script is not deleting the rows in the loop   For $iRowIndex = UBound($aISINfile)-1 To 1 Step -1

 

BR,
AIisKing

 

[Subz]

Use error checking, I'd look at the results of _ArraySearch, you may need to use partial for $iCompare.

[water]

Either use _Excel_BookOpen or _Excel_BookAttach

Local $obj_wb = _Excel_BookOpen($obj_Excel, $sPath)
    $obj_wb = _Excel_BookAttach($sPath)