How can I merge excel sheets from several excel files into one file? File title is keep changing everyday

Help Activities
, ,
1

Hi All,
How can I merge each excel sheets from several excel files into one file?
I have a job to merge 6 of xslx files everyday, but the title of each file is keep changing everyday.
(e.x. when I download UiPath_Fourm_ver2_201028 today, it will be UiPath_Fourm_ver2_201029 or sometimes UiPath_Fourm_ver3_201029)

2

Hi @Sarah_Cho

The rule seems to be “UiPath_Fourm_ver*_Today.ToString(“yyMMdd”).xlsx”

Where * is a wildcard.

Do the other files have a similar pattern?

3

Hi Gabriele,

Thank you for help.
The file version is given without any pattern.
One day it was “UiPath_Fourm_(09-10-2020cc2)”, but one day it was “UiPath_Fourm_(V1.0)”, or one day “UiPath_Fourm_(4128)”, etc…

However, definitely it’s first few words are same and changed versions are in ‘()’
Isn’t there any way to using the first words or ‘()’?

4

The Wild Card substitute any number of characters.

Where do you download the files? Everyday in a different folder? Or everytime in the same?

If is always in the same folder and the pattern is not telling you the day of download you may take files from other days

Else you can use this:

Directory.GetFiles(DownloadFolder).[Select](Function(x) New FileInfo(x)).OrderByDescending(Function(x) x.LastWriteTime).Take(1).ToArray()

This will take the last file modified (so also downloaded) in a particular folder

Then you insert into a List of String the file path and download the next. In the end you will have your List of String of every file downloaded and the you can do the merging

1 Like
6

Since I’m beginner for now, let me study your advice and let you know if I have ran it successfully, soon.
Thank you for help. :smiley:

1 Like
7

Hi @Sarah_Cho
It may help you.

Regards
Balamurugan.S

8

@Sarah_Cho… you can use a simple VBA script to achieve your requirement.

To give you a starter here is the script.
Note: Before using the below script please place all the 6 xlsx files in a folder.

Dim wb As Workbook
Set wb = ActiveWorkbook
Path = "%Path of your 6 xlsx folder%\"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
  For Each Sheet In ActiveWorkbook.Sheets
      Sheet.Copy After:=wb.Sheets(1)
  Next Sheet
  Workbooks(Filename).Close
  Filename = Dir()
Loop

This code can help you to merge different excel files to a single file. Irrespective of their names and number of files.

Hope this helps you…!
Cheers!!

2 Likes