Listing Excel Files in a New Excel File 2

[DanAuber]

I have a directory with 100 Excel files in it (let's call it c:\data).
I want to write a macro (in a separate file) which creates a list of all the files in the directory c:\data in column A.

Thus if the files in C:\data were RHH100.xls RHH203.xls and RHH675.xls, my macro (in file C:\control\master.xls) would run and then Column A in Sheet 1 would have the three file names in it.

Can anyone help with this at all ?

Thanks

Dan

 

[sugarflux]

Hi Dan

In Visual Basic Editor set a reference to 'Microsoft Scripting Runtime' by going to Tools, References and ticking the box.

Paste the following into a module:
Dim fso As Scripting.FileSystemObject
Dim fldr As Folder, fl As File
Dim lngRow As Long
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("C:\")
lngRow = 0
For Each fl In fldr.Files
lngRow = lngRow + 1
Cells(lngRow, 1).FormulaR1C1 = fl.Path
Next
set fso = nothing

Hope this helps,
sugarflux

 

[PHV]

Another way, without external references:

Const cDir = "\path\to\dir\"
Dim strName As String
Dim lngRow As Long
lngRow = 0
strName = Dir(cDir & "*.xls")
While strName <> ""
  lngRow = lngRow + 1
  Cells(lngRow, 1).Value = cDir & strName
  strName = Dir()
Wend

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886