Jump to content

_Excel_RangeRead in Loop until end of the cell


DigDeep
 Share

Recommended Posts

I have 2 issues here.

1. I have an excel with random rows and column numbers. from the below code, I am able to find that the current Excel has 25 lines. I want to therefore read from A1:A25 to find the text 'True' and delete the complete row.

The code works all fine until if it finds the word 'False' in between it stops looking further. My goal is to keep only the rows with False text and delete all the true ones.

2. Once the Excel is read till the end and all the TRUE ones are deleted, Excel should be closed. But it gives me Variable not declared error because I am keeping _Excel_BookClose($oWorkbook, True) inside the Wend and not outside.

 

Can someone please fix these 2 issues?

#RequireAdmin

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

Global $oExcel = _Excel_Open($Excel)
Global $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "Test.xls")

Local $CountLines = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
$i = 1
While $i <= $CountLines
    Local $CellVal = _Excel_RangeRead($oWorkbook, Default, "A2")
    If $CellVal = 'True' Then
        _Excel_RangeDelete($oWorkbook.ActiveSheet, "2:2", 1)
        Sleep(500)

;~  ElseIf $sResult7 = 'False' Then
        ; do nothing
        ContinueLoop
    EndIf
    $i = $i + 1
    _Excel_BookClose($oWorkbook, True)
WEnd

 

Link to comment
Share on other sites

When deleting records you should always start with the last record:

#RequireAdmin
#include <File.au3>
#include <Excel.au3>
Globa $CellVal
Global $oExcel = _Excel_Open($Excel)
Global $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xls")
Global $CountLines = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
For $i = $Countlines to 1 Step -1
    $CellVal = _Excel_RangeRead($oWorkbook, Default, "A" & $i)
    If $CellVal = 'True' Then
        _Excel_RangeDelete($oWorkbook.ActiveSheet, $i & ":" & $i, 1)
    EndIf
Next
_Excel_BookClose($oWorkbook, True)

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...