More than 2 excel tab to filter and paste into new workbook

Help Activities
1

ive 3 tabs in excel to apply same filter and save into a new workbook.
how to automate multiple datatable?

2

Hi @lingmui ,

Do all sheet have same headers and different data?

3

Yes, same header but different data. And to save the result in new workbook with same sheetname.

4

Ji @lingmui ,

Sorry i was away for sometime couldn’t reply.
I hope you have already find the solution but still you can match with given steps below

1 - You can read each sheet and stored data in 3 different data table
2 - Merge them all from Merge data table activity.
3 - Filter data accordingly from Filter activity.
4 - Use write Range to insert filtered data.

5

hi @ermanoj3101 ,
in step 4 result: filtered data is all merged to 1 sheet. Any solution to have filtered data save as each sheet?

6

Hi @lingmui

Correct me if i am wrong, So your requirement
Eg: If you are reading the data from sheet1 then filter the data and store the filtered data into different excel with same sheet name?

I have attached the xaml file let me know if your requirement is met or you need any changes:

Here I am reading the data from each sheet filtering it and writing the filtered data to different excel with the same sheet name

ReadDataFromDIfferentSheetsUsingSameFilter.xaml (7.8 KB)

Step1: Store all your excel sheet names in an array
Step2: Use for each loop through loop through the array values
Step3: Use Read Range inside the foreach loop
Step4: Use FilterData table activity
Step5: Use write range activity to write the filtered data to different sheets

Thanks

7

thank you @vinithareddyk123 ,
got the desired output using the attached xaml file.
have another question: is excel format xlsb not supported in read range?

8

Hi @lingmui

If you are using .xlsb files then use them in excel application scope read range as per my knowledge workbook readrange dosen’t support that format.

Thanks

9

Hi @lingmui

Did it work?

10

outline.xlsx (17.8 KB)
Msg prompt:
RemoteException wrapping System.Exception: Extension ‘.xlsb’ is not supported. Supported extensions are ‘.xlsx’ and ‘.xslm’.
at UiPath.Excel.Activities.WorkbookActivity`1.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

So for now, using .xlsx files

11

Hi @lingmui

I can see you have used workbook readrange activities it doesn’t support xlsb, you have to excel application scope activity it will support xlsb files.

image

Thanks.

12

Hi @lingmui

If the solutions works for you can you please mark my post as solution ?

Thanks

13

thank you

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.