Customising Application.Filesearch to process textpad kind of files

[Hari]

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

[K Dales]

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
>
>
>

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:%23BD31Nj%[email protected]...
> 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
>
>

[Hari]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >
> >

[Hari]

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
news:uxrpKCl#[email protected]...
> 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" <[email protected]> wrote in message
> news:%23BD31Nj%[email protected]...
> > 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
> >
> >
>
>

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:eUo96Bm%[email protected]...
> 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
> news:uxrpKCl#[email protected]...
> > 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" <[email protected]> wrote in message
> > news:%23BD31Nj%[email protected]...
> > > 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
> > >
> > >
> >
> >
>
>

[Hari]

Hi Bernie,

Thanks a lot for clearing my doubts.

Regards,
Hari
India


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:uJvZ9xm#[email protected]...
> 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" <[email protected]> wrote in message
> news:eUo96Bm%[email protected]...
> > 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
> > news:uxrpKCl#[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:%23BD31Nj%[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>