VBA to loop through files in subfolders

Question

I need to amend a column heading in multiple excel files. The files are located in subfolders two levels below the folder I want to loop through (28 .xlsx files in each folder, one folder for each Period).

The file structure is: "c:\...filepath...\Period *\Daily Attributions\*.xlsx"

I have managed to get some code to work that loops through each file in a folder but I need help to repeat the same loop for all the files in each folder.

Here's what I have so far.

Sub FirstLast()
'
' FirstLast Macro
'
  Dim sPath As String
  Dim sFile As String
  Dim wb As Workbook

  sPath = "C:\...filepath...\Period 1\Daily Attributions\"
  sFile = Dir(sPath & "*.xlsx")

Do While sFile <> ""

Set wb = Workbooks.Open(sPath & sFile, UpdateLinks:=False)

    Range("E1").Select
    ActiveCell.FormulaR1C1 = "FirstLast"
    ActiveWorkbook.Save
    ActiveWindow.Close
sFile = Dir

  Loop


End Sub

Create a collection where you store in your `periods` dirs using the same logic above with `dir()` and then run through that collection using a `For each loop` with the same code above! You're not that far from the answer. — Amen Jlili, Feb 20 '15 at 13:09
@brettdj it may be my poor understanding of vba (i'm quite new to it) but I'd already seen the answer you are suggesting that this is a duplicate of... and I thought that was referring to looping through files in a directory. My question explains that I have a solution to this but that I needed to loop through the different folders as well. — Hal Baggot, Feb 23 '15 at 09:46
@HalBaggot Aploges, you are correct. This link gives the subfolders, http://stackoverflow.com/questions/9827715/get-list-of-subdirs-in-vba/9832978#9832978 — brettdj, Feb 23 '15 at 14:14
Thanks, I didn't see that question because it only uses `word` tags. — Hal Baggot, Feb 23 '15 at 14:45
@brettdj Any chance you could remove or amend to deplicate marker? Unless you searches specifically for word-related tags, you'll not find the answer you link to. — Hal Baggot, Feb 25 '15 at 15:50

Amen Jlili · Accepted Answer · 2015-02-20T14:11:33.947

This answer is the illustration of my comment above:

I've tested it and it works like charm:

Sub FirstLast()
  Dim sPath As String
  Dim sFile As String
  Dim wb As Workbook
  Dim subfolder As String
  Dim subdir As Collection
  Set subdir = New Collection
  Dim maindir As String: maindir = "D:\main\" 
  'Above line is the main directory which is "C:\...filepath...\"
  subfolder = Dir(maindir, vbDirectory)
  Do While subfolder <> ""
  subdir.Add Item:=subfolder
  subfolder = Dir(, vbDirectory)
  Loop
  ' remove . and ..
  subdir.Remove (1)
  subdir.Remove (1)
  For Each m In subdir
  sPath = maindir & "\" & m & "\Daily Attributes"
  sFile = Dir(sPath & "*.xlsx")
  Do While sFile <> ""
  ' do stuff:
  'Set wb = Workbooks.Open(sPath & sFile, UpdateLinks:=False)
  'Range("E1").Select
  'ActiveCell.FormulaR1C1 = "FirstLast"
  'ActiveWorkbook.Save
  'ActiveWindow.Close
  sFile = Dir
  Loop
  Next


End Sub

Like a charm, thank you... with one minor adjustment to reflect that my files are in a sub-sub folder. I changed: `sPath = maindir & "\" & m & "\"` ...to... `sPath = maindir & "\" & m & "\Daily Attributes"` — Hal Baggot, Feb 20 '15 at 14:08

score 1 · Answer 2 · answered Feb 20 '15 at 13:05

  Sub FirstLast()
'
' FirstLast Macro
'
  Dim sPath As String
  Dim sFile As String
  Dim wb As Workbook

  sPath = "C:\...filepath...\Period 1\Daily Attributions\"
  sFile = Dir(sPath & "*.xlsx")

for i = 1 to 3
'pseudo code 
'"open foldername " & i
'looping through files in folder

Do While sFile <> ""

Set wb = Workbooks.Open(sPath & sFile, UpdateLinks:=False)

Range("E1").Select
ActiveCell.FormulaR1C1 = "FirstLast"
ActiveWorkbook.Save
ActiveWindow.Close
sFile = Dir

 Loop

next i

End Sub

score 1 · Answer 3 · answered Feb 20 '15 at 14:39

Sub M_snb()
  sn=split(createobject("wscript.shell").exec("cmd /c Dir ""C:\...filepath...\Period *\Daily Attributions\*.xls"" /b /s").stdout.readall,vbcrlf)

  for j=0 to ubound(sn)
    with getobject(sn(j))
      .sheets(1).cells(1,5)="firstlast"
      .close -1
    end with
  next
end sub

VBA to loop through files in subfolders

3 Answers3

This answer is the illustration of my comment above: