Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Listing Directory Contents in Worksheet
Is there a way to list the names of files within a directory in a worksheet?
I have a directory that contains 800 files and I would like to list them within a Excel worksheet. I have tried selecting all, copy and paste but nothing happens. Any help is greatly appreciated. Thanks, Paul |
#2
|
|||
|
|||
Paul,
One way Option Explicit '------------------------------------------------------------------------- Public Sub Hyperlinked_Folder_List() '------------------------------------------------------------------------- Const HFL_FOLDER As String = "C:\myTest" Dim oFSO As Object Dim cList As Long Dim aryfiles Dim iLevel As Long Dim i As Long Dim sFolder As String Dim oSheet As Worksheet Set oFSO = CreateObject("Scripting.FileSystemObject") aryfiles = Array() cList = -1: iLevel = 1 ReDim aryfiles(1, 0) SelectFiles aryfiles, oFSO, cList, iLevel, HFL_FOLDER On Error Resume Next Set oSheet = Worksheets("Files") On Error GoTo 0 If Not oSheet Is Nothing Then oSheet.Cells.ClearContents Else Worksheets.Add.Name = "Files" End If With ActiveSheet For i = LBound(aryfiles, 2) To UBound(aryfiles, 2) .Hyperlinks.Add Anchor:=.Cells(i + 1, aryfiles(1, i)), _ Address:=aryfiles(0, i), _ TextToDisplay:=aryfiles(0, i) Next .Columns("A:Z").EntireColumn.AutoFit End With Set oFSO = Nothing End Sub '-----------------------------**-----------------------------*-*------------ Private Sub SelectFiles(ByRef aryfiles, _ ByVal FSO As Object, _ ByRef pzList As Long, _ ByRef pzLevel As Long, _ ByVal pzPath As String) '-----------------------------**-----------------------------*-*------------ Dim oSubfolder As Object Dim oFolder As Object Dim oFile As Object Dim oFiles As Object Set oFolder = FSO.GetFolder(pzPath) Set oFiles = oFolder.Files For Each oFile In oFiles pzList = pzList + 1 ReDim Preserve aryfiles(1, pzList) aryfiles(0, pzList) = oFolder.Path & "\" & oFile.Name aryfiles(1, pzList) = pzLevel Next oFile pzLevel = pzLevel + 1 For Each oSubfolder In oFolder.Subfolders SelectFiles aryfiles, FSO, pzList, pzLevel, oSubfolder.Path Next oSubfolder pzLevel = pzLevel - 1 Set oFiles = Nothing Set oFolder = Nothing End Sub '------------------------------------------------------------------------- -- HTH RP (remove nothere from the email address if mailing direct) "Pablo" wrote in message ... Is there a way to list the names of files within a directory in a worksheet? I have a directory that contains 800 files and I would like to list them within a Excel worksheet. I have tried selecting all, copy and paste but nothing happens. Any help is greatly appreciated. Thanks, Paul |
#3
|
|||
|
|||
Paul,
You could run a macro, like the one below. This will prompt you to select a file within the folder of interest, and then list the file, date, and file size for all files found in the folder. HTH, Bernie MS Excel MVP Sub ListFolderContents() Dim i As Integer Dim myName As String With Application.FileSearch .NewSearch myName = Application.GetOpenFilename( _ Title:="Pick a file in your folder") .LookIn = Left(myName, InStrRev(myName, "\")) .SearchSubFolders = False .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute() 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count Cells(i, 1).Value = .FoundFiles(i) Cells(i, 2).Value = FileDateTime(.FoundFiles(i)) Cells(i, 3).Value = FileLen(.FoundFiles(i)) Next i Range("A:C").EntireColumn.AutoFit Else MsgBox "There were no files found." End If End With End Sub "Pablo" wrote in message ... Is there a way to list the names of files within a directory in a worksheet? I have a directory that contains 800 files and I would like to list them within a Excel worksheet. I have tried selecting all, copy and paste but nothing happens. Any help is greatly appreciated. Thanks, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Worksheet Function - Find? | Excel Worksheet Functions | |||
grand total column B from every worksheet in workbook | Excel Discussion (Misc queries) | |||
Linking items GREATER THAN O on another worksheet in the same Work | Excel Discussion (Misc queries) | |||
Worksheet name and Backward compatibility | Excel Discussion (Misc queries) |