Macro to copy files from Folders/Subfolders

[Gaura215]

Hello All

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 eac
of them, and so on. My objective is to copy all the excel files i
folder "2013" irrespective of which subfolder it is lying into. And wan
to copy all those .xls files in a seperate folder which I will create o
my desktop. The names of the excel files can be same or may b
different.

I want to know if there is a way out to this. Please be noted, a
mentioned above, that there may be more than 1 file with same name i
few of these subfolders. So can our macro automatically give a suffix t
the file in the destination folder. For example, 3 files in Director
"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 ar
saved with the path name from where they have been copied.

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

 

[Auric__]

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.

 

[Gaura215]

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 (Approximatel
1000 subfolders), and each subfolder may or may not contain any exce
file.

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

Please help if possible :-

 

[Robert Crandal]

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