Macro to loop through all files in a folder

**aldo23** · 02-26-2013, 08:12 AM

Hi,

I have a macro that I use to copy and paste data from a number of reports and I am trying to create a loop that will run the macro on all the files in a certain folder. I have had a go at it but the code I have just opens the first file in the folder that I have selected over and over again. So I'm looking for the code to only open each file once and then move on. Any help would be much appreciated!

Sub Copy()

ChDrive "X:\"
ChDir "X:\Business \ Reports\"
FileToOpen = Application.GetOpenFilename
Do While FileToOpen <> ""
If FileToOpen <> False Then
Workbooks.Open (FileToOpen)

Rest of Macro

Else
Exit Sub
End If
Loop
End Sub

**Andrew-R** · 02-26-2013, 08:17 AM

I'd do something like this:

Dim wbkCurrent As Workbook

FileToOpen = Dir("X:\Business\Reports\*.xl*")
While FileToOpen <> ""
  Set wbkCurrent = Workbooks.Open("X:\Business\Reports\" & FileToOpen)
  'Rest of macro
  wbkCurrent.Close
  FileToOpen = Dir
Wend

**aldo23** · 02-26-2013, 08:59 AM

Thanks so much for the quick response!! That seems to work great.

I should have mentioned in the original post that the 'Reports' folder will often have sub-folders within it. So is there a way to change the code so that the Directory in the first line can be selected through an open file dialogue box or something? Or is there another way to do this without having to change the actual code every time?

Thanks again

**Andrew-R** · 02-26-2013, 09:03 AM

Just have the path in a string variable (populated however you like) and then have the code say something like:

FileToOpen = Dir(sFilePath & "*.xl*")

And

Set wbkCurrent = Workbooks.Open(sFilePath & FileToOpen)

**aldo23** · 03-01-2013, 10:21 AM

Thanks again for your help Andrew.

I didn't really understand what you meant before but after playing around with the macro a bit more it works exactly the way I want it to with the following code:

Dim FileToOpen As Variant

ChDir ("X:\Business\Reports\2013\Feb\")
FileToOpen = Application.GetOpenFilename(, , , , True)
Counter = 1
While Counter <= UBound(FileToOpen)
Workbooks.Open FileToOpen(Counter)

'Rest of Macro

Counter = Counter + 1

Wend

So I can now select more than one file to open at a time in whatever folder I want and loop through the selected files to open them.