For Each folder, workbook, worksheet

[Paul Morris]

Hi All,

Am looking to get pieces of data from each tab of multiple workbooks in multiple folders - e.g. there are 5 folders (A, B, C, D, E) sitting under C:\Stuff, and each of these folders have 4 workbooks.

How do I use the for each statement with respect to folders, and workbooks?

Cheers,
Paul

 

[DominicB]

Good evening Paul Morris

You've been a bit vague here about what you want to do with the files so I can't really tailor the answer to your needs. I suggest you visit Debra Dalgleish's website which gives an example of each of the situations you have described.

http://www.contextures.com/xlfaqMac.html#LoopSheets

HTH

DominicB

 

[Paul Morris]

Thanks - except now the variable "File" returns a filename of a file on my desktop, not a file from the specified pathname (have checked, and it does recognise that the pathname is as it should be). Any idea why this is happening?



Sub UpdateSummary()

Dim w As Workbook
Dim wb As Workbook
Dim File As String
Dim uPath As String
Dim UpdateFolder As String
Dim s As Worksheet

Set w = ThisWorkbook
Set s = w.Sheets(1)

UpdateFolder = s.Range("J1")

uPath = w.Path
File = "a"


Do While File <> ""
ChDir uPath & "\" & UpdateFolder
File = Dir("")
Set wb = Workbooks.Open(uPath & "\" & UpdateFolder & "\" & File)