Listing files in a MS Explorer folder in spreadsheet

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Oct 16, 2002

• #2

Try this:

Code:

Sub Test()
    Dim i As Long
    With Application.FileSearch
        .NewSearch
'       *** Change Folder name to suit ***
        .LookIn = "C:My Documents"
        .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

Or here's another way:

http://www.mrexcel.com/board/viewtopic.php?topic=21442&forum=2

 

Upvote 0

W

wob

Board Regular

Joined

May 21, 2002

Messages

105

• Oct 16, 2002

• #3

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

D

drdrfaulkner

New Member

Joined

Oct 15, 2002

Messages

5

• Oct 16, 2002

• #4

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

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Oct 17, 2002

• #5

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

D

drdrfaulkner

New Member

Joined

Oct 15, 2002

Messages

5

• Oct 17, 2002

• #6

That did it. Thanks.

 

Upvote 0

H

Henrik Hougaard

New Member

Joined

Jun 16, 2002

Messages

14

• Nov 14, 2002

• #7

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

H

Henrik Hougaard

New Member

Joined

Jun 16, 2002

Messages

14

• Nov 15, 2002

• #8

This message was edited by Henrik Hougaard on 2002-11-15 04:12

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Nov 15, 2002

• #9

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