While running an old macro using application.filesearch I am getting some
maco error in excel 2007. The code is

Set fs = Application.FileSearch
With fs
.LookIn = szRepIn
.Filename = rng.Offset(i, 1).Value
.MatchTextExactly = True
If .Execute(SortBy:=msoSortByFileName,
SortOrder:=msoSortOrderAscending) = 0 Then
rng.Offset(i, 2).Value = "File not found"
End If
End With

The error is being raised at line Set fs = Application.FileSearch

Please help me. I am a newbie to excel.

Thanks
Ramesh

filesearch not supported in 2007. Use fso
--
Gary''s Student - gsnu200768


"Ramesh" wrote:

> While running an old macro using application.filesearch I am getting some
> maco error in excel 2007. The code is
>
> Set fs = Application.FileSearch
> With fs
> .LookIn = szRepIn
> .Filename = rng.Offset(i, 1).Value
> .MatchTextExactly = True
> If .Execute(SortBy:=msoSortByFileName,
> SortOrder:=msoSortOrderAscending) = 0 Then
> rng.Offset(i, 2).Value = "File not found"
> End If
> End With
>
> The error is being raised at line Set fs = Application.FileSearch
>
> Please help me. I am a newbie to excel.
>
> Thanks
> Ramesh

I am newbie to excel, could you please send me a sample code using fso

"Gary''s Student" wrote:

> filesearch not supported in 2007. Use fso
> --
> Gary''s Student - gsnu200768
>
>
> "Ramesh" wrote:
>
> > While running an old macro using application.filesearch I am getting some
> > maco error in excel 2007. The code is
> >
> > Set fs = Application.FileSearch
> > With fs
> > .LookIn = szRepIn
> > .Filename = rng.Offset(i, 1).Value
> > .MatchTextExactly = True
> > If .Execute(SortBy:=msoSortByFileName,
> > SortOrder:=msoSortOrderAscending) = 0 Then
> > rng.Offset(i, 2).Value = "File not found"
> > End If
> > End With
> >
> > The error is being raised at line Set fs = Application.FileSearch
> >
> > Please help me. I am a newbie to excel.
> >
> > Thanks
> > Ramesh

http://groups.google.com/group/micro...+exist%22+fso#
--
Gary''s Student - gsnu200768


"Ramesh" wrote:

> I am newbie to excel, could you please send me a sample code using fso
>
> "Gary''s Student" wrote:
>
> > filesearch not supported in 2007. Use fso
> > --
> > Gary''s Student - gsnu200768
> >
> >
> > "Ramesh" wrote:
> >
> > > While running an old macro using application.filesearch I am getting some
> > > maco error in excel 2007. The code is
> > >
> > > Set fs = Application.FileSearch
> > > With fs
> > > .LookIn = szRepIn
> > > .Filename = rng.Offset(i, 1).Value
> > > .MatchTextExactly = True
> > > If .Execute(SortBy:=msoSortByFileName,
> > > SortOrder:=msoSortOrderAscending) = 0 Then
> > > rng.Offset(i, 2).Value = "File not found"
> > > End If
> > > End With
> > >
> > > The error is being raised at line Set fs = Application.FileSearch
> > >
> > > Please help me. I am a newbie to excel.
> > >
> > > Thanks
> > > Ramesh

[PavHabr]

//------------------------------------------------------------------------------------------------

Sub FileSearchByHavrda_Example_of_procedure_calling()
'
' Example of FileSearchByHavrda procedure calling as replacement of missing FileSearch function in the newest MS Office VBA
' 01.06.2009, Author: P. Havrda, Czech Republic
'

Dim FileNameWithPath As Variant
Dim ListOfFilenamesWithParh As New Collection ' create a collection of filenames

' Filling a collection of filenames (search Excel files including subdirectories)
Call FileSearchByHavrda(ListOfFilenamesWithParh, "C:\Temp", "*.xls", True)

' Print list to immediate debug window and as a message window
For Each FileNameWithPath In ListOfFilenamesWithParh ' cycle for list(collection) processing
Debug.Print FileNameWithPath & Chr(13)
MsgBox FileNameWithPath & Chr(13)
Next FileNameWithPath

' Print to immediate debug window and message if no file was found
If ListOfFilenamesWithParh.Count = 0 Then
Debug.Print "No file was found !"
MsgBox "No file was found !"
End If

End Sub

//------------------------------------------------------------------------------------------------

Private Sub FileSearchByHavrda(pFoundFiles As Collection, pPath As String, pMask As String, pIncludeSubdirectories As Boolean)
'
' Search files in Path and create FoundFiles list(collection) of file names(path included) accordant with Mask (search in subdirectories if enabled)
' 01.06.2009, Author: P. Havrda, Czech Republic
'

Dim DirFile As String
Dim CollectionItem As Variant
Dim SubDirCollection As New Collection

' Add backslash at the end of path if not present
pPath = Trim(pPath)
If Right(pPath, 1) <> "\" Then pPath = pPath & "\"

' Searching files accordant with mask
DirFile = Dir(pPath & pMask)
Do While DirFile <> ""
pFoundFiles.Add pPath & DirFile 'add file name to list(collection)
DirFile = Dir ' next file
Loop

' Procedure exiting if searching in subdirectories isn't enabled
If Not pIncludeSubdirectories Then Exit Sub

' Searching for subdirectories in path
DirFile = Dir(pPath & "*", vbDirectory)
Do While DirFile <> ""
' Add subdirectory to local list(collection) of subdirectories in path
If DirFile <> "." And DirFile <> ".." Then If ((GetAttr(pPath & DirFile) And vbDirectory) = 16) Then SubDirCollection.Add pPath & DirFile
DirFile = Dir 'next file
Loop

' Subdirectories list(collection) processing
For Each CollectionItem In SubDirCollection
Call FileSearchByHavrda(pFoundFiles, CStr(CollectionItem), pMask, pIncludeSubdirectories) ' Recursive procedure call
Next

End Sub

//------------------------------------------------------------------------------------------------

[PRASADRPATIL]

Hi PAVHABR

Could you explain how your suggestion for Searching files accordant with mask work?

' [color=blue ! important][color=blue ! important]Searching[/color][/color] files accordant with mask
DirFile = Dir(pPath & pMask)
Do While DirFile <> ""
pFoundFiles.Add pPath & DirFile 'add file name to list(collection)
DirFile = Dir ' next file
Loop

I ask this since in second last line you are setting DirFile = Dir as result the mask is lost and I am retrieving all the files.

Thanks and Regards