+ Reply to Thread
Results 1 to 2 of 2

Code error, after switching to Excel 2010, from 2003

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-09-2005
    Location
    SC
    Posts
    196

    Code error, after switching to Excel 2010, from 2003

    Hello, It's been a while.

    I've been using this vba code, to search for files, on a media drive. It worked well, when in Excel 2003. I recently upgraded to Excel 2010. Now when I run the code, I get error.

    Highlighted area is after clicking debug: With Application.Filesearch

    Sub SrchForFiles()
         ' Searches the selected folders and sub folders for files with the specified
         'extension.  .xls, .doc, .ppt, etc.
         'A new worksheet is produced called "File Search Results".  You can click on the link and go directly
         'to the file you need.
        Dim i As Long, z As Long, Rw As Long
        Dim ws As Worksheet
        Dim y As Variant
        Dim fLdr As String, Fil As String, FPath As String
         
        y = Application.InputBox("Please Enter File Extension", "Info Request")
        If y = False And Not TypeName(y) = "String" Then Exit Sub
        Application.ScreenUpdating = False
         '**********************************************************************
         'fLdr = BrowseForFolderShell
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Show
            fLdr = .SelectedItems(1)
        End With
         '**********************************************************************
        With Application.FileSearch
            .NewSearch
            .LookIn = fLdr
            .SearchSubFolders = True
            .Filename = y
            Set ws = ThisWorkbook.Worksheets.Add(Sheets(1))
            On Error GoTo 1
    2:                      ws.Name = "FileSearch Results"
            On Error GoTo 0
            If .Execute() > 0 Then
                For i = 1 To .FoundFiles.Count
                    Fil = .FoundFiles(i)
                     'Get file path from file name
                    FPath = Left(Fil, Len(Fil) - Len(Split(Fil, "\")(UBound(Split(Fil, "\")))) - 1)
                    If Left$(Fil, 1) = Left$(fLdr, 1) Then
                        If CBool(Len(Dir(Fil))) Then
                            z = z + 1
                            ws.Cells(z + 1, 1).Resize(, 4) = _
                            Array(Dir(Fil), _
                            FileLen(Fil) / 1000, _
                            FileDateTime(Fil), _
                            FPath)
                            ws.Hyperlinks.Add Anchor:=Cells(z + 1, 1), _
                            Address:=.FoundFiles(i)
                        End If
                    End If
                Next i
            End If
        End With
         
        ActiveWindow.DisplayHeadings = False
         
        With ws
            Rw = .Cells.Rows.Count
            With .[A1:D1]
                .Value = [{"Full Name","Kilobytes","Last Modified", "Path"}]
                .Font.Underline = xlUnderlineStyleSingle
                .EntireColumn.AutoFit
                .HorizontalAlignment = xlCenter
            End With
            .[E1:IV1 ].EntireColumn.Hidden = True
            On Error Resume Next
            Range(Cells(Rw, "A").End(3)(2), Cells(Rw, "A")).EntireRow.Hidden = True
            Range(.[A2 ], Cells(Rw, "C")).Sort [A2 ], xlAscending, Header:=xlNo
        End With
         
        Application.ScreenUpdating = True
        Exit Sub
    1:          Application.DisplayAlerts = False
        Worksheets("FileSearch Results").Delete
        Application.DisplayAlerts = True
        GoTo 2
    End Sub
    ANy help, much appreciated.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,531

    Re: Code error, after switching to Excel 2010, from 2003

    Application.FileSearch has been removed in Excel 2007 and beyond.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1