Make a file list using Excel

[Glen Charles Rowel]

What formula can I paste into Excel to have all the files in a folder (eg c:\in here) listed. Most of the files are TXT and HTML.

Thanks

Regards,

Glen Charles Rowell

 

[Glen Charles Rowel]

Some other people could use the same formula for music lists. :wink:

 

[Norie]

Glen

As far as I know this wouldn't be possible using formulae - you would need to use VBA.

Is that what you are looking for?

Something like this perhaps:

Sub ListTxtAndHTML()
Dim I As Integer
    ActiveSheet.Range("A1:B1") = Array("Text", "HTML")
    
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Papers\"
        .Filename = "*.txt*"
        .Execute
        
        For I = 1 To .FoundFiles.Count
            ActiveSheet.Range("A" & (I + 1)) = .FoundFiles(I)
        Next
    End With
 
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Papers\"
        .Filename = "*.htm*"
        .Execute
        
        For I = 1 To .FoundFiles.Count
            ActiveSheet.Range("b" & (I + 1)) = .FoundFiles(I)
        Next
    End With
End Sub

 

[Glen Charles Rowel]

Thank you. Do I just copy that into a cell? I've never used code

 

[Norie]

Do I just copy that into a cell?

No

If you really want to use it open the VBE with Alt+F11.

Then goto Insert>Module and paste the code there.

You can then run it using various methods, one of which is, from Excel, Tools>Macro...>Macros...

 

[Glen Charles Rowel]

Thank you. I will try it now

 

[Glen Charles Rowel]

That code was great but how do I hide the directory structure using the same code? I woul like only the file names to pop up.

 

[Norie]

Glen

I thought you might ask that.

What version of Excel are you using?

With Excel 2000 you could try this:

Sub ListTxtAndHTML()
Dim I As Integer
    ActiveSheet.Range("A1:B1") = Array("Text", "HTML")
    
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Papers\"
        .Filename = "*.txt*"
        .Execute
        
        For I = 1 To .FoundFiles.Count
            ActiveSheet.Range("A" & (I + 1)) = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "\") + 1)
        Next
    End With

    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Papers\"
        .Filename = "*.htm*"
        .Execute
        
        For I = 1 To .FoundFiles.Count
            ActiveSheet.Range("b" & (I + 1)) = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "\") + 1)
        Next
    End With
End Sub

 

[Glen Charles Rowel]

It worked a treat. Thank you very much.

 

[Glen Charles Rowel]

BTW what books could I look at to learn how to program?