Help in Modification of existing code

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

The following code to perform quite well with the exception that it is
Occasionally **Duplicating** certain (several) files in the listing, as
follows:
c:\windows\desktop\temp excel formulas\CountIf_Array_Usage.xls
c:\windows\desktop\Temp excel formulas\CountIf_Array_Usage.xls
c:\windows\desktop\temp excel formulas\CountIf_Array_Usage.xls

Any clues as to what's going on and how to eliminate duplication?
TIA,
JMay


Sub listthefiles()
Set fs = Application.FileSearch
With fs
..LookIn = "C:\Windows\Desktop\Temp Excel Formulas"
..Filename = "*.xls"
If .Execute > 0 Then
ActiveCell = Range("A2")
For I = 1 To .FoundFiles.Count
ActiveCell.FormulaR1C1 = .FoundFiles(I)
ActiveCell.Offset(1, 0).Select
Next I
Else
End If
End With
End Sub
 
Although there is one oddity it the code (ActiveCell = Range("A2")), and the
loop can be done without selecting

If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Range("A1").Offset(i, 0).Value = .FoundFiles(i)
Next i
End If

I can't see any problem, that would cause it to behave as you describe.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob, Thanks for the modification, I've employed it but still am getting the
same results.
A few weeks back I recall someone( actually Tom O) employing the use of
Ucase(), LCase(), if true... ?#$%^.... Could this "add" be a correcting
factor? << I don't know how to add to my code,,,
 
I checked this post, and it seems to apply to checking whether a file
exists, so Tom UCase's it to be sure.

There was a post in 2002 which I reprint, but I admit I don't see a solution
in Tom's reply. Could it be that you are seeing deleted versions of the
file?

This is your code amended as per Tom's but nothing significant that I can
see.

Dim i
With Application.FileSearch
.LookIn = "C:\Windows\Desktop\Temp Excel Formulas"
.Filename = "*.xls"
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Range("A1").Offset(i, 0).Value = .FoundFiles(i)
Next i
End If
End With
End Sub


Here's that post


Message 1 in thread
From: Mike Ames ([email protected])
Subject: Filesearch-Problem


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2002-01-16 07:36:48 PST


Hi, I have a problem with the filesearch function in Excel 2000. Even the
example shipped with the excelhelp will display double entries or already
deleted files.

Example from the Helpfile:
Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = "*.doc"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

my example:
Sub test()With Application.FileSearch.NewSearch.LookIn = "c:\documents and
settings\".SearchSubFolders = True.Filename = "TSData.xls"
If .Execute() > 0 Then For i = 1 To .FoundFiles.Count
Worksheets("sheet1").Cells(i) = .FoundFiles(i) Next i Else
MsgBox "0 Files found" End If MsgBox i.Execute
End With
End Sub
In addition not only TSDATA.XLS Files are shown, but also something linek
TSDATATest123.xls
I am using Office 2000 with Win2000

regards
MichaelMessage 2 in thread
From: Tom Ogilvy ([email protected])
Subject: Re: Filesearch-Problem


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2002-01-16 08:03:00 PST


Sub test()
With Application.FileSearch
..NewSearch
..LookIn = "c:\documents and settings\"
..SearchSubFolders = True
..Filename = "TSData.xls"
If .Execute() > 0 Then
j = 1
For i = 1 To .FoundFiles.Count
if Right(Ucase(.foundfiles),10) = "TSDATA.XLS" then
Worksheets("sheet1").Cells(j) = .FoundFiles(i)
j = j + 1
End if
Next i
Else
MsgBox "0 Files found" End If MsgBox i.Execute
End With
End Sub


Regards,
Tom Ogilvy


--

HTH

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

I've seen a few posts that complain that application.filesearch is flaky in that
version (not sure if it was fixed in xl2003???).

The "flakiness" that I've experienced with win98 and xl2002 is filesearch
missing files. I don't recall it duplicating found files, but that could just
mean that I haven't experienced that part of the flakiness.

Maybe reverting to dir()'s or File system object would yield better results.
 
I have also missed files with FileSearch, and stopped using it in favour of
FSO a long time ago.

As I showed, there has been a previous post on duplicated files before, but
I couldn't see a full resolution.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob, not sure if I'm picking up "deleted" versions of the file << how would
I know?
When in Explorer I go to the folder "C:\Windows\Desktop\Temp Excel Formulas"
and Highlight all the *.xls files it returns "456 files". If in the VBA
code window I run
the code in STEP fashion (using F8) after passing the line ...
foundfiles.count
it's value (by viewing on screen - by hovering over the text "foundfiles")
it returns "757";
so already in my code the problem exists. A bit strange to me (who don't
know much).
Tks,
JMay
 
Dave, Yes!, I am using XL2002!!

Dave Peterson said:
Are you using xl2002?

I've seen a few posts that complain that application.filesearch is flaky in that
version (not sure if it was fixed in xl2003???).

The "flakiness" that I've experienced with win98 and xl2002 is filesearch
missing files. I don't recall it duplicating found files, but that could just
mean that I haven't experienced that part of the flakiness.

Maybe reverting to dir()'s or File system object would yield better results.
Click to expand...
 
I am afraid that was not an educated guess, just a stab in the dark,
probably not worth considering any further.

I would suggest you switch to FSO or Dir as Dave suggests.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks Dave and Bob for your input.
I ended up going the FSO route. It works like a charm;
much appreciation -- to each of you.
 
Good choice, that's what I use<G>

Bob

JMay said:
Thanks Dave and Bob for your input.
I ended up going the FSO route. It works like a charm;
much appreciation -- to each of you.

Dave Peterson said:
If you search google for:

filesearch flakey OR flaky
with *excel*

You'll find about 20 hits.
Click to expand...
http://google.com/groups?as_q=filesearch flakey OR flaky&as_ugroup=*ex
cel*
(one line in your browser)

They might not help you at all, but it'll prove that you're not alone!
Click to expand...
it
is
Click to expand...
listing,
Click to expand...
 
Back
Top