Hari,

It was able to do that because a lot of programmers are lazy. They left out
this, which allowed the old settings to remain:

With Application.FileSearch
.NewSearch
......

So, the ".SearchSubFolders = True stuck around from the previous time your
ran the code.


From help on the NewSEarch method:
Remarks
Search criteria settings are retained throughout an application session. Use
this method every time you change search criteria. This method will not
reset the value of the LookIn property.

HTH,
Bernie
MS Excel MVP

"Hari" wrote in message
...
Hi Bernie,

Thnx a lot for your kind post.
Actually just before K Dales and you posted I came across in excel help an
example for file search and in that they didnt include the statement
".SearchSubFolders = True" so i also did not include it and then ran the
code given below.
Inspite of that the messagebox displayed the correct number of *.lst files
(I mean it included the Lst files in the subfolders as well for
calculating
.FoundFiles.Count ). How was it able to do that?.

Sub MShelp()
Dim i As Integer

With Application.FileSearch
.LookIn = "C:\Documents and Settings\abc\Desktop\Automate\Dev\From
Client\Raw Data"
.Filename = "*.lst"
If .Execute 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Call RenamingLSTasXLS(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

--
Thanks a lot,
Hari
India
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Hari,

Below are your two subs re-written to open .lst files, and to save them
as
.xls files. See the comments in the second sub, where you will need to

make
some changes for saving the file to either the same or another forlder.

HTH,
Bernie
MS Excel MVP

Sub OpenLSTfilesInLocation2()

Application.ScreenUpdating = False
Dim i As Integer
With Application.FileSearch
.NewSearch
.LookIn = _
"C:\Documents and Settings\abc\Desktop\Automate\Dev\From Client\Raw
Data"
.SearchSubFolders = True
.Filename = "*.lst"
.Execute
For i = 1 To .FoundFiles.Count
Call RenamingLSTasXLS2(.FoundFiles(i))
Next i
End With
Application.ScreenUpdating = True

End Sub


Sub RenamingLSTasXLS2(myFName As String)

Workbooks.OpenText Filename:=myFName, _
Origin:=437, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

Application.DisplayAlerts = False
'To save to the same folder, use this:
ActiveWorkbook.SaveAs Filename:=Replace(myFName, ".lst", ".xls"), _
FileFormat:=xlNormal

'To save to another folder, use this:
ActiveWorkbook.SaveAs Filename:= _
Replace("C:\Folder\" & Activeworkbook.Name, ".lst", ".xls"), _
FileFormat:=xlNormal
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub



"Hari" wrote in message
...
Hi,

I have a folder named "C:\Documents and
Settings\abc\Desktop\Automate\Dev\From Client\Raw Data"

Within the Raw Data folder I have subfolders named "Week 1", "Week 2",
"Week
3" etc.

Within a particular week's folder (let's say "Week 1"), I have some
LST
files (a notepad or textpad kind of file). I want to programmatically
open
these text kind of files and save them as XLS files in a different
folder
.

To convert a single file I have the following macro.

Sub RenamingLSTasXLS()

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\abc\Desktop\Automate\Dev\From
Client\Raw
Data\week 1\dev11112.lst", Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\abc\Desktop\Automate\Dev\Working
Files\Renaming\dev11112.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False,
_
CreateBackup:=False

ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

Now, since I automatically want to do the above for all the LST files
within
the Raw Data folder I used the following "Application.filesearch"
method
(somebody in the NG helped me with this feature when I wanted to open
XLS
files programmatically.)

Sub OpenLSTfilesInLocation()

Application.ScreenUpdating = False
Dim i As Integer
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\abc\Desktop\Automate\Dev\From
Client\Raw Data"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
Call RenamingLSTasXLS
Next i
End With
Application.ScreenUpdating = True

End Sub

The problem with the Sub OpenLSTfilesInLocation() is ..

a) I want to open LST files only and not Excel files, so in the above
code
what should I substitute .FileType = msoFileTypeExcelWorkbooks with so
that
I get the count of LST files. I searched help for filetypes but
couldnt
get
any help..

b) How may I pass the name of the LST files as an argument to the Sub
RenamingLSTasXLS().

--
Thanks a lot,
Hari
India