Looping Through Workbooks in a Directory

[GeneralShamu]

I have code that lets a user select a file within any directory they choose. The code runs perfectly fine.

Now however, I need the user to be able to select a directory instead so 20 - 30 workbooks can be run. Does anyone know how to do this?

The directory will have XLS and PDF files as well as other sub-directories. I would only wish to run th is on XLS files/workbooks naturally.

 

[jbeaucaire]

Here's a macro for collecting data from all files in a specific folder. This version copies the sheet in as a whole.

Workbooks to Sheets - PART 2​

The point is that this macro demonstrates the process of opening all the workbooks in a single directory for processing... you can change the part of the code where it "does stuff".

 

[GeneralShamu]

Here's a macro for collecting data from all files in a specific folder. This version copies the sheet in as a whole.

Workbooks to Sheets - PART 2​

The point is that this macro demonstrates the process of opening all the workbooks in a single directory for processing... you can change the part of the code where it "does stuff".

Intriguing; I will have a further look when I am in the office on Monday.

 

[jbeaucaire]

If you post your current "working on a single file" macro, I can show you how to apply my code to yours.

 

[GeneralShamu]

If you post your current "working on a single file" macro, I can show you how to apply my code to yours.

Can't do that unfortunately...and it has about 50-60 loops, sub methods, etc...

 

[GeneralShamu]

Here's a macro for collecting data from all files in a specific folder. This version copies the sheet in as a whole.

Workbooks to Sheets - PART 2​

The point is that this macro demonstrates the process of opening all the workbooks in a single directory for processing... you can change the part of the code where it "does stuff".

Looking at:
"PART 3 - All Subfolders in a main folder"

This is exceptionally difficult to understand unfortunately.

Dim SubFLDRS As Object: Set SubFLDRS = FLD.SubFolders
Dim SubFLD As Object

Seems like the code for opening the subfolders...is there a special way of defining an object for XLS files?

 

[GeneralShamu]

What about the 'Dir' function?

From Excel:

Dir Function
      

Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

Syntax

Dir[(pathname[, attributes])]

The Dir function syntax has these parts:

Part Description 
pathname Optional. String expression that specifies a file name — may include directory or folder, and drive. A zero-length string ("") is returned if pathname is not found. 
attributes Optional. Constant or numeric expression, whose sum specifies file attributes. If omitted, returns files that match pathname but have no attributes. 



Settings

The attributes argument settings are:

Constant Value Description 
vbNormal 0 (Default) Specifies files with no attributes. 
vbReadOnly 1 Specifies read-only files in addition to files with no attributes. 
vbHidden 2 Specifies hidden files in addition to files with no attributes. 
VbSystem 4 Specifies system files in addition to files with no attributes. Not available on the Macintosh. 
vbVolume 8 Specifies volume label; if any other attributed is specified, vbVolume is ignored. Not available on the Macintosh. 
vbDirectory 16 Specifies directories or folders in addition to files with no attributes. 
vbAlias 64 Specified file name is an alias. Available only on the Macintosh. 



Note   These constants are specified by Visual Basic for Applications and can be used anywhere in your code in place of the actual values.

Remarks

In Microsoft Windows, Dir supports the use of multiple character (*) and single character (?) wildcards to specify multiple files. On the Macintosh, these characters are treated as valid file name characters and can't be used as wildcards to specify multiple files.

Since the Macintosh doesn't support the wildcards, use the file type to identify groups of files. You can use the MacID function to specify file type instead of using the file names. For example, the following statement returns the name of the first TEXT file in the current folder:

Dir("SomePath", MacID("TEXT"))

[B]To iterate over all files in a folder, specify an empty string:

Dir("")[/B]
If you use the MacID function with Dir in Microsoft Windows, an error occurs.

Any attribute value greater than 256 is considered a MacID value.

You must specify pathname the first time you call the Dir function, or an error occurs. If you also specify file attributes, pathname must be included.

Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string (""). Once a zero-length string is returned, you must specify pathname in subsequent calls or an error occurs. You can change to a new pathname without retrieving all of the file names that match the current pathname. However, you can't call the Dir function recursively. Calling Dir with the vbDirectory attribute does not continually return subdirectories.

Tip   Because file names are retrieved in no particular order, you may want to store returned file names in an array, and then sort the array.

 

[jbeaucaire]

Here is the same macro with the DIR() method added to get all the .xls files in each subfolder, one at a time, and process them.

Option Explicit

Sub ImportXLSFilesAllSubFolders()
'Author:    Jerry Beaucaire
'Date:      8/9/2010
'Summary:   Opens the XLS files in all subfolders
'           imports data from row 7 down into this workbook
'           putting data into sheets with same names as imported data
'           Destination book creates blank sheets if necessary

Dim fNAME As String, fPATH As String, wsTest As String
Dim FSO As Object, FLD As Object
Dim SubFLDRS As Object, SubFLD As Object
Dim wbMain As Workbook, wbData As Workbook
Dim ws As Worksheet, LR As Long

Application.ScreenUpdating = False
fPATH = "C:\2010\Test\Main\"    'don't forget the final \ in this string
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FLD = FSO.GetFolder(fPATH)
Set SubFLDRS = FLD.SubFolders
Set wbMain = ThisWorkbook

For Each SubFLD In SubFLDRS
    fNAME = Dir(fPATH & SubFLD.Name & "\" & "*.xls")
    
    Do While Len(fNAME) > 0
        Set wbData = Workbooks.Open(fPATH & SubFLD.Name & "\" & fNAME)
        
        On Error Resume Next   'This allows the add-sheet stuff to work if sheet is missing
        
        For Each ws In ActiveWorkbook.Worksheets
            LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Range("A7:A" & LR).EntireRow.Copy
            With wbMain     'add sheet if needed
                If Not Evaluate("ISREF('[" & wbMain.Name & "]" & ws.Name & "'!$A$1)") Then _
                    .Worksheets.Add(After:=.Worksheets(.Worksheets.Count)).Name = ws.Name
                .Sheets(ws.Name).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
            End With
        Next ws
   
        Application.CutCopyMode = False
        wbData.Close False
        
        fNAME = Dir
    Loop
Next SubFLD


Set wbMain = Nothing
Application.ScreenUpdating = True
End Sub