02-06-2018 10:20 AM
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
Solved! Go to Solution.
02-06-2018 11:25 AM
Do you have the Report Generation Toolkit? It has a function called Excel Get Last Row.
02-06-2018 11:44 AM
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.
02-06-2018 02:21 PM
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
02-06-2018 02:37 PM
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.
02-06-2018 02:51 PM
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
02-06-2018 03:03 PM
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?)
02-07-2018 02:42 AM
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.