DigDeep

Read Excel Cell Text

5 posts in this topic

#1 ·  Posted (edited)

I would need some help in getting the below tasks.

1. I am not sure how many rows might be in the column B. Could be 8 or 100

2. Read the Cell Vaue (in text format) from B2:Till End of the column B to look for the word "TRUE".

3. If any cell shows the word "TRUE", it should delete the complete row

4. If any cell shows the word "FALSE", it should leave it until the loop reaches end of the column B.

 

Please help.

Local $sRange5 = "B2:B23"
    Local $sResult = _Excel_RangeRead($oWorkbook, Default, $sRange5, 3)
    While 1
        If $sResult = "FALSE" Then
            MsgBox(0, "", $sResult)
        ElseIf $sResult = "TRUE" Then
            _Excel_RangeDelete($oWorkbook.Worksheets(1), "2:2")
        EndIf
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;~      ContinueLoop
    WEnd

value.jpg

Edited by DigDeep

Share this post


Link to post
Share on other sites



Check the wiki for how to get the last used cell.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Thought I would but a working script for this issue out here.  Hope no one minds

#include <Excel.au3>
#include <ExcelConstants.au3>

Const $sType = $xlCSVMSDOS
Global $oExcel = _Excel_Open()
Global $sTextFile = "PathtoFolder\temp.csv"
Global $oWorkBook = _Excel_BookOpenText($oExcel, $sTextFile, Default)
Global $iLastRow = $oWorkBook.ActiveSheet.UsedRange.Rows.Count
Global $sString = "String to search for"

For $i = $iLastRow To 1 Step -1
      If _Excel_RangeRead($oWorkbook, Default, "C" & $i) = $sString Then
           $oExcel.Rows($i).Delete
       EndIf
    Next
    
_Excel_BookSaveAs($oWorkbook, "PathtoFolder\temp.csv", $xlCSVMSDOS, True)
$oWorkbook.Close
_Excel_Close($oExcel)

Tested and working with Excel 2013  All of the above was learned by water pointing me in the right direction.  Again a big thank you to water for all the help he gives everyone.

Edited by xcaliber13

Share this post


Link to post
Share on other sites

Please remove line

Local $oExcel = ObjCreate("Excel.Application")

This is done by  _Excel_Open.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - 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