Application.FileSearch in Excel 2007  
Author Message
DieZeL





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

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.



Microsoft ISV Community Center Forums2  
 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

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





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

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





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

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





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

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





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

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

 
 
MrLukas





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

Thanks! Brilliant!

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





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

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





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

sorry, Dear Danny,

".FileSearch."

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

 

 



 
  You must enter a user name before continuing.





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

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





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

Application.FileSearch has been deprecated from Excel 2007.

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


 
  Enkore





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

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





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

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





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

9 months later, is there any practical information available now
 
  Jon Peltier





PostPosted: Visual Basic for Applications (VBA), Application.FileSearch in Excel 2007 Top

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
_______