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.
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
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.
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.
1 Like
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
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
