How to combine multiple excel files into one workbook with multiple sheets

Help
1

Hi everyone! I’m looking for an activity to combine multiple excel files into one excel workbook with multiple sheets. I have a number of similarly named excel files in my download folder that I want to loop through and select using a wild card like file1, file2 would be grabbed using file*.xlsx. Any ideas?

2

Hi @canlilar,

Use the below logic.

  • Create a string variable named as “Filenames”
  • Assign Filenames = Directory.Getfiles(“Folderpath”)
  • For each item in Filenames, item type as String
  • Excel application scope, In workbook path give item. Use Read range with output as Datatable1
  • Excel application scope, In workbook path give a new output file name. Use Write range with input as Datatable1, sheetname as item.
3

Thanks for the quick response @Vivek_Arunagiri! However I am getting the following error in the for each activity: "Unable to cast object of type ‘System.Char’ to type ‘System.String’. Exception Type: InvaidCastException. The only change I made to your recommendations was adding a .ToString to the end of the Assign activity (Directory.GetFiles(‘Path’).ToString because I was getting an error and that seemed to solve it. I have attached my bot to show you how I created it. Any other advice you have would be greatly appreciated! Thank you!

Test import multiple excel files V2.xaml (10.0 KB)

4

Hi @canlilar
I know what your problem is. GetFiles() will return an array of strings to represent each filepath it found.

Filenames should be changed to an Array<Of String>
And, then you want a searchPattern string, which will represent the keywords you are looking for in the files. Let’s also move the folder path to a string variable as well.

See screenshot of variable changes:

image
image1336×84 4.79 KB

Then, let’s make an adjustment to your Assign at the top that returns a list of filepaths.
image

Keep in mind that GetFiles() returns an array, so by adding .ToString you are essentially not getting any of the items in the array. To get the items in the array, you use the For each that is right below it, and place the item that is referenced by the For each into your Excel scope, so it can read each file that you found with .GetFiles().

It is also good practice to add Close Workbook at the end of each Excel scope so it doesn’t get left open. You might also consider unchecking Visible, but not necessary.

image
image1244×365 18.3 KB

You would want to use Close Workbook for each Excel Scope you have there.

Everything looks right other than those few adjustments that I mentioned.

EDIT: you probably also want the “AddHeaders” checked in your WriteRange activity since you have it checked in your ReadRange.

Regards.

6

Thank you @ClaytonM! Perfect solution!

7

Here, instead of Write Range, Append range activity should be used. As Write Range will paste on the already existing data, and it will not be considered as merged workbook.

3 Likes
8

Hi Any kind soul can share this full solution here? Still newbie need some guidance by referencing the code.

9

please can you send me the Xaml for thank you

10

This is what I have. It takes multiple csv files and merge them in a single .csv file by pasting their data as separate sheets and writing the filename as sheet name.

Now, I am trying to achieve the same thing using VB .NET and make it work in background.
Any help will be appreciated

Main.xaml (11.3 KB)

11

@Vivek_Arunagiri : Here is my screenshot of the error occurred and .xaml file. Pl. guide. Thanks,

image
image731×262 10.1 KB

Test import multiple excel files V2.xaml (9.1 KB)