Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro to copy files from Folders/Subfolders
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.
__________________
Regards Gaurav |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy files from Folders/Subfolders
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. |
#3
|
|||
|
|||
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 :-)
__________________
Regards Gaurav |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy files from Folders/Subfolders
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Copy Files (all Subfolders and Files) | Excel Programming | |||
How to get list of subfolders, including shortcuts to folders? | Excel Programming | |||
Opening files in folders and subfolders | Excel Discussion (Misc queries) | |||
Map/List of folders, subfolders & files | Excel Programming | |||
copy subfolders, replace text in files and save files in copied subfolders | Excel Programming |