Open All Files In A Folder

[SonicBoomGolf]

I am looking for a piece of code that will open all the files contained within a specific folder. I imagine that it will look something like the sample code below (which is not working).

Sub TEST()

Dim Folder As String, File As String
Folder = "D:\jsmith\Desktop\TEST"

For Each File In Folder
File.Open
File.Close

Next File

End Sub

If I can just get the files in this folder to open and then close I can run the macro to extract data. Any help would be appreciated!! Thanks!!

 

[Von Pookie]

This is the base code that I use to do that:
http://www.mrexcel.com/board2/viewtopic.php?t=137379

 

[Cbrine]

Here's an example using FSO. Didn't check out VP's but it's most likely the same or similiar.

Dim FSO As object, f As object, Path As String

Path = "C:\test"
Set FSO = CreateObject("Scripting.FileSystemObject")

For Each f In FSO.GetFolder(Path).Files
    If f.Type = "Microsoft Excel Worksheet" Then
        Workbooks.Open Path & "\" & f.Name
    End If
Next

HTH
Cal

 

[SonicBoomGolf]

Thanks for the help both of you! Next question, which will be a slight change of course.

The files I will be openeing contain macros that I do not want run when I extract data. How can I disable the macros when opening them using this method?

P.S. I used Cbrine's code in my file.

 

[Von Pookie]

Do you mean event macros? Like Worksheet_Calculate, etc? I haven't tested it but try disabling events in the code that is opening the files.

For example:

Application.EnableEvents = False

For Each f In FSO.GetFolder(Path).Files
     'blahblahblah
Next

Application.EnableEvents = True

 

[SonicBoomGolf]

Thanks for the help! The Application.EnableEvents piece did the job. The code below is working for me. I’ll build on it more in the future but the core piece is working. Thanks again both of you.

Sub TEST()

Dim FSO As Object, f As Object, Path As String

Path = "D:\jkrantz\Desktop\TEST FOLDER"
Set FSO = CreateObject("Scripting.FileSystemObject")

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

For Each f In FSO.GetFolder(Path).Files
If f.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Path & "\" & f.Name

'Begins Data Log Copy Procedure
finalrow = Sheets("LOG SHEET").Cells(65536, 1).End(xlUp).Row
Sheets("LOG SHEET").Rows("2:" & finalrow).Copy
ThisWorkbook.Activate
finalrow1 = ActiveWorkbook.Sheets("Master Log").Cells(65536, 1).End(xlUp).Row
Sheets("Master Log").Rows("" & finalrow1 + 1 & ":" & finalrow1 + 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Workbooks("" & f.Name).Close
End If
Next

Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub