can i get a list of files in specified folder?

[merlin_the_magician]

In order to create an external cell reference that will not result in highly annoying errors I need a formula or VBA code that will allow me to retrieve the filenames in a specified path. I know this is possible, just how?

 

[merlin_the_magician]

It works fine now. I forgot to actually run the macro. (talking stupid....):D

One more thing, can i have this run automatically at file opening?

 

[WillR]

Yeah,

place the code in the This Workbook code pane

Replace the name of the macro as per below

Private Sub Workbook_Open()
    Dim fileList() As String
    Dim fName As String
    Dim fPath As String
    Dim I As Integer
   
    fPath = "C:\My Documents\"
    fName = Dir(fPath & "*.xls")
    While fName <> ""
        I = I + 1
        ReDim Preserve fileList(1 To I)
        fileList(I) = fName
        fName = Dir()
    Wend
    If I = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     For I = 1 To UBound(fileList)
        Range("A" & I).Value = fileList(I)
     Next
End Sub

Will[/code]

 

[merlin_the_magician]

Thank you very much, this really helps solving many problems!