How To Merge Many Excel Sheets in One Sheet Using UiPath?

Help Studio
1

Dears,

How To Merge Many Excel Sheet in One Sheet Using UiPath?
Knowing that , the Number of Sheet might Increase or Decrease, So Probably Need For Each Loop to Read first Existing Sheets in Workbook than Merge them all to one sheet.

image
image1108Ă—46 2.5 KB

Thanks in Advance

2

Hi @hsendel

there is a Package from Balareva

there you can use “get sheet name” and take a loop.

2 Likes
3

Thanks @MarioHerrmann , Could you please share small example for how to achieve this? Because there is no Merge Sheets

4

My approach would be like this (first quick idea)

  • Count how many spreadsheets there are
  • Then read sheet 1 and save it in a DT
  • Then read sheet 2 and append to DT
  • Then read sheet n …
  • Create new spreadsheet
  • Write the DT to the new spreadsheet with WriteRange

Like I said is just a quick-and-dirty idea. There are certainly a lot of subtleties that need to be considered to make the process robust.

There are certainly people here in the community who can solve this much more efficiently with VB code.

1 Like
5

There is no count Sheet activity in BalaReva Package

6

@hsendel - Please try like this…

  1. Read Excel application scope → In “Output” Workbook declare a workbook variable called wb
  2. For Each → Type Argument is “String”

image

  1. Read Range(Excel not workbook) → give the the sheet Name as "EachSheet " and stored it to datatable called dt

  2. Add If Loop if file.exists(“Output.xlsx”)
    Then → Append Range → “Output.xlsx” → SheetName = Eachsheet → dt
    Else → Write Range → “Output.xlsx” → SheetName = Eachsheet → dt

Hope this helps…

1 Like
7

Thanks @prasath17 , Seems there is something missing in step4 I got the following :

image

image
image1099Ă—379 9.37 KB

8

@hsendel - Apologize…it should not be Eachsheet…As shown below…

image
image873Ă—340 12.3 KB

Complete Workflow…

image
image639Ă—550 15.8 KB

So first time when the loop runs, file output.xlsx wont exist so it will go to write range and creates the file…and from the 2nd run onwards it goes to append range…

Note: In the write range, please check “Add Header”. Also instead of wb.getsheets you can use “Get workbooksheets” activity which will give you all the sheetnames and then you can use the sheetnames in the for each(which is exactly similar you don’t have to remember the code)

2 Likes
9

Thanks a lot @prasath17 , Very easy and Simple :+1:

1 Like
10

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