Sheet Consolidation

Hi,

I have multiple sheets (morethan 50) in single work book. I want to consolidate it as single sheet. How can i do it. Please advise. First sheet only have Headers like Column1 as Name and column 2 as URL.

Hi @BaskaranVenkatesan

Welcome to uipath community
hope these steps could help you resolve your issue
–use a build datatable activity and create a datatable with two columns as you said or the required as you wish and get the output from this activity with a variable oftype datatable named Finaldt.
–use excel application scopeactivity and pass the file path as input
in this activity we can get a output of type workbook, from the property panel and name it out_workbook
–use a assign activity and mention like this
out_sheetnames = out_workbook.GetSheets
where out_sheetnames is a variable of type string array
–being inside the excel application scope use a for each loop activity and pass the above variable as input and change the type argument as string in the property pane
–inside this for each loop use a read range activity and mention the sheet name as item as it has the sheetnames of the excel we have passed
–get the output from the read range activity with a variable of type datatable named outdt
–use a merge datatable activity and mention the source file as outdt and destination as Finaldt (the one we created from build datatable activity)
so that the Finaldt is the datatable which will have all the sheet records merged together—then using write range workbook activity we can enter the datatable Finaldt to a excel

hope this would help you
Cheers @BaskaranVenkatesan

@BaskaranVenkatesan use append range so that you can consolidate into one excel sheet

Hi @BaskaranVenkatesan

That is a tricky question, without more information.
Does all the sheets have the same columns, with the same name/order?
Is the first sheet ALWAYS the sheet you want to move the data to?

To get all the sheets as @Palaniyappan suggest, you don’t have use an assign activity, as there is a Get Workbook Sheets activity, you can use within your Excel Application Scope.

image

@BaskaranVenkatesan

Welcome to the UIpath Community.

Create one workbook variable for Excel Application Scope Activity then check below to get sheets count.

varWB.sheets.count

And then use while loop Activity to loop one by one sheet and then use Read Range Activity to read each sheet data and then use Append Range Activity to append data.

Hi Palaniyappan, Thank for your prompt response. As per your instruction i have worked but getting an error message in assign activity.
image

1 Like

Error%20Screen2

1 Like

Fine create the variable OutSheetNames with datatype as
System.Collections.Generic.IEnumerable(of string)
not string array
that was my mistake typo error
Cheers @BaskaranVenkatesan

any issues still to be discussed
were we able to consolidate them
Cheers @BaskaranVenkatesan

I have changed the out variable as string in For each but getting an error message like "Compiler error(s) encountered processing expression “ConsltdCity”.
Option Strict On disallows implicit conversions from ‘String’ to ‘Integer’.

1 Like

can i have a screenshot if possible
@BaskaranVenkatesan

Actually we dont need to get the output from for each loop kindly remove that variable and what we need to do is to pass the input OutSheetNames and change the type argument property here as string
image

Cheers @BaskaranVenkatesan

Tool is running without any error but there is no details in consolidated sheet 1. Give me a min i wil share the screen shot

1 Like

Fine
kindly share the xaml and project.json or even all the files in a zipped folder with excel files as well (sample excel), if possible
Cheers @BaskaranVenkatesan

I’m unable to share the xaml and sample file. Error message displaying like "New user dont have an access.

can i share my sample screen shot

yah sure
@BaskaranVenkatesan

image

image