Extract folder names only

Hello group,

I am using Application.FileSearch
I don't want to extract any files, only the folder names.

I am assembling a fine art project with jpg images.
I have approximately 500 artists spread over 11 art movements.
Each artist has its own folder and the folder name displays his name,
birthdate and death date. I want to extract these folder names into a
worksheet.

Can FileSearch be used for my application ?

Thanks,

Michael Singmin

Sub FolderList()
Dim iFolder As Long
Dim oFSO As Object
Dim oFolder As Object
Dim oFldr As Object

Set oFSO = CreateObject("Scripting.FileSystemobject")
Set oFolder = oFSO.getfolder("C:\Data")

For Each oFldr In oFolder.subfolders
iFolder = iFolder + 1
Cells(iFolder, "A").Value = oFldr.Name
Next oFldr

Set oFolder = Nothing
Set oFSO = Nothing

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Michael Singmin" <[email protected]> wrote in message
news:[email protected]...
> Hello group,
>
> I am using Application.FileSearch
> I don't want to extract any files, only the folder names.
>
> I am assembling a fine art project with jpg images.
> I have approximately 500 artists spread over 11 art movements.
> Each artist has its own folder and the folder name displays his name,
> birthdate and death date. I want to extract these folder names into a
> worksheet.
>
> Can FileSearch be used for my application ?
>
> Thanks,
>
> Michael Singmin
>

Michael,

I find the FileSystemObject much easier to use.
Below is slightly modified example from scripting help.
It adds a list to the active sheet of all folders contained in a specified
folder, including those with hidden and system file attributes set.
'------------------------------
Function ShowFolderList(folderspec)
Dim fso, f, f1, s, sf, n
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(folderspec)
Set sf = f.SubFolders
n = 1
For Each f1 In sf
s = f1.Name
Cells(n, 2).Value = s
n = n + 1
Next
ShowFolderList = s
End Function

'Run this to get things started.
'x must have a valid path
Sub MakeTheList()
Dim x As String
x = "C:\Documents and Settings\user\My Documents\Folder Name"
Call ShowFolderList(x)
End Sub
'-------------------------------

-OR-

You may want to try my free "List Files" Excel add-in.
It can create a list of folders meeting specified criteria.
Download it from here...
http://www.realezsites.com/bus/primitivesoftware


Regards,
Jim Cone
San Francisco, USA



"Michael Singmin" <[email protected]> wrote in message news:[email protected]...
Hello group,
I am using Application.FileSearch
I don't want to extract any files, only the folder names.
I am assembling a fine art project with jpg images.
I have approximately 500 artists spread over 11 art movements.
Each artist has its own folder and the folder name displays his name,
birthdate and death date. I want to extract these folder names into a
worksheet.
Can FileSearch be used for my application ?
Thanks,
Michael Singmin

try
Dir Function


Returns a String representing the name of a file, directory, or folder that
matches a specified pattern or file attribute, or the volume label of a
drive.

Syntax

Dir[(pathname[, attributes])]

The Dir function syntax has these parts:

Part Description
pathname Optional. String expression that specifies a file name - may
include directory or folder, and drive. A zero-length string ("") is
returned if pathname is not found.
attributes Optional. Constant or numeric expression, whose sum
specifies file attributes. If omitted, returns files that match pathname but
have no attributes.



Settings

The attributes argument settings are:

Constant Value Description
vbNormal 0 (Default) Specifies files with no attributes.
vbReadOnly 1 Specifies read-only files in addition to files with no
attributes.
vbHidden 2 Specifies hidden files in addition to files with no
attributes.
VbSystem 4 Specifies system files in addition to files with no
attributes. Not available on the Macintosh.
vbVolume 8 Specifies volume label; if any other attributed is
specified, vbVolume is ignored. Not available on the Macintosh.
vbDirectory 16 Specifies directories or folders in addition to files
with no attributes.
vbAlias 64 Specified file name is an alias. Available only on the
Macintosh.



