list workbook names except one

K

K

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
 
R

Rick Rothstein

Replace this line of code...

Cells(c, 1) = fName

with this one...

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

p45cal

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top