Write range on filtered excel

Build
, , ,
#1

Hi Guys,
I am working on this process where there are 5000+ row item with unique key.This data is stored in an excel which act as log book for the process. The input comes from user via mail and the value is updated according to unique id.
So what I did is used read range activity and got all the data from log book as well as from the input(from mail) from the user.
Log book Datatable: DTLog
User input datatable: DTUser
used nested for each in both datatable and “if” to check unique id and updated the value in the respective row of DTLog datatable.
After the execution of the for loops, I write the datatable to the Log book and thus the values are updated.
The issue sometimes comes up is the log book(excel) is filtered and because of that a lot of garbage value is created. Its like updating the DTLog to filtered excel log book.

Can you help me with how can I check if the excel is filtered or what can I do to remove the garbage value while writing the data back to datatable.

p.s.: I know using nested for loops is bad but what done is done and changing it will be another big headache as there are more things under consideration in the process.

Best Regards
Devbrath

#2

Hi @Devbrath_Rajkhua

Could you give some examples? At first instinct it sounds like a bug when writing to an Excel file.

If it is, I would strongly recommend running a short macro on the file that will remove all filters before reading/writing the files. It is a temporary solution, but should work quite well.

Another way, if you could try it, would be to not use the Excel Application Scope but the workbook activities to write to a file:
image

As to this strange behaviour, I would strongly urge you to try and recreate this scenario in a new project - just the faulty behaviour of the activities. We will gladly fix it!

#3

Hi @loginerror
Thanks for the reply, I have tried the workbook write range activity and it is working fine. There is no issue in writing to excel even if the filters are present in the excel.
I have recreated the same scenario in other projects as well and the problem with excel write range activity is still there. I tried to attach the xaml file and the test file as well to this reply, since I am a new user I am not able to do so.

BR
Devbrath

#4

Hi @Devbrath_Rajkhua

I looked into it a bit more and there is indeed a known bug with the Excel Application Scope when writing range to a filtered file. It will surely get fixed in the future releases, but for now I would suggest to use the workaround from above (using the workbook activities instead OR running a macro that removes filtering before writing your range).

The bug comes from the fact that the rows have 0px height when filtered out. If you change the height of filtered rows to at least 1px height, the write range should write the file properly.

#6

The issue not able to write range in hidden or filtered rows within the excel application scope is not fixed in Version 2.8.5. The problem is in the enterprise edition as well as in community.
In the older version of excel activity (i.e. 2.7.2) there was no problem.

Is there a ticket to solve it and when does it happen?

Thx :slight_smile:

#7

Hi @Akimbow

It should now display and error that you cannot write over filtered rows. It is a technical limitation.

#8

Hi @loginerror,

thanks for your answer.
That is very pity. Why was it able to write over filtered rows in the older Versions and now it is an error?
These new limitation makes no sense to me and therefore it is very annoying that you have now to make an workaround.
If you copy a range and paste it into filtered rows manually in excel it is possible as well. It is no technical limitation of excel itself. What technical limitation reason is it that Uipath changed it to error?

#10

Hello,

and I thougtht we pay for a working Automation Software.

Kind regards,
Tino

#11

Hi LoginError,

Can you please update us when this will be fixed? If this was not a limitation in the past, it shouldn’t be in the future. I will stick to 2.7.2 for now.

Rick

#12

I had another look at this topic to better understand the use case.

I tried to do the action on 2.7.2 version like so:

When I filter this:
image

Into this:
image

And then copy this:
image

And I want to paste it here:
image

With the Excel activity package 2.7.2 and Excel Application Scope, I get this:
image

This means, that even the 2.7.2 version is not working the same as manual copy.

I gave it a test as well. The manual paste over filtered data doesn’t seem to be reliable.

Let’s see a data filtered like so:
image

Now, I will paste this:
image

Into here:
image

The result:
image
image

Could you maybe elaborate on the expected outcome? From my tests, it looks quite unreliable.

I believe the Workbook activity package is designed to ‘tap’ into the source Excel file and is able to paste the data in the specific spot because it ‘sees’ the unfiltered file.

The Excel Application Scope is designed to live in the context of currently displayed values, which makes it tricky to handle. Our team chose to enforce best practice and prevent the user from writing over filtered range with an unpredictable outcome.

#13

Hello @loginerror,
your approach is not a error, it is just what I expect. You need to understand microsoft excel to know how to paste into filtered rows, what happens and do not get unreliable results.

It is correct that you overwrite the filtered cells with the content of your copy cells, it is not an unreliable thing. If excel do not prohibit this by itself, it is not useful that you forbit it in uipath application scope. Everything you can do manually in excel should be possible with application scope too.
To paste into filtered rows and overwriting the unseen cells is not an error and should be fixed in the new activity versions.

Greets
Achim

1 Like