Workbook name

[Guest]

If I have a list of Excel files in a specific folder, can anyone help me
write a macro to obtain their names and enter it in a range as a list? If
posible without opening the files.

Thanks.

 

[Guest]

Hi,

Try using this code:

Sub Button1_Click()
FillFileNames "C:\\", 1
End Sub

Public Sub FillFileNames(path As String, column As Integer)
Dim MyFile As String
Dim Counter As Integer
Counter = 1

MyFile = Dir$(path & "*.xls")
Do While MyFile <> ""
Sheet1.Cells(Counter, column).Value = MyFile
MyFile = Dir$
Counter = Counter + 1
Loop
End Sub

Hope this helps.

Peter

 

[Don Guillett]

One way
Sub FindExcelFiles()
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim FileLocation As String
FileLocation = "c:\ahorse\*.xls"
FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub