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
#15

Hey Devbrath,

You could solve that problem with a workaround. Of course that is not a really beautiful solution but it would handle the problem immediately.

My thoughts therefore are, that because you cant write in filtered table or the outcome is garbage like you said, you could use a macro.
Others mentioned to delete the filter settings via macor which is a very good advice, when you want to update the whole table.
If you want to update just the filtered values, you could build a macro that:

  • fist copies your whole workbook to a save place twice
  • second sets your desired filter on the already existing workbooks worksheet
  • third deletes all hidden rows
  • now fills the filtered datatable with your new data
  • copies your data from the new data table (in the existing worksheet) and replaces the entries in one of the first copied workbooks worksheet with your updated data.
  • compares some from the old values between a copied workbook and the one with updated data.
  • now get informed if the values that are not involved in the updating are the same and if the updated values have been inserted or if an error has accured.
  • if everything’s good delete the copied workbooks.

If you have a beautiful solution for the same problem, I’d be very interested in getting to know it.

Best Regards,

Tim

#16

I faced the same issue, Though error message displayed in new version is helpful, its not precise about root cause.

Scenario 1:

Action : I tried to write data in K column using excel application scope - write range
Error message : Cannot write to the target range because it has hidden rows. Remove any filters or unhide rows and try again.

Analysis :

  • when checking the excel file, some columns are hidden [L, M, N, P] but all rows are visible.

  • i didn’t perform any action in hidden columns but still it throws error

Scenario 2:

Action : I tried to write data in K column using workbook - write range
Error message : Specified method is not supported.

Analysis :

  • when checking the excel file, some columns are hidden [L, M, N, P] but all rows are visible.

  • i didn’t perform any action in hidden columns but still it throws error

Then i tried to replicate above scenarios with hidden rows instead of hidden columns.
Same error message displayed depending on the type of write range used.

Solution or workaround for the problem is to use macro to unhide rows and columns before write range activity

Sample Code:

Sub Unhide_Columns()

Sheets("Sheet1").Select
Columns("K:O").Select
Selection.EntireColumn.Hidden = False

End Sub

Sub Unhide_Rows()

Sheets("Sheet1").Select
Rows("2:8").Select
Selection.EntireRow.Hidden = False

End Sub

1 Like
#17

Any idea when this will be fixed? Hate to say it but it has been over a year.

#18

As mentioned above, it was chosen that the Excel Application Scope should display this error message rather than introducing complicated logic to handle this scenario:
image

There are two ways to handle that:

  • remove all filters from your file before saving with Excel Application Scope
  • use the Write Range from the system activity package, I believe it ‘sees’ the entire file and can properly save the data into it
#19

Thanks, sorry I missed that.

#20

Hi @loginerror. One more question please. Workbook Write Range works for the hidden rows/columns but any cells with formulas are written as literals. Is there anyway to preserve formulas? Thanks.

#21

Don’t bother. Looks like I need to use the Read Range from the Workbook and not the Excel Application. Looks like Workbook is more reliable than Excel Application, true?

#22

@loginerror Hi, the issue is still there. But the weird thing is I don’t have any filtered or hidden columns in the file I am using. I used workbook read range and write range to sort it out. But please do fix this issue as this is occurring frequently.