FrancescoDiMuro

_Excel_RangeRead until first blank row...

19 posts in this topic

#1 ·  Posted

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

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites



#2 ·  Posted

Please, don't answer so many! <_<


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

#3 ·  Posted

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


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

Share this post


Link to post
Share on other sites

#4 ·  Posted

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


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

#5 ·  Posted

1 minute ago, water said:

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

Thanks water. But how can I fix that? :/


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

#6 ·  Posted

Fix what? Did you read through the section you were pointed to?


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

Share this post


Link to post
Share on other sites

#7 ·  Posted

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

#8 ·  Posted

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

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

#9 ·  Posted

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

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

#10 ·  Posted

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


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

#11 ·  Posted

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

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

#12 ·  Posted

About how many rows do we talk here?


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

#13 ·  Posted

3 minutes ago, water said:

About how many rows do we talk here?

At the moment, not over 50... Maybe in the future, not over 150.
Ain't no fixed my issue :/
Thanks water...


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

#14 ·  Posted

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

#15 ·  Posted (edited)

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

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

#16 ·  Posted

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

#17 ·  Posted

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

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

#18 ·  Posted

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

#19 ·  Posted

3 minutes ago, water said:

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

Thanks water, and have a good day! :D 


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

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