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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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"?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top