Sign in to follow this  
Followers 0
JLogan3o13

Deleting rows out of excel through _ExcelReadSheetToArray

10 posts in this topic

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by guinness

_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_BinaryBin()_CheckMsgBox()_CmdLineRaw()_ContextMenu()_ConvertLHWebColor()/_ConvertSHWebColor()_DesktopDimensions()_DisplayPassword()_DotNet_Load()/_DotNet_Unload()_Fibonacci()_FileCompare()_FileCompareContents()_FileNameByHandle()_FilePrefix/SRE()_FindInFile()_GetBackgroundColor()/_SetBackgroundColor()_GetConrolID()_GetCtrlClass()_GetDirectoryFormat()_GetDriveMediaType()_GetFilename()/_GetFilenameExt()_GetHardwareID()_GetIP()_GetIP_Country()_GetOSLanguage()_GetSavedSource()_GetStringSize()_GetSystemPaths()_GetURLImage()_GIFImage()_GoogleWeather()_GUICtrlCreateGroup()_GUICtrlListBox_CreateArray()_GUICtrlListView_CreateArray()_GUICtrlListView_SaveCSV()_GUICtrlListView_SaveHTML()_GUICtrlListView_SaveTxt()_GUICtrlListView_SaveXML()_GUICtrlMenu_Recent()_GUICtrlMenu_SetItemImage()_GUICtrlTreeView_CreateArray()_GUIDisable()_GUIImageList_SetIconFromHandle()_GUIRegisterMsg()_GUISetIcon()_Icon_Clear()/_Icon_Set()_IdleTime()_InetGet()_InetGetGUI()_InetGetProgress()_IPDetails()_IsFileOlder()_IsGUID()_IsHex()_IsPalindrome()_IsRegKey()_IsStringRegExp()_IsSystemDrive()_IsUPX()_IsValidType()_IsWebColor()_Language()_Log()_MicrosoftInternetConnectivity()_MSDNDataType()_PathFull/GetRelative/Split()_PathSplitEx()_PrintFromArray()_ProgressSetMarquee()_ReDim()_RockPaperScissors()/_RockPaperScissorsLizardSpock()_ScrollingCredits_SelfDelete()_SelfRename()_SelfUpdate()_SendTo()_ShellAll()_ShellFile()_ShellFolder()_SingletonHWID()_SingletonPID()_Startup()_StringCompact()_StringIsValid()_StringRegExpMetaCharacters()_StringReplaceWholeWord()_StringStripChars()_Temperature()_TrialPeriod()_UKToUSDate()/_USToUKDate()_WinAPI_Create_CTL_CODE()_WinAPI_CreateGUID()_WMIDateStringToDate()/_DateToWMIDateString()Au3 script parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 04/09/2015

Share this post


Link to post
Share on other sites

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?


IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by boththose

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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


