Collapse/ excel rows - The source contains no DataRows


I tried to read this collapsed excel rows and returns some values. It works fine when I expand the rows in advance → save → then run UIpath. However, if I leave the excel collapse as is, it throws me an error saying “The source contains no DataRows”. Why does this happen? Is it not actually reading the file but doing screen scraping??? How to read the actual file instead…?

I thought the robot is reading every row and returns the result but not only on what’s visible… Is there a way make it read the excel even when the excel rows is collapsed? I only want to expand small portion of the excel where it said “Z67 Total” and get the “Z67” rows, but not the entire excel sheet (in the real life scenario, there are a lot of other collapsed rows…) Any idea?

I tried to use hot keys to unclear all the formatting, however, the data table that I input is still the one that I read from the beginning. How to store the data table after hot keys? Please see attachments!

FilterCol&WriteText.xaml (11.1 KB)

Tst.xlsx (23.3 KB)