So here's the skinny. I have a workbook(#1) that I populate with Data from a
dozen or so different workbooks (A-Z). All of this dozen workbooks (A-Z) are
formatted the same and the data I need is in the exact same place for each
workbook.
A new workbook (just pick a letter of the alphabet) is generated once a day
and saved in the same Windows folder (STUFF).
Can I get #1 to search STUFF for folders A-Z and populate itself with the
data I want?

Yes.

Here's a modified VBA snippet from J.Walk, listing files in a
directory and sub-directories. You can use it to list files and dates
they were modified, then look through the dates for recent ones, open
them, and pull the data you need. It doesn't understand greek letters
as I discovered, so careful if you are using unicode filenames. 4000
files takes about 20 seconds, in Excel 2007 under Windows XP. Put
this in a standard module. It uses the active sheet to create the
file list (in John's example there's a button on a worksheet that
initiates the sub), so adjust accordingly if needed, keeping in mind
that the main function is recursive. Calling GetAllFiles pops up a
dialog box to browse for folder; replace this portion with a pathname
to your STUFF if it's always in the same place - GetDirectory
encapsulates that portion.


'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As
String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type


Sub GetAllFiles()
Dim Msg As String
Dim Directory As String

Application.ScreenUpdating = False

Msg = "Select the folder for the recursive directory listing."
Directory = GetDirectory(Msg)
If Directory = "" Then Exit Sub
If Right(Directory, 1) < "\" Then Directory = Directory & "\"
Cells.ClearContents
Call RecursiveDir(Directory)

Application.ScreenUpdating = True
End Sub

Public Sub RecursiveDir(ByVal CurrDir As String)
Dim Dirs() As String
Dim NumDirs As Long
Dim FileName As String
Dim PathAndName As String
Dim i As Long

' Make sure path ends in backslash
If Right(CurrDir, 1) < "\" Then CurrDir = CurrDir & "\"

' Put column headings on active sheet
Cells(1, 1) = "Filename"
Cells(1, 2) = "Date/Time"
Range("A1:B1").Font.Bold = True

' Get files
FileName = Dir(CurrDir & "*.*", vbDirectory)
Do While Len(FileName) < 0
If Left(FileName, 1) < "." Then 'Current dir
PathAndName = CurrDir & FileName
If (GetAttr(PathAndName) And vbDirectory) = vbDirectory Then
'store found directories
ReDim Preserve Dirs(0 To NumDirs) As String
Dirs(NumDirs) = PathAndName
NumDirs = NumDirs + 1
Else
'Write the path and file to the sheet
Cells(WorksheetFunction.CountA(Range("A:A")) + 1, 1) =
CurrDir & FileName
Cells(WorksheetFunction.CountA(Range("B:B")) + 1, 2) =
FileDateTime(PathAndName)
End If
End If
FileName = Dir()
Loop
' Process the found directories, recursively
For i = 0 To NumDirs - 1
DoEvents
RecursiveDir Dirs(i)
Next i
End Sub

Function GetDirectory(Optional Msg) As String
Dim bInfo As BROWSEINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer

' Root folder = Desktop
bInfo.pidlRoot = 0&

' Title in the dialog
If IsMissing(Msg) Then
bInfo.lpszTitle = "Select a folder."
Else
bInfo.lpszTitle = Msg
End If

' Type of directory to return
bInfo.ulFlags = &H1

' Display the dialog
x = SHBrowseForFolder(bInfo)

' Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function








On Oct 25, 2:39 pm, B Diggity <B
wrote:
So here's the skinny. I have a workbook(#1) that I populate with Data from a
dozen or so different workbooks (A-Z). All of this dozen workbooks (A-Z) are
formatted the same and the data I need is in the exact same place for each
workbook.
A new workbook (just pick a letter of the alphabet) is generated once a day
and saved in the same Windows folder (STUFF).
Can I get #1 to search STUFF for folders A-Z and populate itself with the
data I want?