Writing Workbook Filesnames in a Folder to a Spreadsheet

[graham80]

Hi all, I am attempting to read the filenames of workbooks contained in a folder into a spreadsheet...I have coded this:

Sub WriteXLSFilenames()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
.LookIn = "W:\Production\Work Orders\In Production\"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls"

Set Rng = wbCodeBook.Range("C3:E30")
aydata = Rng

If .Execute > 0 Then 'Workbooks in folder

For i = LBound(aydata) To UBound(aydata)

For lCount = 1 To .FoundFiles.Count 'Loop through all.

'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

aydata(i, 1) = "wbResults"

wbResults.Close SaveChanges:=True

Next lCount
Next i

End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

When ran, nothing happens...what have I done wrong??

 

[au_jericho]

I'm not sure what's happening with your code, but i picked this up a little while back and it works a treat for me - think it does what you're asking for:

Sheets(1).Select
ChDir "G:/" 'insert the directory here
MyRow = 1
MyFile = Dir("*.xls")
Do Until MyFile = ""
Cells(MyRow,1)=MyFile
MyRow = MyRow+1
MyFile = Dir
Cells(MyRow,1)=MyFile
Loop

 

[jindon]

try

Sub test()
Dim myList As String, fn As String, myDir As String
myDir = "W:\Production\Work Orders\In Production\"
fn = Dir(myDir & "*.xls")
Do While fn <> ""
     myList = myList & vbLf & fn
     fn = Dir()
Loop
MsgBox myList
End Sub

 

[graham80]

Thanks! It worked perfectly jindon!