I needed a function to get the list of folders within a folder/directory,
from within Excel.
Looked like a piece of cake, but I was a bit more complex than I thought.
So I want to share it with you.
Here's the (VB) script, which, after adding 1 line, is perfectly useful in Notes too:

Function GetFolders(thepath As String)
Dim FolderArray() As Variant
Dim FolderCount As Integer
Dim FolderName As String
Const vbDirectory=16 'this line not needed in Excel

FolderCount = 0
FolderName = Dir(thepath & "\*", vbDirectory)
  'Loop until no more folders are found
Do While FolderName <> ""
If Not (FolderName = "." Or FolderName = "..") Then
If (Getattr(thepath & "\" & FolderName) And vbDirectory) = vbDirectory Then
FolderCount = FolderCount + 1
Redim Preserve FolderArray(1 To FolderCount)
FolderArray(FolderCount) = FolderName
End If
End If
FolderName = Dir()
Loop
GetFolders = FolderArray
End Function

You can call it like this in Notes:
Dim FolderArray As Variant
FolderArray=GetFolders("C:")
Msgbox Join(FolderArray,";")
Or like this in the immediate window in Excel:
Debug.Print Join(GetFolders("C:"),";")

Could be useful. If not, it's here for my sake ;-)

Category:  Lotus  Notes  Domino  | TechnoratiTechnorati: , ,