Create new excel sheet for every value in a DT

Help
1

Hi,

How can I create a new excel sheet for every value in a particular datatable? I have a data table with a column named EMP ID, so i want an excel file containing all Emp IDs in various tabs/sheets.
So, For each Emp ID in that data table, a new sheet should be created in the excel file.

1 Like
2

Fine
Hope these could help you resolve this
— hope you have datatable read from the excel using read range readily
— now use a for each row loop activity and pass the above variable obtained as input and inside the loop use a write range activity within a excel application scope
—that is in the excel application scope activity and mention the file path of the excel
— inside the scope now use write range activity with sheetname as row(“yourempidcolumnname”).ToString) and mention the range as “”
— this will create sheetnames with those empid
As sheet names

Kindly try this and let know for any queries or clarification

Cheers @shreyaank

3

Hi @shreyaank

Check this xaml

Main.xaml (7.7 KB)

4

Hi Ashwin,
Can you name the activity that you have used. It is displaying Missing/Invalid actvity

5

Hi Palani, Shouldnt I pass the Datatable name in the Write range activity?

1 Like
6

Hi @shreyaank
Use excel application scope and read range and along with assign with write range activity

Thanks
Ashwin S

7

Yes of course we need to buddy
Cheers @shreyaank

8

image
image775Ă—481 13.2 KB
Im not able to see the activity.

9

So i dont need to paste anything inside the sheet, i just want the sheets to be created

1 Like
10

have use for each row activity buddy

Thanks
Ashwin S

11

can you send the screenshot?

12

Hi @shreyaank

Take this screenshot

for%20each%20row%20activity
for%20each%20row%20activity965Ă—631 26.2 KB

13

Hi @shreyaank

may i know what is the version of UiPath are you using

14

Fine
In that case we can create a dummy Datatable
Like use a build datatable activity before to this for each row loop and get the output from the activity named out_dt
— no inside the loop within write range activity use this datatable as input
But keep in mind that uncheck the Add Headers property in write range property panel

Simple isn’t it
Cheers @shreyaank

15

Hi @shreyaank

After assign strvar=row(“A”).toString

Use Datatable=Datatable.clone()

Thanks
Ashwin S

16

So did that work buddy
Cheers @shreyaank

17

Hey Palani,

That does work, but it is not solving my problem.

So I have an excel sheet with a particular format, When im trying to create multiple sheets of the same format with different Emp ID, it is not coping the format. Even after checking the option- preserve Format while reading the range of the datatable, the format is bot being copied.

the below is the format of the excel im trying to duplicate with various emp ID’s

image
image1282Ă—519 2.99 KB

1 Like
18

Fine
Kindly share a excel sample so that we could come out with some xaml
Cheers @shreyaank

19

SAMPLE.xlsx (34.7 KB)

Hey Palani, Ive attached the file. All I need to do is create duplicate copies of the same sheet1 with different emp IDs. in the same file. I need multiple sheets of the same format to be created in the excel file.