Copy Filenames

karlruff

Board Regular
Joined
Sep 24, 2003
Messages
91
Hello,
I'm looking for a piece of code that could copy all the names of files in a folder and place the list in excel.
Could anyone point me in the right direction?
Thx
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Try this:
Code:
Sub ListFilesInCurrentFolder()
'List all the filenames in the current folder in a new worksheet
Dim f()
Dim strTemp As String
Dim intCount As Integer
Dim ws As Worksheet
    
    intCount = 0
    strTemp = Dir("*.*")
    Do While strTemp <> Empty
        intCount = intCount + 1
        ReDim Preserve f(1 To intCount)
        f(intCount) = strTemp
        strTemp = Dir
    Loop

Set ws = Workbooks.Add.Worksheets(1)
ws.Cells(1, 1).Resize(UBound(f), 1).Value = _
            WorksheetFunction.Transpose(f)

End Sub

Let us know if this isn't quite right - output is to a new workbook, Sheet1. Regards.
 
Upvote 0
This works much faster then my other solution but is there a way to put the date the file was created on here?
 
Upvote 0
you can also replace the code

Dir("*.*")

with

Inputbox ("please enter directory here") & "\"

to get any directory you want instead.
'you need the backslash added onto the end else it wont work
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top