Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect validation list with blank in list | Excel Discussion (Misc queries) | |||
can not scroll on Edit links very long list of filenames | Excel Discussion (Misc queries) | |||
drop down list shows first blank row after last list entry | Excel Discussion (Misc queries) | |||
Eliminate Blank Rown in a List - Then Create New List | Excel Discussion (Misc queries) | |||
List all filenames & tab names | Excel Worksheet Functions |