VBA - importing all excel files in subfolders

  • Hi, i have a bunch of excel files (with only one sheet per file) i need to import into one consolidated spreadsheet - so far i've done it manually but this is a very time consuming and error prone task so i'm turning to you geniouses for some advice.


    I have a folder with six sub-folders, which all contain 10 excel files with the same names (as in ten unique names per folder, but it repeats for every folder). So, if a piece of code can do something like running through the folders, imports all the excel files and names them with the appropriate the sub-folder name + the file name, then it would literally save me days of work (i have to do this every two weeks! [Blocked Image: http://www.mrexcel.com/forum/images/smilies/icon_banghead.gif]). If something like this exists already, or if anyone has some advice on how to make this I will be forever grateful! [Blocked Image: http://www.mrexcel.com/forum/images/smilies/icon_biggrin.gif]


    Thanks

  • Re: VBA - importing all excel files in subfolders


    Quote

    and names them with the appropriate the sub-folder name + the file name


    Names what?


    Are you saying you want each Excel file imported into a separate sheet and name each sheet like: Subfolder name_Filename ?


    Do you want the data for each sheet appended below existing data each time?


    Attach a dummy Workbook that is a representation of one of the files you import.

  • Re: VBA - importing all excel files in subfolders


    Have you tried recording a macro?


    I bet you'd get 85% of the way to where you want to go. We'd be able to get you the last 15% .... but you'd be a heck of a lot farther down the road if you did some grunt work prior to us handing you the finished solution.



    Check out the help on the "DIR" function so that you can get folder / file names


    Check out help for "Do...Loop Statement" use it in conjuction with the "DIR" function.



    Once you've determined how to find the files "DIR" and once you get them opened "RECRODED" macro will show you that ..... we can help you beautify it.






    And us helping you import 60 files will save you "DAYS??????" of work?

  • Re: VBA - importing all excel files in subfolders


    ozgrid.com/forum/core/index.php?attachment/61406/


    I've written this on a Mac which doesn't support half of the stuff that's in it so it's completely untested. Therefore if it actually works I will be amazed, but it should point you in the right direction.


    Think of this as a welcome freebie, first and last. Going forward at least try recording some code first and playing around with it like iwrk4dedpr suggested.


    You can press Alt + F11 to view the code when the workbook is open.

  • Re: VBA - importing all excel files in subfolders



    Replace "G:\OF\" by the foldername on your system.

  • Re: VBA - importing all excel files in subfolders


    If you split the string that is the result of the file retrieving code, it ends with a VbCrLf. so the last element of the array will be empty. Since we only need array elements that refer to a file, an element has to contain a : after the name of the drive the file is found in. G:\.....


    As a filter you could use as alternatives e.g. : "\", or ".".


    Instead you could use:


    Code
    1. for j=0 to ubound(sn)-1
  • Re: VBA - importing all excel files in subfolders


    Quote from snb;719561


    Replace "G:\OF\" by the foldername on your system.


    Jesus this is impressive, I just learned a handful of things in 7 lines of code. Good job snb! :cheers:

  • Re: VBA - importing all excel files in subfolders


    Something more to learn from an alternative:



    NB. The complication is the prevention of overwriting files that reside in different subfolders but with identical filenames.

  • Re: VBA - importing all excel files in subfolders


    snb...

    Quote

    prevention of overwriting files that reside in different subfolders but with identical filenames


    Is there any kind of built in function that works similar to when a file is copied to a location that already has a file with the same filename.. it asks if you want to Keep both files.. and (if i remember correctly).. appends a (x) to the filename..?

  • Re: VBA - importing all excel files from subfolders


    HI,


    When I merge multiple workbooks from different sub folder by using below code, the header gets repeated, e.g column A1 to AQ15. How do I remove the header and merge only the data file.


    Sub M_snb() sn=filter(split(Createobject("wscript.shell").exec("cmd /c Dir ""G:\OF\*.xls"" /b/s").stdout.readall,vbcrlf),":")

    For j=0 To UBound(sn)
    With getobject(sn(j)).sheets(1).usedrange
    thisworkbook.sheets(1).cells(rows.count,1).end(xlup).offset(2).resize(.rows.count,.columns.count)=.value
    .Parent.parent.close 0
    End With
    Next
    End Sub