Hello Friends,
I would like to know if it is possible to have one xls file load all .xls files that are inside a folder or a folder with subfolders and so on?
Regards,
David
Hello Friends,
I would like to know if it is possible to have one xls file load all .xls files that are inside a folder or a folder with subfolders and so on?
Regards,
David
drtduarte,
See:
List Files and the File Information in Folders and SubFolders
Leith Ross
http://www.excelforum.com/excel-prog...in-folder.html
Have a great day,
Stan
Windows 10, Excel 2007, on a PC.
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
I friend,
Thanks for your reply.
What I really want it's not list the file names but load their content.
Any idea if this can be done?
Hello drtduarte,
Do mean open the workbooks as they are found?
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star below the post.3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Here you have an example:
You have a master file that is empty when closed
Here you have the structure
Name | Address | Phone
You have then a lot of files with data on a subfolder with the same structure
file_df.xls
Name | Address | Phone
David | 21th street | 123456789
Joe | Avenue | 8527441963
file_fd.xls
Teresa | 45th street | 12951789
Mary | Plazza | 005441963
file_hb.xls
Alison | 920th street | 987654312
Marc | Terminal 45 | 3579841
When you open master file you get:
Name | Address | Phone
David | 21th street | 123456789
Joe | Avenue | 8527441963
Teresa | 45th street | 12951789
Mary | Plazza | 005441963
Alison | 920th street | 987654312
Marc | Terminal 45 | 3579841
I know this may sound crazy but I know that excel can do almost everything...
I than have a form to do great searches on that data.
It is important to have the data on the external files as each file will be updated a lot by people all over the country, and will be sent to the one with the master file who will be able to do the searches on the data.
drtduarte,
See the attached workbook "Load External xls files from folder subfolders - drtduarte - roderbruin - SDG12.xls".
It contains a Workbook_Open Event that triggers macro "RDB_Merge_Data_SDG".
The data from all the files in your folder\sub-folders will be copied into Sheet(1).
Right now, in column D, you will see the "File Path's". If this is not acceptable, the code can be removed.
Make sure that in "Module1", macro "RDB_Merge_Data_SDG", you change the filepath in BOLD:
Please Login or Register to view this content.
My test folders\sub-folders were: C:\TestData\TestData2
Each folder contained duplicate files based on the data your posted.
When you open the workbook for testing the first time, the macro's will fail because the filepath is not correct. Change the filepath as directed above, and then save the workbook. Then, open the workbook, and you should see your results on Sheet(1).
For testing, before you open the workbook, you could create the following folders on your local hard drive, and copy a few or your data files into each:
C:\TestData
C:\TestData\TestData2
Great approach that's exactly what I was searching for.
Just a small issue that I would like to solve, When I open the file I get the warning that the file is already opened, if I say that I want to reopen nothing happens, If I say I don't want the data is imported correctly.
Is there a way to void the warning? I guess this happens as the vba code finds also the master file, it should be possible to use some code to bypass that master file.
I found a solution,
I will put (MyPath:=ThisWorkbook.Path & "/TestData2", _)
This way only files inside that folder will be opened.
Thanks a lot for all the help on this.
Let me tell you that this system is to be used on a disaster emergency plan to a non profit community
drtduarte,
What is the exact name, with extension, of the master file, along with its full path?
Last edited by stanleydgromjr; 10-31-2009 at 06:14 PM.
Hi there,
The master file name will be calamidades.xls, and the full path is difficult to know as these files could be placed on usb pen disk
Anyway all the files with data will be inside a folder named "database" so theres no problem
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks