+ Reply to Thread
Results 1 to 5 of 5

Search for files in folders and subfolders

Hybrid View

  1. #1
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Ladson SC
    MS-Off Ver
    Office365
    Posts
    1,177

    Search for files in folders and subfolders

    I cant seem to figure how to make the code below search thru subfolders, it will search the "strPath" only (original code by Jim Cone).
    Any hints, tips or examples are appreciated.

    'Opens each .xls file in the folder, makes changes and closes the workbook.
    'Protected worksheets throw an error.
    'Jim Cone - Portland, Oregon USA
    'Modified RS 04.15.14, (Thank you Jim Cone)
    
    Sub RemoveFooter()
        Dim objFSO As Object
        Dim objFolder As Object
        Dim objFile As Object
        Dim strPath As String
        Dim strName As String
        Dim WS As Worksheet
    
        Application.ScreenUpdating = False
        'Specify the folder...
        strPath = "C:\Users\rick\Downloads"
    
        'Use Microsoft Scripting runtime.
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFolder = objFSO.GetFolder(strPath)
    
        'Check type of file in the folder and open file.
        For Each objFile In objFolder.Files
            If objFile.Name Like "*.xls" Then
                strName = objFile.Name
                Workbooks.Open objFile
    
                'Open Workbook
                'Clear Footer
                For Each WS In Worksheets
                    WS.PageSetup.LeftFooter = ""
                    WS.PageSetup.CenterFooter = ""
                    WS.PageSetup.RightFooter = ""
                Next WS
                Workbooks(strName).Close savechanges:=True
            End If
        Next    'objFile
    
        'Cleanup
        Application.ScreenUpdating = True
        Set objFSO = Nothing
        Set objFolder = Nothing
        Set objFile = Nothing
    End Sub
    Regards

    Rick
    Win10, Office 365

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Search for files in folders and subfolders

    Hi Rick,
    Look at "Application.FileSearch", it has a handle for SubFolders - ".SearchSubFolders="True".

    Good luck
    - Jewel

  3. #3
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Search for files in folders and subfolders

    Hi Rick,

    I believe Jewel is correct.

    See post #3 in the following thread which probably will help you.
    http://www.mrexcel.com/forum/excel-q...ilesearch.html

    Lewis

  4. #4
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Ladson SC
    MS-Off Ver
    Office365
    Posts
    1,177

    Re: Search for files in folders and subfolders

    LJMetzer, your link was very helpful!
    Thanks!

  5. #5
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Search for files in folders and subfolders

    Please note that when searching for "*.xls", files with extensions ".xlsm", ".xlsx" also apparently show up as matches. It seems to be a legacy feature of Windows that 8.3 extensions are matches and other characters in the extension beyond character 3 don't matter.

    See http://www.networksteve.com/windows/...=16743&Posts=6

    A possible workaround could be the 'like' command:
    Sub TestEightDotThree()
    
      Dim sFileName As String
      Dim sMask As String
      
      sMask = "*.xls"
      
      sFileName = "Abc.xls"
      If sFileName Like sMask Then
        MsgBox "'" & sFileName & "' is a match for the mask '" & sMask & " '."
      Else
        MsgBox "'" & sFileName & "' is NOT a match for the mask '" & sMask & " '."
      End If
      
      sFileName = "Abc.xlsm"
      If sFileName Like sMask Then
        MsgBox "'" & sFileName & "' is a match for the mask '" & sMask & " '."
      Else
        MsgBox "'" & sFileName & "' is NOT a match for the mask '" & sMask & " '."
      End If
      
    End Sub
    Lewis

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA directory search for files stored on mac & in subfolders
    By thebute in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-20-2014, 04:02 AM
  2. Macro Code to Encrypt Multiple Excel Files in Folders and Subfolders
    By ali_1989 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2014, 12:53 PM
  3. Search data in files and folders
    By Remphan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-16-2013, 07:22 AM
  4. [SOLVED] Map/List of folders, subfolders & files
    By Bogdan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-11-2006, 01:10 PM
  5. [SOLVED] copy subfolders, replace text in files and save files in copied subfolders
    By pieros in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-01-2005, 09:05 AM

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