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

Hi Gert-Jan,

well, you got to compare the names of the
workbooks in the folder with the name
of the active workbook and, if unequal
to the name of the active workbook, with
the names of the worksheets in the active workbook.

What code have you got so far?


--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

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
(E-Mail Removed)
"Gert-Jan" <(E-Mail Removed)> wrote in message
news:4560bdb3$0$28046$(E-Mail Removed)...
> 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
>
>
>

Sub ListFiles()
Dim sPath as String, sName as STring
Dim sName1 as String, bFound as Boolean
Dim i as Long, sh as Worksheet
sPath = Thisworkbook.Path
if right(sPath,1) <> "\" then sPath = sPath & "\"
sName = Dir(sPath & "*.xls")
do while sName <> ""
sName1 = Left(sName,len(sName)-4)
if lcase(sName) <> lcase(ThisWorkbook.Name) then
bFound = False
for each sh in Thisworkbook.Worksheets
if lcase(sName1) = lcase(sh.name) then
bfound = True
exit for
end if
Next
if not bFound then
i = i + 1
with worksheets("Sheet1")
.Cells(i,1) = sName
end with
end if
end if
sName = Dir
Loop
End Sub

Untested pseudo code should get you started.

--
Regards,
Tom Ogilvy

"Gert-Jan" <(E-Mail Removed)> wrote in message
news:4560bdb3$0$28046$(E-Mail Removed)...
> 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
>
>
>

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" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
> 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
> (E-Mail Removed)
> "Gert-Jan" <(E-Mail Removed)> wrote in message
> news:4560bdb3$0$28046$(E-Mail Removed)...
>> 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
>>
>>
>>
>
>

Thanks Tom, this works fine!

Regards, Gert-Jan

"Tom Ogilvy" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
> Sub ListFiles()
> Dim sPath as String, sName as STring
> Dim sName1 as String, bFound as Boolean
> Dim i as Long, sh as Worksheet
> sPath = Thisworkbook.Path
> if right(sPath,1) <> "\" then sPath = sPath & "\"
> sName = Dir(sPath & "*.xls")
> do while sName <> ""
> sName1 = Left(sName,len(sName)-4)
> if lcase(sName) <> lcase(ThisWorkbook.Name) then
> bFound = False
> for each sh in Thisworkbook.Worksheets
> if lcase(sName1) = lcase(sh.name) then
> bfound = True
> exit for
> end if
> Next
> if not bFound then
> i = i + 1
> with worksheets("Sheet1")
> .Cells(i,1) = sName
> end with
> end if
> end if
> sName = Dir
> Loop
> End Sub
>
> Untested pseudo code should get you started.
>
> --
> Regards,
> Tom Ogilvy
>
> "Gert-Jan" <(E-Mail Removed)> wrote in message
> news:4560bdb3$0$28046$(E-Mail Removed)...
>> 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
>>
>>
>>
>
>

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" <(E-Mail Removed)> schreef in bericht
> news:(E-Mail Removed)...
> > 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
> > (E-Mail Removed)
> > "Gert-Jan" <(E-Mail Removed)> wrote in message
> > news:4560bdb3$0$28046$(E-Mail Removed)...
> >> 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
> >>
> >>
> >>
> >
> >
>
>
>

I just re-tested as presented and it worked just fine

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"fugazi48" <(E-Mail Removed)> wrote in message
news:EB75BF7C-7C31-42C8-8721-(E-Mail Removed)...
> 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" <(E-Mail Removed)> schreef in bericht
>> news:(E-Mail Removed)...
>> > 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
>> > (E-Mail Removed)
>> > "Gert-Jan" <(E-Mail Removed)> wrote in message
>> > news:4560bdb3$0$28046$(E-Mail Removed)...
>> >> 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
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>>