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)
How can I merge excel sheets from several excel files into one file? File title is keep changing everyday
The rule seems to be “UiPath_Fourm_ver*_Today.ToString(“yyMMdd”).xlsx”
Where * is a wildcard.
Do the other files have a similar pattern?
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 ‘()’?
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
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.
It may help you.
@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…!