Programatically Get A List Of Subfolders

[Tom]

How do you programatically get a list of subfolders in a folder?

Thanks!

Tom

[deko]

The short answer is to use the Microsoft Scripting Runtime.

The function below has the code you're looking for, but is probably more
than you were looking for. What I did was log each subfolder name to a
table. You could use a collection, which is what I did in this function
before I switched to the Logger function - which is simply a db.Execute
(INSERT....) command.

Public Function GetPath(strRoot As String, blnDig As Boolean) As Boolean
'Required Reference: Microsoft Scripting Runtime
On Error GoTo HandleErr
Dim strJobFolder As String
Dim strJobFile As String
Dim str1 As String
Dim str2 As String
Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.folder
Dim fldSub As Scripting.folder
Set fso = New Scripting.FileSystemObject
If Not fso.FolderExists(strRoot) Then Err.Raise INVALID_PATH
Set fld = fso.GetFolder(strRoot)
If blnDig Then
'================================================= =====
' process multiple Job Collections
'================================================= =====
For Each fldSub In fld.SubFolders
strJobFolder = fldSub.Path
strJobFile = fso.BuildPath(fldSub.Path, "MEAS.MDB")
If fso.FileExists(strJobFile) Then
Call basHandler.Logger(strJobFile, fldSub.Name, , , , , 140)
Else
str1 = "No Meas.mdb found in " & fldSub.Name
str2 = fso.GetParentFolderName(fldSub)
Call basHandler.Logger(str1, str2, , , , , 40)
End If
Call GetPath(strJobFolder, blnDig)
Next fldSub
Else
'================================================= =====
' process Job Files in selected directory only
'================================================= =====
For Each fldSub In fld.SubFolders
strJobFolder = fso.GetAbsolutePathName(fldSub)
strJobFile = fso.BuildPath(strJobFolder, "MEAS.MDB")
If fso.FileExists(strJobFile) Then
Call basHandler.Logger(strJobFile, fldSub.Name, , , , , 140)
Else
str1 = "No Meas.mdb found in " & strJobFolder
str2 = fldSub.Name
Call basHandler.Logger(str1, str2, , , , , 40)
End If
Next fldSub
End If
GetPath = True
Exit_Here:
On Error Resume Next
Set fld = Nothing
Set fso = Nothing
Exit Function
HandleErr:
Select Case Err.Number
Case INVALID_PATH
MsgBox "Could not validate Path to Data Files.", vbCritical, "
Invalid Path"
Case Else
MsgBox Err.Description & vbCrLf & vbCrLf & "Error Number " &
Err.Number & _
" at basJobFilePath.GetPath", vbExclamation, " Unexpected
Error"
End Select
GetPath = False
Resume Exit_Here
End Function

[Tom]

Will the Dir function work?

