Jump to content
AIisKing

Loop through excel rows and delete rows that meet the condition

Recommended Posts

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.

#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

 

Share this post


Link to post
Share on other sites

Either use _Excel_BookOpen or _Excel_BookAttach

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


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