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]

Is there a way to use the same code but have the result looking like this below?

<option VALUE="../a.htm">a</option>

 

[Norie]

Glen

Whatever you posted hasn't appeared I'm afraid?

 

[Glen Charles Rowel]

<option VALUE="../a.htm">a</option>

 

[Glen Charles Rowel]

BTW I'm making an index for a JavaScript dictionary. That's why I'm trying to find out how to do this. Typing in 108,000 lines like this would take a long time. :wink:

 

[Glen Charles Rowel]

Are you online everyday Norie?

 

[Norie]

Glen

I'm still a little unsure exactly what you're looking for but is it something along these lines:

Sub ListTxtAndHTML()
Dim I As Long
Dim strFileName As String
Dim strFriendlyName As String

    ActiveSheet.Range("A1:B1") = Array("Text", "HTML")
    
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Papers\"
        .Filename = "*.txt*"
        .Execute
        
        For I = 1 To .FoundFiles.Count
        
            strFileName = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "\") + 1)
            strFriendlyName = Left(strFileName, InStrRev(strFileName, ".") - 1)
            
            ActiveSheet.Range("A" & (I + 1)) = "<option VALUE=" & Chr(34) & "../" & strFileName & Chr(34) & ">" & strFriendlyName & "</option>"

        Next
    End With

    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Papers\"
        .Filename = "*.htm*"
        .Execute
        
        For I = 1 To .FoundFiles.Count
        
            strFileName = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "\") + 1)
            strFriendlyName = Left(strFileName, InStrRev(strFileName, ".") - 1)
            
            ActiveSheet.Range("B" & (I + 1)) = "<option VALUE=" & Chr(34) & "../" & strFileName & Chr(34) & ">" & strFriendlyName & "</option>"
        Next
    End With
End Sub

 

[Glen Charles Rowel]

That worked perfectly and with these examples I should be able to easily change anything that doesn't work. Thank you sooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo much.

Glen Charles Rowell

 

[spammy]

Need help

Hi there

I've been looking at this script and it is very helpful. The thing I need is a modified version of this.

I want to create an index of all files and all subfolders in a specific folder.

For example. In column A would be a number (that goes up)
In column B would be the full path to the file name
In column C would be the folder name that the file is in
In column D would be the file name
In column E would be the file type

I also want to make all folder names "linkable" - meaning that when I click on that file name - it will open up that file (Like the Ctrl-K link file thing)

ALSO - to make things more complicated.. I need to have the links to the file name dynamic.... so if I move this index somewhere else with all the files.... the links will still work (This part is not necessary though - because I could just run the script again)

PLEASE help

Thank you!

 

[Norie]

spammy

I suggest you create a new post with your specific question.

You could put a likn back to this thread for reference.