Note These constants are specified by Visual Basic for Applications and
can be used anywhere in your code in place of the actual values.

Remarks

In Microsoft Windows, Dir supports the use of multiple character (*) and
single character (?) wildcards to specify multiple files. On the Macintosh,
these characters are treated as valid file name characters and can't be used
as wildcards to specify multiple files.

Since the Macintosh doesn't support the wildcards, use the file type to
identify groups of files. You can use the MacID function to specify file
type instead of using the file names. For example, the following statement
returns the name of the first TEXT file in the current folder:

Dir("SomePath", MacID("TEXT"))
To iterate over all files in a folder, specify an empty string:

Dir("")
If you use the MacID function with Dir in Microsoft Windows, an error
occurs.

Any attribute value greater than 256 is considered a MacID value.

You must specify pathname the first time you call the Dir function, or an
error occurs. If you also specify file attributes, pathname must be
included.

Dir returns the first file name that matches pathname. To get any additional
file names that match pathname, call Dir again with no arguments. When no
more file names match, Dir returns a zero-length string (""). Once a
zero-length string is returned, you must specify pathname in subsequent
calls or an error occurs. You can change to a new pathname without
retrieving all of the file names that match the current pathname. However,
you can't call the Dir function recursively. Calling Dir with the
vbDirectory attribute does not continually return subdirectories.

Tip Because file names are retrieved in no particular order, you may want
to store returned file names in an array, and then sort the array.

======

Dir Function Example
This example uses the Dir function to check if certain files and directories
exist. On the Macintosh, "HD:" is the default drive name and portions of the
pathname are separated by colons instead of backslashes. Also, the Microsoft
Windows wildcard characters are treated as valid file-name characters on the
Mac. However, you can use the MacID function to specify file groups.

Dim MyFile, MyPath, MyName
' Returns "WIN.INI" (on Microsoft Windows) if it exists.
MyFile = Dir("C:\WINDOWS\WIN.INI")

' Returns filename with specified extension. If more than one *.ini
' file exists, the first file found is returned.
MyFile = Dir("C:\WINDOWS\*.INI")

' Call Dir again without arguments to return the next *.INI file in the
' same directory.
MyFile = Dir

' Return first *.TXT file with a set hidden attribute.
MyFile = Dir("*.TXT", vbHidden)

' Display the names in C:\ that represent directories.
MyPath = "c:\" ' Set the path.
MyName = Dir(MyPath, vbDirectory) ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MyName <> "." And MyName <> ".." Then
' Use bitwise comparison to make sure MyName is a directory.
If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
Debug.Print MyName ' Display entry only if it
End If ' it represents a directory.
End If
MyName = Dir ' Get next entry.
Loop

--
Don Guillett
SalesAid Software
[email protected]
"Michael Singmin" <[email protected]> wrote in message
news:[email protected]...
> Hello group,
>
> I am using Application.FileSearch
> I don't want to extract any files, only the folder names.
>
> I am assembling a fine art project with jpg images.
> I have approximately 500 artists spread over 11 art movements.
> Each artist has its own folder and the folder name displays his name,
> birthdate and death date. I want to extract these folder names into a
> worksheet.
>
> Can FileSearch be used for my application ?
>
> Thanks,
>
> Michael Singmin
>

Thank you Bob, Jim and Don,

I will go with DIR as it seems the simplest and straightforward.
I note it only extracts the top level so I will have to iterate over
the first set of folders.

Many thanks for the other code which was very instructional.

Michael
====================================================


Michael Singmin <[email protected]> wrote:

>Hello group,
>
>I am using Application.FileSearch
>I don't want to extract any files, only the folder names.
>
>I am assembling a fine art project with jpg images.
>I have approximately 500 artists spread over 11 art movements.
>Each artist has its own folder and the folder name displays his name,
>birthdate and death date. I want to extract these folder names into a
>worksheet.
>
>Can FileSearch be used for my application ?
>
>Thanks,
>
>Michael Singmin