write the name of all excel file in a folder in an excel file

[uktous]

Hi,

Could you please write me the macro?


I want to open each workbook in a folder, and then input the name of the opened folder in a particular workbook, and then close the workbook.

After I run the macro, the names for each workbook will be input in a particular workbook.





For each excel workbook in a folder

Step1:
Open a workbook.
Step2:
Write the name of the workbook in a particular workbook (says 1.xlsm).
Step3:
Close a workbook.

Next





Thanks

[Bob Phillips]

Public Sub GetFilenames()
Const ROOT_FOLDER As String = "C:\temp\"
Dim Filename As String
Dim filenames As Variant
Dim cnt As Long

Filename = Dir(ROOT_FOLDER & "*.xls*")
ReDim filenames(1 To 1)

Do While Filename <> ""

cnt = cnt + 1
ReDim Preserve filenames(1 To cnt)
filenames(cnt) = Filename
Filename = Dir
Loop

Workbooks.Add
With ActiveWorkbook

.Worksheets(1).Range("A1").Resize(cnt).Value = Application.Transpose(filenames)
.SaveAs ROOT_FOLDER & "Filenames"
.Close
End With
End Sub