Creating an index of a directory using macros

[ChristianBacklund]

I would like to create a macro which will copy the names of the files and folders of a chosen directory into a spreadsheet, effectively creating an index. I don't know how to go about this or even if it is possible. I'm trying to avoid having to do it manually as the directory in question spans 1500 folders... Any help would be greatly appreciated

 

[s-o-s]

On 2002-07-11 03:28, ChristianBacklund wrote:
I would like to create a macro which will copy the names of the files and folders of a chosen directory into a spreadsheet, effectively creating an index. I don't know how to go about this or even if it is possible. I'm trying to avoid having to do it manually as the directory in question spans 1500 folders... Any help would be greatly appreciated

Christian,

The following will add a new sheet titled Index and then list down all the filenames in column A

Hope this helps.

Code deleted due to incorrect post...

see below...

_________________<MARQUEE/><A HREF= "http://website.lineone.net/~s-o-s/Index.html">

</MARQUEE>
This message was edited by s-o-s on 2002-07-11 07:25

 

[ChristianBacklund]

I pasted in the code that you sent me, and I got an error message back saying :
Compile Error: User-defined type not defined
All the while it has highlighted CAW As AnswerWizard
what can I do to make it work? Also, what variable specifies which directory it lifts the foldernames & filenames from?

 

[ChristianBacklund]

Alternatively, how can I get Excel to read in data from a file into a column? I can get an index into a file, but now i need Excel to read it in

 

[tusharm]

Three possibilites. In XL VBE help, check out

the DIR command, and its associated functions,

the FileSearch property/object and its associated properties/methods/objects

the FileSystemObject, which is part of the Windows Scripting Host. To enable the help/intellisense capability related to FSO, you should establish a reference to WSH (Tools | References...).

 

[s-o-s]

On 2002-07-11 06:12, ChristianBacklund wrote:
I pasted in the code that you sent me, and I got an error message back saying :
Compile Error: User-defined type not defined
All the while it has highlighted CAW As AnswerWizard
what can I do to make it work? Also, what variable specifies which directory it lifts the foldernames & filenames from?

Sorry about that lifted the wrong bit of code.

This should do it... theres a note in the file about setting the directory if needed

Sub IndexFiles()
    
Sheets.Add
ActiveSheet.Name = "Index"

With Application.FileSearch
    
' If you leave the lookin line out it will display this
' list for the current active directory.
    .LookIn = "c:my documents"
    .FileType = msoFileTypeExcelWorkbooks
    .Execute
End With
    
cnt = Application.FileSearch.FoundFiles.Count

For i = 1 To cnt
    rng = "A" & i
    Range(rng).Value = Application.FileSearch.FoundFiles.Item(i)
Next

End Sub

Hope this helps

ps When pasting there should only be a single backslash in the lookin line...I noticed the board has added a second slash.


_________________<MARQUEE/><A HREF= "http://website.lineone.net/~s-o-s/Index.html">

</MARQUEE>
This message was edited by s-o-s on 2002-07-11 07:27

 

[ChristianBacklund]

S.O.S.,
Your code works fine, although it only lists files, I would need to list folders as well (seen as how I have to create an automated inventory of a drive). Is there any way to do this with Excel? If so, how can I combine the two to give me both?
Thanks for your help

 

[s-o-s]

On 2002-07-11 07:48, ChristianBacklund wrote:
S.O.S.,
Your code works fine, although it only lists files, I would need to list folders as well (seen as how I have to create an automated inventory of a drive). Is there any way to do this with Excel? If so, how can I combine the two to give me both?
Thanks for your help

Yes use the search subfolders...here's the revised code.

Sub IndexFiles()
    
Sheets.Add
ActiveSheet.Name = "Index"

With Application.FileSearch
    ' Change as applicable.
    .LookIn = "C:MyDocuments"
    .FileType = msoFileTypeAllFiles
    .SearchSubFolders = True
    .Execute
End With
    
cnt = Application.FileSearch.FoundFiles.Count

For i = 1 To cnt
    rng = "A" & i
    Range(rng).Value = Application.FileSearch.FoundFiles.Item(i)
Next

End Sub

Time to go now...

 

[tusharm]

Try the foll. It has undergone limited testing. Change the starting directory in StartADir and run the sub.

Option Explicit

Sub doADirectory(whatDir As String, OutputCell As Range)
    Dim aFileName As String, FullName As String, i As Integer
    ReDim FolderList(1 To 1) As String
    
    aFileName = Dir(whatDir, &H1F)
        'cheating a bit here; look up DIR in XL VBE help for 2nd argument
    OutputCell.Value = whatDir
    Set OutputCell = OutputCell.Offset(1, 1)
    Do While aFileName <> ""
        If aFileName = "." Or aFileName = ".." Then
        Else
            FullName = whatDir & aFileName
            If (GetAttr(FullName) _
                And vbDirectory) = vbDirectory Then
                ReDim Preserve FolderList(LBound(FolderList) To UBound(FolderList) + 1)
                FolderList(UBound(FolderList)) = aFileName
            Else
                OutputCell.Value = aFileName
                Set OutputCell = OutputCell.Offset(1, 0)
                End If
            End If
        aFileName = Dir
        Loop
    For i = LBound(FolderList) + 1 To UBound(FolderList)
        doADirectory whatDir & FolderList(i) & Application.PathSeparator, OutputCell
        Set OutputCell = OutputCell.Offset(0, -1)
        Next i
    End Sub
Sub startADir()
    doADirectory "c:my documents", Range("a1")
    End Sub

 

[ChristianBacklund]

Tushar, your code works really well and I'm very pleased with it, but I'm wondering how to limit the file searches so that it only lists files with extensions .vsd, .pdf, .doc, .xls. I'd really appreciate any further help you can provide.
Thanks

PS. How can I make the code you supplied search a directory which the user inputs into a cell? This way the user can change it without having to go into the VBA code.
This message was edited by ChristianBacklund on 2002-07-12 04:11