The only problem with Don's code was that ThisRow was never defined. Once it
is defined, the code creates a list of file names. Good job, Don. i will
probably use this elsewhere.

"Gert-Jan" wrote:

Hi Don, thanks, but your solution doesn't work. I found this (history this
group), but I don't know how to change the last line: the message box should
not appear, data must be in list. Hope somone could help.

Sub AllExcelFilesInFolder()
Dim FileList() As String
Dim Counter As Long
Dim NextFile As String
Dim DirToSearch As String

DirToSearch = "C:\Exceldok" 'the folder name
Counter = 0


NextFile = Dir(DirToSearch & "\" & "*.xls")


Do Until NextFile = ""
ReDim Preserve FileList(Counter)
FileList(Counter) = DirToSearch & "\" & NextFile
Counter = Counter + 1
NextFile = Dir()
Loop


For Counter = LBound(FileList) To UBound(FileList)
MsgBox FileList(Counter)
'do your stuff here instead of previous line
Next
End Sub





"Don Guillett" schreef in bericht
...
This will list the files. Suggest doing this first and then another
for/each loop macro with FINDNEXT to remove the undesired entries.

Here is one you can adapt using DIR

Sub anotherfindfiles()
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim MediaFileLocation As String
MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
FN = Dir(MediaFileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub


--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
.. .
Hi,

Can someone help me with the following:

I want a macro that creates a file list (starting in Sheet1, A1) of all
excel-files in a specific directory. But: the file itself (test.xls)
should not appear in that list and also not the names of the files wich
have the same name as one of the sheets in test.xls.
For example: if there is a workbook "apple.xls" AND test.xls contains a
sheet called "apple", apple.xls should not appear in the filelist. If
apple is NOT one of the sheetnames, apple.xls must be showed in the list.

Any help would be appriciated.

Gert-Jan