application.filesearch code help 2007

[methzzz]

I have a problem with running this excel 2003 vba code in new 2007 excel versions. The problem is that application.FileSearch does not work in the new versions.

After much time spent searching the Internet I cannot find a solution or work out how to adapt my code so I have had to give in and seek help from others!

The function of this code is to search through all folders and sub-folders in a specified directory. The filename is checked and if it matches a variable “Part Number” it is opened. Once opened it is renamed Doner.

This process continues searching through the folders and sub-folders checking filename by variable until all variables have been checked.

Sub Import()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim DOS As Variant
Dim Delimiter As String
Dim rngCel As Range
Dim rFound As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = “\\PATH\Path1\Path\”
        .SearchSubFolders = True
        .FileType = msoFileTypeExcelWorkbooks
        .Filename = "*" & Part_Number & "*.xls"
        
            If .Execute > 0 Then 'Workbooks in folder
                For lCount = 1 To .FoundFiles.count 'Loop through all.
                 'Open Workbook x and Set a Workbook variable to it
                 DOS = GetFilenameFromPath(.FoundFiles(lCount))
                 Delimiter = "^"
                    PartNumber = GetElement2(DOS, 2, Delimiter)
                 Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                           Doner = ActiveWorkbook.Name

Hope somebody can help, been trying to sort this myself but just don’t know how. Any questions just ask, cheers.

 

[hiker95]

methzzz,

Welcome to the MrExcel forum.


Application.FileSearch is not available in Excel 2007.

See:

By RichardSchollar
http://www.ozgrid.com/forum/showpost...73&postcount=4

By Peter_SSs
http://www.mrexcel.com/forum/showthr...ht=fList()

Recursively Search Directories by Using FileSystemObject
http://support.microsoft.com/kb/185601/

 

[methzzz]

Thanks, the first 2 links are showing 'Page cannot be found' tho

 

[methzzz]

delete

 

[methzzz]

Sorry for double-reply, cant see any edit button! I have tried to manipulate the Microsoft support code but it seems that this is not the same function that I am trying to do. *Forgive me I am new to vb*

Option Explicit
 
Dim fso As New FileSystemObject
Dim fld As Folder
 
Private Sub Command1_Click()
   Dim nDirs As Long, nFiles As Long, lSize As Currency
   Dim sDir As String, sSrchString As String
   sDir = InputBox("Type the directory that you want to search for", _
                   "FileSystemObjects example", "C:\")
   sSrchString = InputBox("Type the file name that you want to search for", _
                   "FileSystemObjects example", "vb.ini")
  MousePointer = vbHourglass
  Label1.Caption = "Searching " & vbCrLf & UCase(sDir) & "..."
   lSize = FindFile(sDir, sSrchString, nDirs, nFiles)
  MousePointer = vbDefault
  MsgBox Str(nFiles) & " files found in" & Str(nDirs) & _
         " directories", vbInformation
  MsgBox "Total Size = " & lSize & " bytes"
End Sub
 
Private Function FindFile(ByVal sFol As String, sFile As String, _
   nDirs As Long, nFiles As Long) As Currency
   Dim tFld As Folder, tFil As File, FileName As String
 
   On Error GoTo Catch
   Set fld = fso.GetFolder(sFol)
   FileName = Dir(fso.BuildPath(fld.Path, sFile), vbNormal Or _
                  vbHidden Or vbSystem Or vbReadOnly)
   While Len(FileName) <> 0
      FindFile = FindFile + FileLen(fso.BuildPath(fld.Path, _
      FileName))
      nFiles = nFiles + 1
      List1.AddItem fso.BuildPath(fld.Path, FileName)  ' Load ListBox
      FileName = Dir()  ' Get next file
      DoEvents
   Wend
   Label1 = "Searching " & vbCrLf & fld.Path & "..."
   nDirs = nDirs + 1
   If fld.SubFolders.Count > 0 Then
      For Each tFld In fld.SubFolders
         DoEvents
         FindFile = FindFile + FindFile(tFld.Path, sFile, nDirs, nFiles)
      Next
   End If
   Exit Function
Catch:  FileName = ""
       Resume Next
End Function

The firrst part of the code seems to suggest that it is opening up a dialog box, asking for user inputs and displaying some values. This is not what I require. I wish to have code which searches through folders and subfolders searching for xls files against a filename(a variable). Not sure how to manipulate this code, as when I tried it resulted in me just deleting most of it

 

[Kenneth Hobson]

I responded to the cross-post with these class examples that I know about. I just wanted to include that information here should others find it helpful. The cross-post is at: http://www.excelforum.com/excel-programming/789499-application-filesearch-code-help-2007-a.html


>>>
I could list some FSO methods but here are some class methods which offer similar features. The last one seems to be a close fit.

'ginismo, http://www.mrexcel.com/forum/showthread.php?t=369982 'Class method
'http://www.mrexcel.com/forum/showthread.php?p=1839452
'http://www.4shared.com/file/87591234/8d1d705d/1839452_classFileSearch_and_Excel4.html
'http://www.mrexcel.com/forum/showthread.php?p=2551004 'alternate class method
'http://dl.dropbox.com/u/35239054/FileSearch.cls 'alternate class method by Andreas Killer, version 1.43