VBA - importing all excel files in subfolders

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

forum.ozgrid.com/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

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

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

Re: VBA - importing all excel files in subfolders

Hi snb...

Why did you use "Filter" and ":" in the line that defines the sn array?

I am trying to visualise why.. but alas it escapes me.. a hint please..

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:

for j=0 to ubound(sn)-1

Re: VBA - importing all excel files in subfolders

Got it!.. thanks

Re: VBA - importing all excel files in subfolders

Nice....

Re: VBA - importing all excel files in subfolders

Quote from snb;719561

Code

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

Display More

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:

Sub M_snb()
    sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\OF\adres_*.xls"" /b/s").stdout.readall, vbCrLf), ":")
     
    For j = 0 To UBound(sn)
        With GetObject(sn(j))
            .SaveAs "G:\OF\" & Replace(Replace(Replace(sn(j), "\", "_"), "G:", "__"), ".", "") & CreateObject("scripting.filesystemobject").getBaseName(sn(j)) & ".csv", 24
            .Close 0
        End With
    Next
    
    CreateObject("wscript.shell").Run "cmd /c copy ""G:\OF\___*.csv"" G:\OF\integration.csv"
    Workbooks.Open "G:\OF\integration.csv"
End Sub

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

This is brilliant! I (sadly) get quite excited about moments like this where I'm learning something new. Cheers snb!

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