I would like to archive a list of folders that are on data CDs that we burn
.... Is there a way to do it with excel? I¹ve written a macro to rip out
filenames from a specific folder, but folder names themselves is interesting
problem.

I would also like sub-folders (and sub-sub etc) if possible.

Any ideas or even a small app that does it for you that isn¹t excel??

Thanks a million ...

Sub newfilesearchthing()
Dim LoopCount As Integer
Dim MsgStr As String

With Application.FileSearch
.NewSearch
.LookIn = "D:\"
.SearchSubFolders = True
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
ActiveCell.Value = "There were " & .FoundFiles.Count & _
" file(s) found."
For LoopCount = 1 To .FoundFiles.Count
ActiveCell.Value = .FoundFiles(LoopCount)
ActiveCell.Offset(1, 0).Activate
Next
Else
'MsgStr = "There were no files found."
End If
'MsgBox MsgStr
End With

End Sub


"Brad" wrote:

I would like to archive a list of folders that are on data CDs that we burn
.... Is there a way to do it with excel? I¹ve written a macro to rip out
filenames from a specific folder, but folder names themselves is interesting
problem.

I would also like sub-folders (and sub-sub etc) if possible.

Any ideas or even a small app that does it for you that isn¹t excel??

Thanks a million ..

Brad,

My Excel add-in "List Files" does that.
It searches for the file type you specify or for all files.
Files and folders in the specified directory are listed.
Sub-folders also listed if specified by user
File name, size, date, type are provided
Directories/folder path is listed in one column and files in the adjacent column.
Each file name on the list is hyperlinked.
Available - free - upon direct request.
Remove xxx from my email address

Regards,
Jim Cone
San Francisco, CA
XX


"Brad" wrote in message ...
I would like to archive a list of folders that are on data CDs that we burn
... Is there a way to do it with excel? I¹ve written a macro to rip out
filenames from a specific folder, but folder names themselves is interesting
problem.
I would also like sub-folders (and sub-sub etc) if possible.
Any ideas or even a small app that does it for you that isn¹t excel??
Thanks a million ...

The following code was posted by Bill Manville some time ago. You should be
able to make a few changes to get a list of directories only (just remove the
section that saves the names of individual files).

Option Base 1
Dim aFiles() As String, iFile As Integer

Sub ListAllFilesInDirectoryStructure()
Dim Counter As Integer
iFile = 0
ListFilesInDirectory "c:\test\" ' change the top level as you wish

For Counter = 1 To iFile
Worksheets("Sheet1").Cells(Counter, 1).Value = aFiles(Counter)
Next

End Sub

Sub ListFilesInDirectory(Directory As String)
Dim aDirs() As String, iDir As Integer, stFile As String

' use Dir function to find files and directories in Directory
' look for directories and build a separate array of them
' note that Dir returns files as well as directories when vbDirectory
specified
iDir = 0
stFile = Directory & Dir(Directory & "*.*", vbDirectory)
Do While stFile < Directory
If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then
' do nothing - GetAttr doesn't like these directories
ElseIf GetAttr(stFile) = vbDirectory Then
' add to local array of directories
iDir = iDir + 1
ReDim Preserve aDirs(iDir)
aDirs(iDir) = stFile
Else
' add to global array of files
iFile = iFile + 1
ReDim Preserve aFiles(iFile)
aFiles(iFile) = stFile
End If
stFile = Directory & Dir()
Loop

' now, for any directories in aDirs call self recursively
If iDir 0 Then
For iDir = 1 To UBound(aDirs)
ListFilesInDirectory aDirs(iDir) & Application.PathSeparator
Next iDir
End If
End Sub

On Thu, 18 Nov 2004 11:14:27 +1000, Brad wrote:

I would like to archive a list of folders that are on data CDs that we burn
... Is there a way to do it with excel? I¹ve written a macro to rip out
filenames from a specific folder, but folder names themselves is interesting
problem.

I would also like sub-folders (and sub-sub etc) if possible.

Any ideas or even a small app that does it for you that isn¹t excel??

Thanks a million ...

Is a folder list extraction possible?I have an example on my website: List (Sub)Folders


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Brad" wrote in message ...
I would like to archive a list of folders that are on data CDs that we burn ... Is there a way to do it with excel? I've written a macro to rip out filenames from a specific folder, but folder names themselves is interesting problem.

I would also like sub-folders (and sub-sub etc) if possible.

Any ideas or even a small app that does it for you that isn't excel??

Thanks a million ...