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().

 

[Guest]

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

 

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

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.

 

[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

 

[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