list workbook names except one
 

Hi all, below macro list all the workbook names in column "A" which
exists in a folder. What code line i should add in below macro that
if there is any worbook exists with the name "Closed.xls" in the
folder then it should not be listed with the other workbooks names.
Please can any friend can help

Sub Chk()
fldrnm = Sheets("INFO").TextBox3.Value
fName = Dir(fldrnm & "*.xlsm")
Do While fName < ""
c = c + 1
Cells(c, 1) = fName
fName = Dir()
Loop
End Sub

list workbook names except one
 

Replace this line of code...

Cells(c, 1) = fName

with this one...

If fName < "Closed.xls" Then Cells(c, 1) = fName

--
Rick (MVP - Excel)


"K" wrote in message
...
Hi all, below macro list all the workbook names in column "A" which
exists in a folder. What code line i should add in below macro that
if there is any worbook exists with the name "Closed.xls" in the
folder then it should not be listed with the other workbooks names.
Please can any friend can help

Sub Chk()
fldrnm = Sheets("INFO").TextBox3.Value
fName = Dir(fldrnm & "*.xlsm")
Do While fName < ""
c = c + 1
Cells(c, 1) = fName
fName = Dir()
Loop
End Sub

list workbook names except one
 

To be oicky (probably) a file called Closed.xls won't be listed anyway
because your search is for files with a .xlsm extension.

Not clear on whether you want to skip examining the whole folder if
there is a file called Closed.xls in it or just not include that single
file in the results..
For the former, untested:

Sub Chk()
fldrnm = Sheets("INFO").TextBox3.value
If Dir(fldrnm & "Closed.xls") = "" Then
fName = Dir(fldrnm & "*.xlsm")
Do While fName < ""
c = c + 1
Cells(c, 1) = fName
fName = Dir()
Loop
End If
End Sub


For the latter (untested):

Sub Chk()
fldrnm = Sheets("INFO").TextBox3.value
fname = Dir(fldrnm & "*.xlsm")
Do While fname < ""
If Right(fname, 10) < "Closed.xls" Then 'If Right(fname, 11) <
"Closed.xlsm" Then
c = c + 1
Cells(c, 1) = fname
End If
fname = Dir()
Loop
End Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=156502

Microsoft Office Help