Application.FileSearch in Excel 2007

DieZeL

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.

Derek Smyth

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.

johnAR

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

DieZeL

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

DannyK2316

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.

DannyK2316

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

MrLukas

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

Ilyas Kazi

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

You must enter a user name before continuing.

sorry, Dear Danny,

".FileSearch."

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

You must enter a user name before continuing.

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

Jon Peltier

Application.FileSearch has been deprecated from Excel 2007.

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

Enkore

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

Jon Peltier

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
_______

SixSigmaGuy

9 months later, is there any practical information available now

Jon Peltier

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
_______

Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007

Re: Application.FileSearch in Excel 2007