Listing files in a MS Explorer folder in spreadsheet

drdrfaulkner

New Member
Joined
Oct 15, 2002
Messages
5
I have a folder in Windows Explorer containing numerous files I want to list in a spreadsheet without cutting and pasting them individually. Is there a way I can do this?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

wob

Board Regular
Joined
May 21, 2002
Messages
105
I use a similar routine at work.

How could I adapt this one to find out which subfolders are empty within a folder.

ie. c:temp has three folders; one, two, and three. the macro needs to list the empty folders only.

Cheers!
 

drdrfaulkner

New Member
Joined
Oct 15, 2002
Messages
5
I used the routine to make a macro in Excel and it ran. However, it did not place the filenames in separate cells, but listed the last one in the folder. Please tell me how to get the filenames in separate cells (preferably in a column format):

Sub Test()
Dim i As Long
With Application.FileSearch
.NewSearch
.LookIn = "T:gfxxyzabcPhotos"
.SearchSubFolders = False
.Filename = "*.*"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Cells(1, 1).Value = .FoundFiles(i)
Next i
End If
End With
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

My mistake - it should be:

Cells(i, 1).Value = .FoundFiles(i)

Don't know how that happened - I thought I copied from code I tested. Must have been tired.
 

Henrik Hougaard

New Member
Joined
Jun 16, 2002
Messages
14

ADVERTISEMENT

I am building a simple macro which is supposed to use the Excel files in a specific directory. Therefore I have tried to use the code posted above by Andrew Poulsom to get a list of files in my spreadsheet.

But it does not work.

When I debug I get an error message when I execute the line ' .FileName = "*.*" '. The error message is a run error 5 and the message is (translated) equal to "Call of procedure or argument is not valid".

This is the only code in my macro so far so what am I doing wrong?

Henrik
This message was edited by Henrik Hougaard on 2002-11-14 07:07
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The fact that there is an upper case N in FileName indicates that you are using Excel 97. In Excel 2000 AutoCorrect changes the upper case N to a lower case n (and Excel 97 does the opposite).

I ran the code in Excel 97 with no errors. I cannot find anything on the Microsoft KB to indicate that there is a bug corrected by a Service Release.

So I'm afraid I don't know why you get the error. Have you tried it with "*.xls"?
 

Forum statistics

Threads
1,144,060
Messages
5,722,276
Members
422,420
Latest member
losc

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