Copy Specific Excel Data to Another Worksheet?

[Luecke_Anthony]

Hi all,

I have an excel sheet that is created once a week from a reporting system. It will always look like below screenshot (Headers start on cell B6 and the row count will be different each time. The columns will always be the same.).

What I need to do is grab only the information in the table, including the headers and copy it to another worksheet. The last row will always contain the “grand totals”. I do not need the title “Sales Reports” nor the “selection criteria” row. How would I get only the data and the headers?

Thanks!!

[unknownay]

Hi @Luecke_Anthony

In read range activity you can mention range. Give “B6” Range in read range and the excel data will be copied to a datatable. later you can write this datatable to any other sheet or workbook using write range activity.

Regards
Roshan

[Luecke_Anthony]

But what if my row count is different each time, how would I tell it to grab the data all the way until the row with “Grand Totals”?

[unknownay]

@Luecke_Anthony

The last row you don’t need to mention. mention only starting range. Also check the property first row is header.

Mark as solution if you found this helpful

Regards
Roshan

[Luecke_Anthony]

Thank you, I tried this but when I write the output to excel, it includes the last row with “selection criteria” which I do not want. It is also not preserving the format of some columns even after clicking “preserve format”?

[balupad14 (Balamurugan)]

Hi @Luecke_Anthony ,

• Use “Copy to WorkBook” activity from below.

BalaReva.Excel.Activities

Hi Friends, I have included the below new custom components in the package. 1.Create Workbook.(Create a new worksheet) 2.Copy to File (Copy the worksheet to another excel file). 3.Copy to WorkBook (Copy the worksheet inside the workbook). 4.HideUnhide the worksheet 5.Protect Unprotect the worksheet [image] Create WorkBook Properties [image] Sample : CreateWorkbook.zip (18.5 KB) Copy To File This activity copies the work sheet to an another excel file. If the another file is not ex…

• find and replace activity from here.

UiPath Go ! BalaReva EasyExcel Activities

Hi , One my friend @VanessaCova having the situation to find and replace the text inside the excel. For that purpose I have developed some activities below. Find - Find the text inside the sheet. It will return the cell info as a array string and row indexes. FindReplace - Find and replace the text inside the excel sheet. [image] [image] Regards Balamurugan.S

Thank you
Balamurugan.S

[unknownay]

@Luecke_Anthony

Basically you need to remove last 2 rows of your datatable. use below code to do that
dt.AsEnumerable.Take(dt.Rows.Count -2).CopyToDataTable

If you get any error on asenumerable function. refere below link

AsEnumerable is not a member of 'System.Data.Datatable'

When trying to use the AsEnumerable() method on a data table, i receive a error stating that AsEnumerable is not a member of ‘System.Data.Datatable’. I’ve seen someone else with this problem and their solution was to use temporary data table activities in their project to load in the System.Data.DataSetExtension library. However this has not worked for me. Any help would be greatly appreciated. Thank you.

Regards
Roshan

[Luecke_Anthony]

@balupad14 this activity set is great! Do you know how I could align a column left or right? I did not see this activity available for alignment. Thanks!

[balupad14 (Balamurugan)]

Hi @Luecke_Anthony,

The activity name is Format Cells

It has the features that are below it has all align features…

Thank you
Balamurugan.S

Share your workflow with sample excel. Let me check and get back to you…

Thank you
Balamurugan.S

[Luecke_Anthony]

@balupad14 Perfect, I didn’t realize this was in the Format Cells activity. Thanks!

[Luecke_Anthony]

@balupad14 is this activity set compatible with 2019+ versions of Studio? It was working for me fine, but now seems to only work in debug mode, rather than normally in Studio? I’m on 2019.10.4 Thanks!

[balupad14 (Balamurugan)]

yes . it is compatible…

[arifudinmyf]

https://forum.uipath.com/t/cannot-display-the-colors-and-tables-in-the-original-file-after-they-are-run/227823?u=arifudinmyf

help me

[Luecke_Anthony]

@balupad14 If I’m using your “Change Cell Type” activity multiple times, should I include a long delay after each? I notice it’s only performing the activities in debug mode on slow step, but not when I actually run the process. No errors, the robot just stays running but does nothing.

It’s the same file path on each activity, and I have 11 of them total.

[balupad14 (Balamurugan)]

can you share me the sample workflow ?

[Luecke_Anthony]

Hard to share because it works when I only have the “change cell type” activities…but when I have activities before it, it does not work, and the robot just freezes but shows no errors.

[balupad14 (Balamurugan)]

Create a sample and send me ?..

Thank you…