Customising Application.Filesearch to process textpad kind of files
 

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

Customising Application.Filesearch to process textpad kind of file
 

For the first part, forget the FileType property of the FileSearch and use
FileName := "*.LST" instead.

For the second part, add the parameter the the line declaring the procedure,
i.e:
Sub RenamingLSTasXLS(LSTFilePath as String)

Within the sub, remove the "hard coded" file path and use LSTFilePath
instead, i.e:
Workbooks.OpenText Filename:= LSTFilePath Origin:=437, StartRow ...
' rest of code here
ActiveWorkbook.SaveAs Filename:= Replace(LSTFilePath,".lst",".xls"),
FileFormat:=xlNormal, ...

That takes care of the Sub modifications, so now from your Sub
OpenLSTfilesInLocation() call it as:
For i = 1 To .FoundFiles.Count
Call RenamingLSTasXLS(FoundFiles(i))
Next i


"Hari" wrote:

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

Customising Application.Filesearch to process textpad kind of files
 

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

Customising Application.Filesearch to process textpad kind of file
 

Hi K Dales,

Thanx a lot for that neat piece of advice of replacing filetype with
filename. It works as I wanted. Thanx for the argument also.
--
Regards,
Hari
India
"K Dales" wrote in message
...
For the first part, forget the FileType property of the FileSearch and use
FileName := "*.LST" instead.

For the second part, add the parameter the the line declaring the
procedure,
i.e:
Sub RenamingLSTasXLS(LSTFilePath as String)

Within the sub, remove the "hard coded" file path and use LSTFilePath
instead, i.e:
Workbooks.OpenText Filename:= LSTFilePath Origin:=437, StartRow ...
' rest of code here
ActiveWorkbook.SaveAs Filename:= Replace(LSTFilePath,".lst",".xls"),
FileFormat:=xlNormal, ...

That takes care of the Sub modifications, so now from your Sub
OpenLSTfilesInLocation() call it as:
For i = 1 To .FoundFiles.Count
Call RenamingLSTasXLS(FoundFiles(i))
Next i


"Hari" wrote:

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

Customising Application.Filesearch to process textpad kind of files
 

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

Customising Application.Filesearch to process textpad kind of files
 

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

Customising Application.Filesearch to process textpad kind of files
 

Hi Bernie,

Thanks a lot for clearing my doubts.

Regards,
Hari
India


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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