Deleting rows out of excel through _ExcelReadSheetToArray

[JLogan3o13]

Admittedly, I have not done much with the Excel UDF, though what little I have used it has always worked flawlessly. The issue I am encountering below I am pretty sure is more due to the way I am handling the array rather than Excel, but would appreciate any suggestions.

I am reading the necessary rows and colums from a spreadsheet (attached) into an array. I am then cycling through the array to delete rows if they meet certain criteria. I am currently using Ubound, but don't believe I am doing so correctly as the script fails to grab all the rows that match the criteria. I have always stayed away from ReDim, but is this a case where I would have to use it?

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

$oExcel = _ExcelBookOpen(@DesktopDir & "\ACC.xls", 1)
Local $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 3)

For $i = 1 To UBound($aArray) -1
Select
Case StringInStr($aArray[$i][2], "Citrix")
    _ExcelRowDelete($oExcel, $i)
Case StringInStr($aArray[$i][2], "Microsoft")
    _ExcelRowDelete($oExcel, $i)
EndSelect
Next

ACC.xls

[guinness]

This works for me

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

Local $oExcel = _ExcelBookOpen(@DesktopDir & "\ACC.xls", 1)
Local $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 3)

For $i = 1 To $aArray[0][0]
    Select
        Case StringInStr($aArray[$i][2], "Citrix")
            _ExcelRowDelete($oExcel, $i)
        Case StringInStr($aArray[$i][2], "Microsoft")
            _ExcelRowDelete($oExcel, $i)
    EndSelect
Next

_ExcelBookClose($oExcel, 1)

Edit: It isn't as when it deletes the row it's mismatched. Give me 5 mins to tinker unless water the Excel master pops by.

Edited January 23, 2013 by guinness

[jdelaney]

Wouldn't you need to do the loop in reverse? If you delete a row, then the refences will be out of wack between the sheet and your array.

Or, instead of even using the array, why not loop through the sheet it's self?

[iamtheky]

going backwards through your loop seemed to work

edit: late as usual

[size=4]#include <Array.au3>[/size]
#include <Excel.au3>

$oExcel = _ExcelBookOpen(@ScriptDir & "\ACC.xls", 1)
Local $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 3)

For $i = UBound($aArray) -1 to 1 step -1
Select
Case StringInStr($aArray[$i][2], "Citrix")
_ExcelRowDelete($oExcel, $i)
Case StringInStr($aArray[$i][2], "Microsoft")
_ExcelRowDelete($oExcel, $i)
EndSelect
Next

Edited January 23, 2013 by boththose

[JLogan3o13]

That was my first attempt at it. Unfortunately, I get the same results - not all rows containing "Citrix", or all rows containing "Microsoft", are deleted. There are 6 rows matching "Citrix" and 37 matching "Microsoft", but only a total of 36 are deleted.

[JLogan3o13]

I hadn't thought of a reverse loop; that worked great. I need to spend more time thinking outside the box it seems. Thanks, all.

[guinness]

#include <Excel.au3>

Local $oExcel = _ExcelBookOpen(@DesktopDir & "\ACC.xls", 1)
Local $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 3)

Local $iOffset = 0 ; Offset var.
For $i = 1 To $aArray[0][0]
    If StringInStr($aArray[$i][2], "Citrix", 2) Then
        _ExcelRowDelete($oExcel, $i - $iOffset)
        $iOffset += 1
    EndIf
    If StringInStr($aArray[$i][2], "Microsoft", 2) Then
        _ExcelRowDelete($oExcel, $i - $iOffset)
        $iOffset += 1
    EndIf
Next

_ExcelBookClose($oExcel, 0)

[JLogan3o13]

Thanks, guinness. I changed it to case sensitive ("ATI" was deleting rows with the word "installation" in them), and this method works great as well.

[guinness]

I like the reverse loop, but for me it's just easier to understand with a "normal" loop. Great it works.

Yeh, I don't know what I added the case-insensitive flag.

[junkew]

this would be a way without the excel udf directly with the excel object model

;~ 'VBA
;~ '    ws.Range("$A$1:$F$86").AutoFilter Field:=2, Criteria1:="=*Microsoft*", Operator:=xlOr, Criteria2:="=*Citrix*"
;~ '    ws.Range("A2", Range("A2").End(xlDown)).Delete Shift:=xlUp
;~ '    ws.Cells.AutoFilter
const $cXLOR=2
const $cTrue=-1
const $cFalse=0
const $cxlDown=-4121
const $cxlUP=-4162

Local $file = "acc.xls"
Local $xlApp = objcreate("excel.application")

$xlapp.visible=$cTrue

$wb = $xlapp.workbooks.open($file,$cFalse, $cTrue)
$ws = $wb.worksheets(1)
$ws.Range("$A$1:$F$86").AutoFilter(2,"=*Microsoft*", $cxlOr, "=*Citrix*")
$ws.Range("A2", $ws.Range("A2").End($cxlDown)).Delete($cXLUP)
$ws.Cells.AutoFilter

;~ $wb.close(0)