Hello All

I have gone through several threads. One of that was quite close: http://www.mrexcel.com/forum/excel-q...name-file.html

However, I have a slight different problem.

I have a folder for year 2013, which will be 2014 obviously next year. This folder contains multiple subfolder, and further subfolders in each of them, and so on. My objective is to copy all the excel files in folder "2013" irrespective of which subfolder it is lying into. And want to copy all those .xls files in a seperate folder which I will create on my desktop. The names of the excel files can be same or may be different.

I want to know if there is a way out to this. Please be noted, as mentioned above, that there may be more than 1 file with same name in few of these subfolders. So can our macro automatically give a suffix to the file in the destination folder. For example, 3 files in Directory "2013" with name "AAA". Can the macro save these three files as "AAA", "AAA_1" & "AAA_2". Alternatively, I am fine with it, if the files are saved with the path name from where they have been copied.

Any help in this would be highly appreciable. Please let me know if more details needs to be furnished here.

Gaura215 wrote:

I have gone through several threads. One of that was quite close:
http://tinyurl.com/kkw6ahr

However, I have a slight different problem.

I have a folder for year 2013, which will be 2014 obviously next year.
This folder contains multiple subfolder, and further subfolders in each
of them, and so on. My objective is to copy all the excel files in
folder "2013" irrespective of which subfolder it is lying into. And want
to copy all those .xls files in a seperate folder which I will create on
my desktop. The names of the excel files can be same or may be
different.

I want to know if there is a way out to this. Please be noted, as
mentioned above, that there may be more than 1 file with same name in
few of these subfolders. So can our macro automatically give a suffix to
the file in the destination folder. For example, 3 files in Directory
"2013" with name "AAA". Can the macro save these three files as "AAA",
"AAA_1" & "AAA_2". Alternatively, I am fine with it, if the files are
saved with the path name from where they have been copied.

Any help in this would be highly appreciable. Please let me know if more
details needs to be furnished here.

There are a number of ways you can do this. What you do depends on what you
do and do not want copied.

The simplest way is to open a cmd prompt, navigate to the directory
containing \2013, and use xcopy:

cd /d "C:\users\auric\Desktop"
xcopy .\2013\*.xl* .\2014\ /s

This will copy all Excel files, and nothing else, to 2014, preserving the
directory structure (which you said was acceptable). For a list of switches
supported by xcopy, type "xcopy /?" (without quotes) at a command prompt.

Other methods (in VBA) involve walking the tree, checking whether or not a
given file already exists, keeping track of the current number to be
appended to the filename, etc. Bit of a PITA.

--
- You seem a decent fellow. I hate to kill you.
- You seem a decent fellow. I hate to die.

Hi Auric

Thanks a lot for spending your valuable time on this.
But I have a slightly different requirement.

I want all excel files to get copied in a single folder.

For example, there are several subfolders in 2013 folder (Approximately 1000 subfolders), and each subfolder may or may not contain any excel file.

I have a folder name as Output on my desktop, in which I want all excel files gets copied, without any subfolders getting created in Output folder. The name of the excel files if possible can be the path they got copied from, so that i can easily differenciate as to which folder/subfolder that excel file belongs to.

Please help if possible :-)

"Gaura215" wrote:

I have a folder name as Output on my desktop, in which I want all excel
files gets copied, without any subfolders getting created in Output
folder. The name of the excel files if possible can be the path they got
copied from, so that i can easily differenciate as to which
folder/subfolder that excel file belongs to.


I have a possible solution that might not require the use of Excel VBA.

Using a DOS prompt or a cmd prompt, type the following command:

dir /b /s c:\2013\*.xls* c:\xcel_2013_file_list.txt

You might need to change the pathnames according to your system.

Okay, so this will give you a text file named "xcel_2013_file_list.txt".
Can you now create a script that reads each line in this file and copies
each source file to your target destination folder (using your own
filename choice)???

I hope that gets you going in the right direction.

Robert