Something like ....
Dir("C:\MySubfolder\",vbDirectory)

Tom
"deko" <de**@nospam.com> wrote in message
news:mv***************@newssvr13.news.prodigy.com. ..

The short answer is to use the Microsoft Scripting Runtime.

The function below has the code you're looking for, but is probably more
than you were looking for. What I did was log each subfolder name to a
table. You could use a collection, which is what I did in this function
before I switched to the Logger function - which is simply a db.Execute
(INSERT....) command.

Public Function GetPath(strRoot As String, blnDig As Boolean) As Boolean
'Required Reference: Microsoft Scripting Runtime
On Error GoTo HandleErr
Dim strJobFolder As String
Dim strJobFile As String
Dim str1 As String
Dim str2 As String
Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.folder
Dim fldSub As Scripting.folder
Set fso = New Scripting.FileSystemObject
If Not fso.FolderExists(strRoot) Then Err.Raise INVALID_PATH
Set fld = fso.GetFolder(strRoot)
If blnDig Then
'================================================= =====
' process multiple Job Collections
'================================================= =====
For Each fldSub In fld.SubFolders
strJobFolder = fldSub.Path
strJobFile = fso.BuildPath(fldSub.Path, "MEAS.MDB")
If fso.FileExists(strJobFile) Then
Call basHandler.Logger(strJobFile, fldSub.Name, , , , , 140) Else
str1 = "No Meas.mdb found in " & fldSub.Name
str2 = fso.GetParentFolderName(fldSub)
Call basHandler.Logger(str1, str2, , , , , 40)
End If
Call GetPath(strJobFolder, blnDig)
Next fldSub
Else
'================================================= =====
' process Job Files in selected directory only
'================================================= =====
For Each fldSub In fld.SubFolders
strJobFolder = fso.GetAbsolutePathName(fldSub)
strJobFile = fso.BuildPath(strJobFolder, "MEAS.MDB")
If fso.FileExists(strJobFile) Then
Call basHandler.Logger(strJobFile, fldSub.Name, , , , , 140) Else
str1 = "No Meas.mdb found in " & strJobFolder
str2 = fldSub.Name
Call basHandler.Logger(str1, str2, , , , , 40)
End If
Next fldSub
End If
GetPath = True
Exit_Here:
On Error Resume Next
Set fld = Nothing
Set fso = Nothing
Exit Function
HandleErr:
Select Case Err.Number
Case INVALID_PATH
MsgBox "Could not validate Path to Data Files.", vbCritical, "
Invalid Path"
Case Else
MsgBox Err.Description & vbCrLf & vbCrLf & "Error Number " &
Err.Number & _
" at basJobFilePath.GetPath", vbExclamation, " Unexpected
Error"
End Select
GetPath = False
Resume Exit_Here
End Function

[deko]

> Will the Dir function work?

But what if there are subdirectories in your subdirectory?

[Tom]

I meant to write this as ......
Dir("C:\MyFolder\",vbDirectory)

and find the subfolders in MyFolder. I wouldn't expect the function to list
subfolders in the subfolders.

Tom

"deko" <de**@nospam.com> wrote in message
news:OD**************@newssvr13.news.prodigy.com.. .

Will the Dir function work?

But what if there are subdirectories in your subdirectory?

[deko]

>I meant to write this as ......

Dir("C:\MyFolder\",vbDirectory)

and find the subfolders in MyFolder. I wouldn't expect the function to
list
subfolders in the subfolders.

I would still use the Scripting Runtime. But if Dir works for you...

[Larry Linson]

"Tom" wrote

Dir("C:\MyFolder\",vbDirectory)

and find the subfolders in MyFolder.
I wouldn't expect the function to list
subfolders in the subfolders.

Yes, this should do what you want. You have to use a looping structure, with
some care, to find all the folders in the folder hierarchy with Dir.

However, a caution about the File System Object -- it requires the Windows
Scripting Host to execute. Some company's System Administrators remove the
WSH because it can be used as a vehicle for "mischief" by viruses or
worms -- and, if that is the case, using FSO it will just cause an error.

Larry Linson
Microsoft Access MVP

[Tom]

When I substitute an actual folder name for "MyFolder" and run:
MsgBox Dir("C:\MyFolder\",vbDirectory)
It comes up blank. Why is this?

Thanks!
"Larry Linson" <bo*****@localhost.not> wrote in message
news:O3lAe.1982$Gk4.457@trnddc01...

"Tom" wrote

> Dir("C:\MyFolder\",vbDirectory)
>
> and find the subfolders in MyFolder.
> I wouldn't expect the function to list
> subfolders in the subfolders.
Yes, this should do what you want. You have to use a looping structure,

with some care, to find all the folders in the folder hierarchy with Dir.

However, a caution about the File System Object -- it requires the Windows
Scripting Host to execute. Some company's System Administrators remove the
WSH because it can be used as a vehicle for "mischief" by viruses or
worms -- and, if that is the case, using FSO it will just cause an error.

Larry Linson
Microsoft Access MVP

[Tom]

The above was not coming up blank after all. I was seeing "." and ".." for
the current and encompassing directories. The following codes returns all
the subfolders in MyFolder:
Dim Path As String
Dim PictureSubfolder As String
Path = "C:\Housing Survey Database\Housing Survey Pictures\"
PictureSubfolder = Dir(Path, vbDirectory)
Do While Len(PictureSubfolder) > 0
‘ Ignore the current and encompassing directories
If (PictureSubfolder <> ".") And (PictureSubfolder <> "..") Then
MsgBox PictureSubfolder
End If
PictureSubfolder = Dir()
Loop

Tom
"Tom" <no********@address.net> wrote in message
news:G9*****************@newsread1.news.atl.earthl ink.net...

When I substitute an actual folder name for "MyFolder" and run:
MsgBox Dir("C:\MyFolder\",vbDirectory)
It comes up blank. Why is this?

Thanks!
"Larry Linson" <bo*****@localhost.not> wrote in message
news:O3lAe.1982$Gk4.457@trnddc01...

"Tom" wrote

> Dir("C:\MyFolder\",vbDirectory)
>
> and find the subfolders in MyFolder.
> I wouldn't expect the function to list
> subfolders in the subfolders.

Yes, this should do what you want. You have to use a looping structure,

with

some care, to find all the folders in the folder hierarchy with Dir.

However, a caution about the File System Object -- it requires the Windows Scripting Host to execute. Some company's System Administrators remove the WSH because it can be used as a vehicle for "mischief" by viruses or
worms -- and, if that is the case, using FSO it will just cause an error.
Larry Linson
Microsoft Access MVP

[Trevor Best]

deko wrote:

I meant to write this as ......
Dir("C:\MyFolder\",vbDirectory)

and find the subfolders in MyFolder. I wouldn't expect the function to
list
subfolders in the subfolders.

I would still use the Scripting Runtime. But if Dir works for you...

Dir works with a bit of recursion, it also doesn't get stopped by anti
virus programs :-)

--
[OO=00=OO]