Files names to excel with VBA?

[audrey]

Hello I have a list of files in a directory and I would like copy the name of the files to an excel sheet automatically.

Is there a smart way to do it thru macros ?

regards

audrey

 

[VoG]

Try like this: change the folder to suit

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
    MyFile = Dir
Loop
End Sub

 

[Trevor G]

Try this out

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> mefiles1()<br><SPAN style="color:#00007F">Dim</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><SPAN style="color:#007F00">'List files in a folder</SPAN><br><br>f = Dir("m:\Access Files\*.xls")<br><br>Range("A1").Activate<br><br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Len(f) > 0<br><br>ActiveCell.Formula = f<br><br>ActiveCell.Offset(1, 0).Select<br><br>f = Dir()<br><br><SPAN style="color:#00007F">Loop</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Andrew Poulsom]

Try (requires a reference to Microsoft Scripting Runtime):

Sub TestListFilesInFolder()
    Workbooks.Add ' create a new workbook for the file list
    Range("A1").Formula = "File Name:"
    Range("B1").Formula = "Date Last Modified:"
    ListFilesInFolder "C:\My Documents", True
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Range("A65536").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
        Cells(r, 1).Formula = FileItem.Path
        Cells(r, 2).Formula = FileItem.DateLastModified
        r = r + 1
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    Columns("A:H").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub

Change the folder path to suit.

 

[audrey]

thanks guys