Application.FileSearch in Excel 2007

There is an issue which I am facing after I installed Office 2007. I had written a small VBA which would search for a file and if found will rename the file.

I used Application.FileSearch then. Now I am tying to do it again but unfortunately there is an error prompted stating that 'the object does not exist'

Set fSearch = Application.FileSearch

defPath = "H:\SourceSafe_1_Feb-28_Feb_2006\SecondSet"

Set rg = Range("MyFiles")
fSearch.LookIn = defPath

I have been frantically looking for changes in the VBA object model especially for Excel but cant seem to find them.

Answer this question

Application.FileSearch in Excel 2007

Brian Pound
I am having this same problem as well doing a very similar thing but in Excell. The code is as follows

Set fs = Application.FileSearch

With fs
'retrieve all .doc files in DB directory

.lookin = [Internal Functions].dirName
.fileName = "*.doc"
.Execute
fC = .foundfiles.Count
If fC = 0 Then Exit Sub

etc. etc. Its easy to see what its doing.

If anyone has a workaround for this or knows whether they replaced this function with a different name please post it! I have looked for hours and been unable to figure out a reciprocal command, nor why they would take it out of the language. Thanks so much!

John
br_other
[quote user="DieZeL"]
```
Function ReportFileStatus(filespec)
  Dim fso, msg
  Set fso = CreateObject("Scripting.FileSystemObject")
  If (fso.FileExists(filespec)) Then
   msg = filespec & " exists."
  Else
   msg = filespec & " doesn't exist."
  End If
  ReportFileStatus = msg
End Function

Glax
```
Thats great!

But your function works only to find whether specific file exist or doesn't exist. Is there a way to find whether any files exist in the particular folder. If yes loop through all existing files.

I need to find all existing files and run some other code. Can u plz help to find all those files...
Thankyou.
yanivpinhas

please look this niceFine link;
it's the most I like in MSDN

Scripting Runtime Library

FileSystemObject Sample Code

it's a great sample, a little bit too much ragged and cleved logic with procedures, but: all you need!

or use "CreateObject("Scripting.FileSystemObject")" as searchWord in MicrosoftMSDN

Dwight Kulkarni

Hi

I went back to using VBScript to get the job done

here is a snippet

Function ReportFileStatus(filespec)
  Dim fso, msg
  Set fso = CreateObject("Scripting.FileSystemObject")
  If (fso.FileExists(filespec)) Then
   msg = filespec & " exists."
  Else
   msg = filespec & " doesn't exist."
  End If
  ReportFileStatus = msg
End Function

this should do the job for you... ofcourse with a bit of modification. 
Make sure that you save the file in XLS format rather than the new XLSX format.

Glax

MarcoB

Thought I'd just put a quick reply to this as I was having trouble with this file searching process. I used it in Access and then tried to use it when writing an Outlook macro, with no joy. I think it is specific to Access, because I managed to change my code as follows and it now works. Know this post is old, but if anyone has the same problem as us, could save hours of frustration. (You need to add the 'Microsoft Access 9.0 Object Library' to your references).

Sub processFiles()

Dim app02 As New Access.Application
Dim filename
Dim NumFiles
NumFiles = 0

line1:
With Access.Application.FileSearch
.NewSearch
.LookIn = "C:\Temp\Dataprep\"
.SearchSubFolders = False
.MatchTextExactly = True
.filename = "*.*"
.Execute

End With

If FileSearch.FoundFiles.Count > 0 Then
filename = FileSearch.FoundFiles.Item(1)

'Put your code that processes files here

Name filename As Left(filename, 17) & "processed\" & Mid(filename, 18, 60)
NumFiles = NumFiles + 1
GoTo line1
Else

End If
MsgBox NumFiles & " File(s) Processed!", vbOKOnly, "Finished!"
End Sub

All this code does is loop through the dataprep folder and place all the files it finds in a 'processed' sub folder.

This is placed in an excel module and seems to do the trick. I know the code's probably badly written but hope it will be of use to someone.
ICW
9 months later, is there any practical information available now
LiL_X
There has been little in the way of KB articles. Have you looked at the online help That's what I was trying to use in January to explore the object model, but it was not yet mature enough, and using the object browser alone was also not very useful.
```
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
```
Melvinsb

sorry, Dear Danny,

".FileSearch."

it's no more time standard in 2007, SEVEN!
raatey

Hi,

I only have 2003 so this is just advice rather than an absolute answer. In the VBA Editor use the Object Browser and do a search (or browse) for the Application object. See if the FileSearch object is there.

If that doesn't help can you find a feature on the GUI to do the File Search. If you can record a macro and that will give you an idea of what legacy code to use.

I hope that helps.
vb2005
I don't know about Access. I do know .FileSearch is gone from Excel 2007. I also know that there is little practical information available yet on the changes to VBA or the Office 2007 Object Models.
```
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
```

Tryin2Bgood

Thanks! Brilliant!

DieZeL wrote:

Hi

I went back to using VBScript to get the job done

here is a snippet

Function ReportFileStatus(filespec)
  Dim fso, msg
  Set fso = CreateObject("Scripting.FileSystemObject")
  If (fso.FileExists(filespec)) Then
   msg = filespec & " exists."
  Else
   msg = filespec & " doesn't exist."
  End If
  ReportFileStatus = msg
End Function

this should do the job for you... ofcourse with a bit of modification. 
Make sure that you save the file in XLS format rather than the new XLSX format.

Glax

041661K

Application.FileSearch has been deprecated from Excel 2007.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

AlexU
Have a quick look at my response to the original question. Hope that helps mate.

KenJoh

But if you look in help you can see in searching SearchScope this:

Sub DisplayAvailableScopes()

  'Declare a variable that references a
  'SearchScope object.
  Dim ss As SearchScope

  'Use a With...End With block to reference the
  'FileSearch object.
  With Application.FileSearch

    'Loop through the SearchScopes collection.
    For Each ss In .SearchScopes
      Select Case ss.Type
        Case msoSearchInMyComputer
          MsgBox "My Computer is an available search scope."
        Case msoSearchInMyNetworkPlaces
          MsgBox "My Network Places is an available search scope."
        Case msoSearchInOutlook
          MsgBox "Outlook is an available search scope."
        Case msoSearchInCustom
          MsgBox "A custom search scope is available."
        Case Else
          MsgBox "Can't determine search scope."
      End Select
    Next ss
  End With
End Sub

Filesearch exist or not in Access 2007, what do you think

Application.FileSearch in Excel 2007

Answer this question