Return list of file names from specific directory

[Chad]

I am in need of VBA code where I can return a list of file names located in a
specific directory. My workbook contains the directory path (e.g.,
O:\Budgets\2008) in cell A1 of sheet1. Cell B1 contains a header, so I'd
like the list of file names to begin in cell B2 and continue down column B
until all files are listed.

If there are only two files in this directory (e.g., Store1.xls &
Store2.xls), I'd expect Store1.xls to be listed in cell B2 and Store2.xls to
be listed in cell B3.

I don't want to hard code the directory path in the code. Instead I'd like
to keep it flexible and refer to the value in cell A1.

Any help would be greatly appreciated. Thank you.

 

[Norman Jones]

Hi Chad,

In a standard module, try:

'===========>>
Option Explicit

Private Sub UserForm_Initialize()
Dim WB As Workbook
Dim SH As Worksheet
Dim destRng As Range
Dim oFSO As Object
Dim oFolder As Object
Dim ofile As Object
Dim sFolderName As String
Dim i As Long

Const sPath As String = _
"C:\Users\Norman\Documents" '<<==== CHANGE

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
Set destRng = SH.Range("B2") '<<==== CHANGE

Set oFSO = CreateObject("Scripting.FileSystemObject")
sFolderName = sPath & Application.PathSeparator

On Error Resume Next
Set oFolder = oFSO.GetFolder(sFolderName)
On Error GoTo XIT
If Not oFolder Is Nothing Then
For Each ofile In oFolder.Files
destRng.Offset(i).Value = ofile.Name
i = i + 1
Next ofile
End If

XIT:
Set ofile = Nothing
Set oFolder = Nothing
Set oFSO = Nothing
End Sub
'<<===========

 

[Norman Jones]

Hi Chad,

Private Sub UserForm_Initialize()

Should have read:

Public Sub Tester()


(I happened to be working in a Userform
module when I respnded to your question!

 

[Chad]

Hi Chad,


Should have read:

Public Sub Tester()


(I happened to be working in a Userform
module when I respnded to your question!

Thanks Norman. This works great.