Macro to open all files in a folder

[bookworm121]

Is it possible to write a macro that would open all files in a certain folder? The folder name and location will always stay the same but the name of the files within and the amount of files may change.

The macro would open them all, and then copy the tabs from them and create a new spreadsheet with all the tabs from all the files in it.

 

[Trevor G]

This code will open all workbooks in a folder all you have to do is add what you want to do inside the code. Adjust what I am doing in Red

Sub Open_My_Files()
Dim MyFile As String
MyPath = "M:\Access Files\"
MyFile = Dir(MyPath)
Do While MyFile <> ""
If MyFile Like "*.xls" Then
Workbooks.Open MyPath & MyFile
Sheets(1).Select
Range("C3") = 6
ActiveWorkbook.Close True
End If
MyFile = Dir
Loop
End Sub

 

[VoG]

This would open them - change the folder to suit

Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    Workbooks.Open Filename:=MyFolder & "\" & MyFile
Loop
End Sub

 

[MichaelB1]

Trevor - just a quick note of thanks on this! I had a situation where had over 200 files linked to a comparison sheet by an overly complicated HLOOKUP and they just wouldn't recalculate. I was resorting to opening all 200 files, then closing them again, just to get the recalculation to work (after swearing a lot and pressing various F9 combinations!).

Your macro took what was a 15 minute process and sorted it in 2 minutes. Thanks!!

 

[Trevor G]

Nice to read MichaelB1 thank you for the comments....

 

[actualDOOD]

Is there a way to do this so you don't have to enter the folder name?