Open All Files In A Folder

SonicBoomGolf

Active Member
Joined
Aug 7, 2004
Messages
325
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!!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Here's an example using FSO. Didn't check out VP's but it's most likely the same or similiar.

Code:
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

Active Member
Joined
Aug 7, 2004
Messages
325
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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:
Code:
Application.EnableEvents = False

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

Application.EnableEvents = True
 

SonicBoomGolf

Active Member
Joined
Aug 7, 2004
Messages
325
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
 

Forum statistics

Threads
1,141,018
Messages
5,703,754
Members
421,313
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top