LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

Reading UsedRange rows from excel yields unexpected answer from a particular sheet

Solved!
Go to solution

I am trying to append data to exisiting data in a Excel sheet and I am able to do it. But there is always a huge cell gap between the existing data  and newly written data. I have used ActiveX to read the number of used rows from a worksheet. The workbook has 3 sheets and unexpectedly it reads wrong value from just one sheet (Historie) while other 2(Übersicht and testing) are read correctly. I have checked the Vi (check zip) with other excel sheets and it works correctly everytime except for the HIstorie sheet. Any input is appreciated

 

Excel: .xlsx ; Labview access: ActiveX

RowValue.jpg

0 Kudos
Message 1 of 8
(2,807 Views)

Do you have the Report Generation Toolkit?  It has a function called Excel Get Last Row. 

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
0 Kudos
Message 2 of 8
(2,794 Views)

Hmm...I just tried with the Get Last Row Function and it returns row 222....not 203. 

 

If I copy the first 202 rows to a new sheet and run the same code there, it returns 203.  So I'm guessing there is maybe a named range or an image or something in those next 20 rows that is causing the gap. 

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
0 Kudos
Message 3 of 8
(2,789 Views)
Solution
Accepted by GoKu25

UsedRange is ok with a worksheet that was never edited but if you delete rows or columns from an existing worksheet these rows/columns will still be considered as part of the UsedRange. Basically UsedRange is not the same as range with data, once a cell was edited it become part of the UsedRange. You can select rows 203 to 224 of your template, right-click, select delete and save your file. On the next read you will get 202.

 

An alternative is to use the vi is post #9 of this thread.

 

Ben64

0 Kudos
Message 4 of 8
(2,772 Views)

wrote:

UsedRange is ok with a worksheet that was never edited but if you delete rows or columns from an existing worksheet these rows/columns will still be considered as part of the UsedRange. Basically UsedRange is not the same as range with data, once a cell was edited it become part of the UsedRange.


That isn't entirely true based on my trials.  I can add data, save the file and get the last row and it updates.  Delete the data and LastRow still updates.  Now if I change the format of a cell, that causes an entry to be added into the underlying XML file that isn't immediately visible to a user.  And then deleting any data in that cell doesn't delete the formatting.  So that cell is still part of the UsedRange.  

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
Message 5 of 8
(2,769 Views)

wrote:

wrote:

UsedRange is ok with a worksheet that was never edited but if you delete rows or columns from an existing worksheet these rows/columns will still be considered as part of the UsedRange. Basically UsedRange is not the same as range with data, once a cell was edited it become part of the UsedRange.


That isn't entirely true based on my trials.  I can add data, save the file and get the last row and it updates.  Delete the data and LastRow still updates.  Now if I change the format of a cell, that causes an entry to be added into the underlying XML file that isn't immediately visible to a user.  And then deleting any data in that cell doesn't delete the formatting.  So that cell is still part of the UsedRange.  


I'd say you're right, it's something I haven't tried in a while and I remember adding and deleting something to a workshheet that impacted the UsedRange (maybe adding and removing border or backgroung color).

 

Ben64

0 Kudos
Message 6 of 8
(2,766 Views)
Solution
Accepted by GoKu25

What row or cell does the cursor jump to if you used Ctrl-End?

 

If you have truly cleared out anything below, saved, closed, then reopened the file without doing anything else, which row does it now recognize as the last?  (Likewise with Ctrl-End?)

0 Kudos
Message 7 of 8
(2,759 Views)

Thank you all for the input. Now I remember that I deleted some data manually tha I forgot to mention. It works now after clearing it.

 

 

0 Kudos
Message 8 of 8
(2,743 Views)