_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_BinaryBin()_CheckMsgBox()_CmdLineRaw()_ContextMenu()_ConvertLHWebColor()/_ConvertSHWebColor()_DesktopDimensions()_DisplayPassword()_DotNet_Load()/_DotNet_Unload()_Fibonacci()_FileCompare()_FileCompareContents()_FileNameByHandle()_FilePrefix/SRE()_FindInFile()_GetBackgroundColor()/_SetBackgroundColor()_GetConrolID()_GetCtrlClass()_GetDirectoryFormat()_GetDriveMediaType()_GetFilename()/_GetFilenameExt()_GetHardwareID()_GetIP()_GetIP_Country()_GetOSLanguage()_GetSavedSource()_GetStringSize()_GetSystemPaths()_GetURLImage()_GIFImage()_GoogleWeather()_GUICtrlCreateGroup()_GUICtrlListBox_CreateArray()_GUICtrlListView_CreateArray()_GUICtrlListView_SaveCSV()_GUICtrlListView_SaveHTML()_GUICtrlListView_SaveTxt()_GUICtrlListView_SaveXML()_GUICtrlMenu_Recent()_GUICtrlMenu_SetItemImage()_GUICtrlTreeView_CreateArray()_GUIDisable()_GUIImageList_SetIconFromHandle()_GUIRegisterMsg()_GUISetIcon()_Icon_Clear()/_Icon_Set()_IdleTime()_InetGet()_InetGetGUI()_InetGetProgress()_IPDetails()_IsFileOlder()_IsGUID()_IsHex()_IsPalindrome()_IsRegKey()_IsStringRegExp()_IsSystemDrive()_IsUPX()_IsValidType()_IsWebColor()_Language()_Log()_MicrosoftInternetConnectivity()_MSDNDataType()_PathFull/GetRelative/Split()_PathSplitEx()_PrintFromArray()_ProgressSetMarquee()_ReDim()_RockPaperScissors()/_RockPaperScissorsLizardSpock()_ScrollingCredits_SelfDelete()_SelfRename()_SelfUpdate()_SendTo()_ShellAll()_ShellFile()_ShellFolder()_SingletonHWID()_SingletonPID()_Startup()_StringCompact()_StringIsValid()_StringRegExpMetaCharacters()_StringReplaceWholeWord()_StringStripChars()_Temperature()_TrialPeriod()_UKToUSDate()/_USToUKDate()_WinAPI_Create_CTL_CODE()_WinAPI_CreateGUID()_WMIDateStringToDate()/_DateToWMIDateString()Au3 script parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 04/09/2015

Share this post


Link to post
Share on other sites

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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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.


_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_BinaryBin()_CheckMsgBox()_CmdLineRaw()_ContextMenu()_ConvertLHWebColor()/_ConvertSHWebColor()_DesktopDimensions()_DisplayPassword()_DotNet_Load()/_DotNet_Unload()_Fibonacci()_FileCompare()_FileCompareContents()_FileNameByHandle()_FilePrefix/SRE()_FindInFile()_GetBackgroundColor()/_SetBackgroundColor()_GetConrolID()_GetCtrlClass()_GetDirectoryFormat()_GetDriveMediaType()_GetFilename()/_GetFilenameExt()_GetHardwareID()_GetIP()_GetIP_Country()_GetOSLanguage()_GetSavedSource()_GetStringSize()_GetSystemPaths()_GetURLImage()_GIFImage()_GoogleWeather()_GUICtrlCreateGroup()_GUICtrlListBox_CreateArray()_GUICtrlListView_CreateArray()_GUICtrlListView_SaveCSV()_GUICtrlListView_SaveHTML()_GUICtrlListView_SaveTxt()_GUICtrlListView_SaveXML()_GUICtrlMenu_Recent()_GUICtrlMenu_SetItemImage()_GUICtrlTreeView_CreateArray()_GUIDisable()_GUIImageList_SetIconFromHandle()_GUIRegisterMsg()_GUISetIcon()_Icon_Clear()/_Icon_Set()_IdleTime()_InetGet()_InetGetGUI()_InetGetProgress()_IPDetails()_IsFileOlder()_IsGUID()_IsHex()_IsPalindrome()_IsRegKey()_IsStringRegExp()_IsSystemDrive()_IsUPX()_IsValidType()_IsWebColor()_Language()_Log()_MicrosoftInternetConnectivity()_MSDNDataType()_PathFull/GetRelative/Split()_PathSplitEx()_PrintFromArray()_ProgressSetMarquee()_ReDim()_RockPaperScissors()/_RockPaperScissorsLizardSpock()_ScrollingCredits_SelfDelete()_SelfRename()_SelfUpdate()_SendTo()_ShellAll()_ShellFile()_ShellFolder()_SingletonHWID()_SingletonPID()_Startup()_StringCompact()_StringIsValid()_StringRegExpMetaCharacters()_StringReplaceWholeWord()_StringStripChars()_Temperature()_TrialPeriod()_UKToUSDate()/_USToUKDate()_WinAPI_Create_CTL_CODE()_WinAPI_CreateGUID()_WMIDateStringToDate()/_DateToWMIDateString()Au3 script parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 04/09/2015

Share this post


Link to post
Share on other sites

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)

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
Sign in to follow this  
Followers 0