How to append all excel files from a file path to a single excel file

Help Something Else
1

I have two cases, where I have 8 excel files

  1. Like 3 excel files have 1 sheet where in this excel files sheet names would differ
  2. Remaining 5 excel files have 2 sheets but here sheet names are same
    so I want all the excel files in a single sheet. so may I know combine all this in a single excel file?

could anyone share me workflow for this please… if u have some time

2

Hi @sathish_Kumar6

All these excel files and sheets have same kind of data, i.e., the column names are same.

3

@sathish_Kumar6 read the one excel and add the data into main excel then read second excel then use the Append range activity provide the data table and main file path in append range activity

1 Like
4

yes all the column names are same but only sheet names will change for 3 excel files
For another 5 excel files column names and 2 sheet names are same

may i know to do this in workflow? please?

5

@sathish_Kumar6,

You can do this using VBA,

1 Like
6

sorry sarathi idk about vba but ill try

7

you can use get sheets from every excel and you can iterate through the for each

8

define variable
sheets(int32)

use Excel Application Scope
output Workbook : workbooks
sheets=workbooks.getsheets.count-1

do: use Do While
body: use read range

           sheetName=workbooks.getsheets(sheets).ToString
            merge datatable operater
           sheets=sheets-1
            
 Condition: sheets>=0

This is the operation of a single file (including multiple worksheets), and you can add a loop to operate multiple EXCEL files.

1 Like
9

get workbook sheets? ill try bro

10

could you share me workflow for this bro im very new to uipath i can understand but idk how to execute in workflow?

11

@sathish_Kumar6
Please find code and let me know any issues
Merge_ExcelFiles.zip (372.1 KB)

12

Hi @sathish_Kumar6
Please find code and let me know any issues
Merge_ExcelFiles.zip (372.1 KB)

13

Thank you so much for the code @Naveen_Veeravalli but i have different sheet names like from flipkart it has a sheet name flipakart28-07-2022
amazon it has a sheet name amazon28-07-2022
olx it has sheet name olx28-07-2022
dates will change right but i will try to work on that later
but since sheet names are changing i have to give different sheet names too right
so there are two situations i have single sheets for flipkart,amazon,olx
double sheets for snapdeal,quickr,myntra,nike,addidos
but for snapdeal two sheets like snap1(28-07-2022), snap2(28-07-2022)
like wise it continous for two sheets like quickr1(28-07-2022), quickr2(28-07-2022)
two sheets like myntra1(28-07-2022), myntra2(28-07-2022)
two sheets like nike1(28-07-2022), nike2(28-07-2022)
two sheets like addidos1(28-07-2022), addidos2(28-07-2022)
and i want all the sheets in single excel file