LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Solved!
Go to solution
Highlighted

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

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
(563 Views)

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

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

aputman
LabVIEW 2017
LabVIEW Programming
0 Kudos
Message 2 of 8
(550 Views)

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

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
LabVIEW 2017
LabVIEW Programming
0 Kudos
Message 3 of 8
(545 Views)
Solution
Accepted by topic author GoKu25
02-07-2018 12:40 AM

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

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

--------------------------------------------------
The best way to say thanks is to give kudos!
0 Kudos
Message 4 of 8
(528 Views)

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


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
LabVIEW 2017
LabVIEW Programming
Message 5 of 8
(525 Views)

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


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

--------------------------------------------------
The best way to say thanks is to give kudos!
0 Kudos
Message 6 of 8
(522 Views)
Solution
Accepted by topic author GoKu25
02-07-2018 12:40 AM

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

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
(515 Views)

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

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
(499 Views)