Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Listing Excel Files in a New Excel File 2

Status
Not open for further replies.

DanAuber

IS-IT--Management
Apr 28, 2000
255
FR
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

 
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
 
Another way, without external references:
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top