Jump to content

_Excel_RangeRead until first blank row...


Recommended Posts

Good morning guys, and Happy New Year!!! :sorcerer:

How are you? Hope fine :) I'm here because I'm trying to read an Excel sheet, until the function _Excel_RangeRead meet the first blank line... The issue is:
Even If I have cells without text, but formatted ( Cell Format: Text, Number... ), the function _Excel_RangeRead reads the "blank" cells, and I'd like to avoid this... What can I do in this case? Thanks everyone! :)

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to comment
Share on other sites

  • Moderators

@FrancescoDiMuro you need to show a little patience, you have been around long enough to know you need to wait 24 hours before bumping your posts.

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

Please check the wiki - section "Current Region" - and you will see how to do it.

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

If something goes wrong, please post the code you tried plus all error messages you get - or the result you expect vs. the result you get.

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

I have an Excel sheet.
I want to read ONLY rows that are not empty.
 

Local $aRisultato = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:H"), 1)

Using this, since I modifed some rows below the last NON EMPTY row, the function _Excel_RangeRead, reads those rows.
How can I DO NOT let the function read those rows?
Thanks

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to comment
Share on other sites

2 minutes ago, FrancescoDiMuro said:

I have an Excel sheet.
I want to read ONLY rows that are not empty.
 

Local $aRisultato = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:H"), 1)

Using this, since I modifed some rows below the last NON EMPTY row, the function _Excel_RangeRead, reads those rows.
How can I DO NOT let the function read those rows?
Thanks

My actual result:
A|B|C
1|2|3
Empty|Empty|Empty
My expected result:
A|B|C
1|2|3

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to comment
Share on other sites

I would simply read all rows and ignore the empty ones.

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

1 minute ago, water said:

I would simply read all rows and ignore the empty ones.

How? Something like this?
*After reading the entire sheet with the function I posted above...
 

Local $aNewRisultato
For $i = 2 To UBound($aRisultato) - 1
    If($aRisultato[$i][0]) <> "") Then
        $aNewRisultato = $aRisultato
    EndIf
Next
_ArrayDisplay($aNewRisultato)

 

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to comment
Share on other sites

About how many rows do we talk here?

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

In this case you could simply eliminate all blank rows:

For $i = UBound($aRisultato) - 1 To 2 Step -1 
    If $aRisultato[$i][0]) = "" Then _ArrayDelete($aRisultato, $i)
Next
_ArrayDisplay($aRisultato)

 

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

19 minutes ago, water said:

In this case you could simply eliminate all blank rows:

For $i = UBound($aRisultato) - 1 To 2 Step -1 
    If $aRisultato[$i][0]) = "" Then _ArrayDelete($aRisultato, $i)
Next
_ArrayDisplay($aRisultato)

 

It works perfectly! Thanks water... But, why you do it in reverse? Just my curiosity :) 
PS: Now I'd like to develop the possibility to click on an item of the listeview and let a popup compares... :D

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to comment
Share on other sites

Because you run into the following problem when doing it the other way round:

Imagine row 1 contains data, row 2 and 3 are empty, row 4 contains data. The script would do the following.

Check row 1 - is fine because it contains data - advance to row 2
Check row 2 - gets deleted because it is empty => now row 3 becomes row 2 - advance to row 3
Check row 3 ... wait! What happened to the former row 3 (which became row 2 as stated above)?

This approach would not process all records and crash when you reach the end of the array because the number of records in a for...next statement is only evaluated at the beginning. Later you would need to reduce this number because you delete records from the array.
It's still possible to use this approach but it becomes more complex :)

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

1 minute ago, water said:

Because you run into the following problem when doing it the other way round:

Imagine row 1 contains data, row 2 and 3 are empty, row 4 contains data. The script would do the following.

Check row 1 - is fine because it contains data - advance to row 2
Check row 2 - gets deleted because it is empty => now row 3 becomes row 2 - advance to row 3
Check row 3 ... wait! What happened to the former row 3 (which became row 2 as stated above)?

This approach would not process all records and crash when you reach the end of the array because the number of records in a for...next statement is only evaluated at the beginning. Later you would need to reduce this number because you delete records from the array.
It's still possible to use this approach but it becomes more complex :)

Ahhhhh, ok! Thanks for the explanation! :D Really thanks, not as your "colleagues"... They seems to be angry with other people... :( By the way, I'd like to implementi an interesting thing... Read the PS of my previous post :) Thanks! :D

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to comment
Share on other sites

No one is angry. You were simply pointed to the forum rules and the forum netiquette :)

Your new idea shouldn't be too hard to implement. Good luck :)

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