ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help in Modification of existing code (https://www.excelbanter.com/excel-programming/292919-help-modification-existing-code.html)

JMay
Help in Modification of existing code
 
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

Bob Phillips[_6_]
Help in Modification of existing code
 
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)

"JMay" wrote in message
news:I%20c.14183$TT5.10703@lakeread06...
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

JMay
Help in Modification of existing code
 
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,,,


"Bob Phillips" wrote in message
...
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)

"JMay" wrote in message
news:I%20c.14183$TT5.10703@lakeread06...
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

Bob Phillips[_6_]
Help in Modification of existing 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 )
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 )
Subject: 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)

"JMay" wrote in message
news:TH30c.14233$TT5.12468@lakeread06...
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,,,


"Bob Phillips" wrote in message
...
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)

"JMay" wrote in message
news:I%20c.14183$TT5.10703@lakeread06...
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

Dave Peterson[_3_]
Help in Modification of existing code
 
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.


JMay wrote:

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

--

Dave Peterson

Bob Phillips[_6_]
Help in Modification of existing code
 
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)

"Dave Peterson" wrote in message
...
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.

JMay
Help in Modification of existing code
 
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

"Bob Phillips" wrote in message
...
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 )
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 )
Subject: 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)

"JMay" wrote in message
news:TH30c.14233$TT5.12468@lakeread06...
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,,,


"Bob Phillips" wrote in message
...
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)

"JMay" wrote in message
news:I%20c.14183$TT5.10703@lakeread06...
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

JMay
Help in Modification of existing code
 
Dave, Yes!, I am using XL2002!!

"Dave Peterson" wrote in message
...
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.


JMay wrote:

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

--

Dave Peterson

Bob Phillips[_6_]
Help in Modification of existing code
 
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)

"JMay" wrote in message
news:Ou50c.14814$TT5.2147@lakeread06...
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

"Bob Phillips" wrote in message
...
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 )
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 )
Subject: 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)

"JMay" wrote in message
news:TH30c.14233$TT5.12468@lakeread06...
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,,,


"Bob Phillips" wrote in message
...
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)

"JMay" wrote in message
news:I%20c.14183$TT5.10703@lakeread06...
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

Dave Peterson[_3_]
Help in Modification of existing code
 
If you search google for:

filesearch flakey OR flaky
with *excel*

You'll find about 20 hits.

http://google.com/groups?as_q=filese... roup=*excel*
(one line in your browser)

They might not help you at all, but it'll prove that you're not alone!



JMay wrote:

Dave, Yes!, I am using XL2002!!

"Dave Peterson" wrote in message
...
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.


JMay wrote:

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

--

Dave Peterson


--

Dave Peterson

JMay
Help in Modification of existing code
 
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" wrote in message
...
If you search google for:

filesearch flakey OR flaky
with *excel*

You'll find about 20 hits.


http://google.com/groups?as_q=filese...s_ug roup=*ex
cel*
(one line in your browser)

They might not help you at all, but it'll prove that you're not alone!



JMay wrote:

Dave, Yes!, I am using XL2002!!

"Dave Peterson" wrote in message
...
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.


JMay wrote:

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

--

Dave Peterson


--

Dave Peterson

Bob Phillips[_6_]
Help in Modification of existing code
 
Good choice, that's what I use<G

Bob

"JMay" wrote in message
news:yz60c.15089$TT5.14163@lakeread06...
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" wrote in message
...
If you search google for:

filesearch flakey OR flaky
with *excel*

You'll find about 20 hits.



http://google.com/groups?as_q=filese...s_ug roup=*ex
cel*
(one line in your browser)

They might not help you at all, but it'll prove that you're not alone!



JMay wrote:

Dave, Yes!, I am using XL2002!!

"Dave Peterson" wrote in message
...
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.


JMay wrote:

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

--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 08:43 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com