PC Review


Reply
Thread Tools Rate Thread

application.filesearch error in excel 2007

 
 
Ramesh
Guest
Posts: n/a
 
      11th Feb 2008
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
 
Reply With Quote
 
 
 
 
Gary''s Student
Guest
Posts: n/a
 
      11th Feb 2008
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

 
Reply With Quote
 
Ramesh
Guest
Posts: n/a
 
      11th Feb 2008
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

 
Reply With Quote
 
Gary''s Student
Guest
Posts: n/a
 
      11th Feb 2008
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

 
Reply With Quote
 
New Member
Join Date: Jun 2009
Posts: 2
 
      24th Jun 2009
//------------------------------------------------------------------------------------------------

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

//------------------------------------------------------------------------------------------------
 
Reply With Quote
 
New Member
Join Date: Jul 2009
Posts: 1
 
      21st Jul 2009
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Solution for Application.FileSearch Excel 2007-2010 Michel Demers Microsoft Excel Programming 2 23rd Nov 2010 12:28 AM
missing Application.FileSearch excel 2007 John Microsoft Excel Programming 3 14th May 2010 02:42 AM
Application.FileSearch in Excel 2007! Mark Microsoft Excel Discussion 3 24th Jun 2009 04:56 PM
Excel 2007 / application.filesearch no longer works Pete Ross Microsoft Excel Programming 1 15th Sep 2008 06:25 PM
Filesearch error in Excel 2007 Libby Microsoft Excel Programming 4 8th Feb 2008 08:51 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:32 PM.