MACRO to copy data.xls file from different subfolders to dest folder and rename the file

[sanjay1982]

I would like to copy every file named "data.xls" from every subfolder found in the folder c:\test\ and want to save in "dest" folder with file name "data_1.xls","data_2.xls"....so on so. Each sub folder is named as 01022013,02022013,03022013.....and all are contain the file "data.xls"

 

[kbriaz]

Sub a()
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   oldpath = "c:\test\"
   newpath = "c:\dest\"
   Set mainFolder = objFSO.GetFolder(oldpath)
   For Each mySubFolder In mainFolder.subfolders
     Name = "data.xls"
     p=instrrev(mySubFolder,"\")
     Name1 = "data_" & right(mySubFolder, len(mySubFolder)-p) & ".xls"
     FileCopy mySubFolder [COLOR=#ff0000]& "\"[/COLOR] & Name, newpath & Name1
   Next
End Sub

Hi Mate,

I have a similar problem.

I have a file in directory C:\Test

Name is File.xls

I want to re-name it to 'File_1.xls' first

Then copy different file called Data.xls to new location called C:\Done

Sorry to give you trouble mate. thanks in advance ( i am an excel noob )

 

[patel45]

Name C:\Test\File.xls As C:\Test\File_1.xls

FileCopy C:\Test\Data.xls, C:\Done\Data.xls

 

[kbriaz]

Many thanks, i will test & report back. happy holidays

 

[Venus_excel]

Hi Patel,

Im looking for your help.

My Question:

MACRO to copy all .xls file from different subfolders to Single dest folder

Thanks in Advance