Writing Workbook Filesnames in a Folder to a Spreadsheet

graham80

New Member
Joined
Jul 1, 2007
Messages
6
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??
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

au_jericho

New Member
Joined
Aug 16, 2004
Messages
40
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Code:
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
 

Forum statistics

Threads
1,181,102
Messages
5,928,061
Members
436,586
Latest member
latintxn

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
Top