List out FileNames.xls with K4 Blank
 

I'm trying to edit some existing (similar) code that will
Search all files (all.xls) in a folder and list out on my current sheet from
A4 downward
all files where the search finds Cell "K4" in each file to be Blank.
Posting my code would be near useless.
I'm been to google and help and am now exhausted.. Can anyone assist?

List out FileNames.xls with K4 Blank
 

Here's some code, but be aware that the workbook could contain many
worksheets. This code just looks at sheet 1

Sub ShowSomeFiles()
Dim i As Long, j As Long

Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\MyTest"
.Filename = ".xls"
.SearchSubFolders = False
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
If IsEmpty(ActiveWorkbook.Worksheets(1).Range("K4").V alue) Then
Range("A4").Offset(j, 0).Value = .FoundFiles(i)
j = j + 1
End If
ActiveWorkbook.Close savechanges:=False
Next i
End With
Application.ScreenUpdating = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:Q3GAb.49607$yM6.26347@lakeread06...
I'm trying to edit some existing (similar) code that will
Search all files (all.xls) in a folder and list out on my current sheet
from
A4 downward
all files where the search finds Cell "K4" in each file to be Blank.
Posting my code would be near useless.
I'm been to google and help and am now exhausted.. Can anyone assist?

List out FileNames.xls with K4 Blank
 

Bob: Thanks for the code -- however purposely blanking out cell K4 in one
of my workbooks does not list that filename beginning in my A4 of my "home"
Worksheet. My A4 continues blank - should have one file
C:\My Documents\CMyGroup\4126Jan2003.xls, but doesn't.

This line (below) is suspect: Does the code know to write the value of
..FoundFiles(i) to my Originating Workbook and Worksheet (Sheet1) as this
line is within the loop?

Range("A4").Offset(j, 0).Value = .FoundFiles(i)

Any thoughts?
Thanks,
JMay


"Bob Phillips" wrote in message
...
Here's some code, but be aware that the workbook could contain many
worksheets. This code just looks at sheet 1

Sub ShowSomeFiles()
Dim i As Long, j As Long

Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\MyTest"
.Filename = ".xls"
.SearchSubFolders = False
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
If IsEmpty(ActiveWorkbook.Worksheets(1).Range("K4").V alue)
Then
Range("A4").Offset(j, 0).Value = .FoundFiles(i)
j = j + 1
End If
ActiveWorkbook.Close savechanges:=False
Next i
End With
Application.ScreenUpdating = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:Q3GAb.49607$yM6.26347@lakeread06...
I'm trying to edit some existing (similar) code that will
Search all files (all.xls) in a folder and list out on my current sheet
from
A4 downward
all files where the search finds Cell "K4" in each file to be Blank.
Posting my code would be near useless.
I'm been to google and help and am now exhausted.. Can anyone assist?

List out FileNames.xls with K4 Blank
 

Not really sure what the problem is. The code I supplied lists all files, by
name, with cell K4 in worksheet 1 as a blank cell.

The line you highlight is the line that outputs the filename to your home
worksheet. It is assuming that you start with your home worksheet open.

You could try replacing that line with this to be more precise

ThisWorkbook.Worksheets(1).Range("A4").Offset(j, 0).Value =
..FoundFiles(i)

In my test I had 9 files that were empty and 1 that wasn't, and I got a list
of the 9.

The other thin to be aware of is that it is checking for empty, so spaces
are treated as a value.

Try it and post back.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:XNHAb.50065$yM6.13569@lakeread06...
Bob: Thanks for the code -- however purposely blanking out cell K4 in one
of my workbooks does not list that filename beginning in my A4 of my
"home"
Worksheet. My A4 continues blank - should have one file
C:\My Documents\CMyGroup\4126Jan2003.xls, but doesn't.

This line (below) is suspect: Does the code know to write the value of
.FoundFiles(i) to my Originating Workbook and Worksheet (Sheet1) as this
line is within the loop?

Range("A4").Offset(j, 0).Value = .FoundFiles(i)

Any thoughts?
Thanks,
JMay


"Bob Phillips" wrote in message
...
Here's some code, but be aware that the workbook could contain many
worksheets. This code just looks at sheet 1

Sub ShowSomeFiles()
Dim i As Long, j As Long

Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\MyTest"
.Filename = ".xls"
.SearchSubFolders = False
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
If IsEmpty(ActiveWorkbook.Worksheets(1).Range("K4").V alue)
Then
Range("A4").Offset(j, 0).Value = .FoundFiles(i)
j = j + 1
End If
ActiveWorkbook.Close savechanges:=False
Next i
End With
Application.ScreenUpdating = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:Q3GAb.49607$yM6.26347@lakeread06...
I'm trying to edit some existing (similar) code that will
Search all files (all.xls) in a folder and list out on my current
sheet
from
A4 downward
all files where the search finds Cell "K4" in each file to be Blank.
Posting my code would be near useless.
I'm been to google and help and am now exhausted.. Can anyone assist?

List out FileNames.xls with K4 Blank
 

That did the trick Bob;
Works great!!
Tks again,
JMay

"Bob Phillips" wrote in message
...
Not really sure what the problem is. The code I supplied lists all files,
by
name, with cell K4 in worksheet 1 as a blank cell.

The line you highlight is the line that outputs the filename to your home
worksheet. It is assuming that you start with your home worksheet open.

You could try replacing that line with this to be more precise

ThisWorkbook.Worksheets(1).Range("A4").Offset(j, 0).Value
=
.FoundFiles(i)

In my test I had 9 files that were empty and 1 that wasn't, and I got a
list
of the 9.

The other thin to be aware of is that it is checking for empty, so spaces
are treated as a value.

Try it and post back.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:XNHAb.50065$yM6.13569@lakeread06...
Bob: Thanks for the code -- however purposely blanking out cell K4 in
one
of my workbooks does not list that filename beginning in my A4 of my
"home"
Worksheet. My A4 continues blank - should have one file
C:\My Documents\CMyGroup\4126Jan2003.xls, but doesn't.

This line (below) is suspect: Does the code know to write the value of
.FoundFiles(i) to my Originating Workbook and Worksheet (Sheet1) as this
line is within the loop?

Range("A4").Offset(j, 0).Value = .FoundFiles(i)

Any thoughts?
Thanks,
JMay


"Bob Phillips" wrote in message
...
Here's some code, but be aware that the workbook could contain many
worksheets. This code just looks at sheet 1

Sub ShowSomeFiles()
Dim i As Long, j As Long

Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\MyTest"
.Filename = ".xls"
.SearchSubFolders = False
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
If IsEmpty(ActiveWorkbook.Worksheets(1).Range("K4").V alue)
Then
Range("A4").Offset(j, 0).Value = .FoundFiles(i)
j = j + 1
End If
ActiveWorkbook.Close savechanges:=False
Next i
End With
Application.ScreenUpdating = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:Q3GAb.49607$yM6.26347@lakeread06...
I'm trying to edit some existing (similar) code that will
Search all files (all.xls) in a folder and list out on my current
sheet
from
A4 downward
all files where the search finds Cell "K4" in each file to be Blank.
Posting my code would be near useless.
I'm been to google and help and am now exhausted.. Can anyone
assist?