open excel files in a sub-folder and copy sheet to another worksheet.

[arcesdiola]

Hello guys,

May you help me with my project?

I need to open "all the files" in a specific sub-folder inside a folder. files inside the sub-folder do have specified date on its sheet name (also with the file name, correspondingly) and close the files after copying. here are the details.

main folder: D:\Documents and Settings\arces.diola\Desktop\teams
sub folder: \AO
sub-sub folder: \20110115-20110215
files: john smith_20110115-20110215.xls
jane close_20110115-20110215.xls
james reid_20110115-20110215.xls
(inside john smith_20110115-20110215.xls file, name of the worksheets are 0115-0131 and 0201-0215, and same for the other excel files)
the sheets to be copied will be placed to compilation.xls

i hope you can help me with my problem. thanks..

 

[tusharm]

If you have 3 files you are probably much better off copying the sheets by hand. But if you must automate the process look at
Process all files in a folder and, optionally, in sub-folders
http://www.tushar-mehta.com/publish_train/xl_vba_cases/process_all_files_in_folder.htm

Use the 'ProcessOneFile' subroutine to open the indicated file, copy the worksheets, and close the file without saving it.

To get the necessary code, use the macro recorder. Once you have the code from the macro recorder, adapt it yourself or share it here and someone should be able to generalize it for you.

 

[arcesdiola]

Hi Tushar,

I use Marco Recorder and these are the codes I got opening the files inside the sub-sub folder and copying particular sheets on the "compilation.xls"

'opening james reid_20110115-20110215.xls
Sheets("jr_0115-0131").Select
Sheets("jr_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(1)
Windows("james reid_20110115-20110215.xls").Activate
ActiveWindow.Close

'opening jane close_20110115-20110215.xls
Sheets("jc_0115-0131").Select
Sheets("jc_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(2)
Windows("jane close_20110115-20110215.xls").Activate
ActiveWindow.Close

'opening john smith_20110115-20110215.xls
Sheets("js_0115-0131").Select
Sheets("js_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(3)
Windows("john smith_20110115-20110215.xls").Activate
ActiveWindow.Close

 

[tusharm]

Originally, you'd indicated that the worksheet names would be the same for each of the workbooks. In the sample you shared, they are different.

You can program all this but it will get increasingly messier as you customize the code for each different workbook.

Are you sure you can't process the 3 workbooks by hand?

Hi Tushar,

I use Marco Recorder and these are the codes I got opening the files inside the sub-sub folder and copying particular sheets on the "compilation.xls"

'opening james reid_20110115-20110215.xls
Sheets("jr_0115-0131").Select
Sheets("jr_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(1)
Windows("james reid_20110115-20110215.xls").Activate
ActiveWindow.Close

'opening jane close_20110115-20110215.xls
Sheets("jc_0115-0131").Select
Sheets("jc_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(2)
Windows("jane close_20110115-20110215.xls").Activate
ActiveWindow.Close

'opening john smith_20110115-20110215.xls
Sheets("js_0115-0131").Select
Sheets("js_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(3)
Windows("john smith_20110115-20110215.xls").Activate
ActiveWindow.Close