Results 1 to 2 of 2

Loop Through Subfolders - Sub only copies the first positive result in each folder

Threaded View

natelfo Loop Through Subfolders - Sub... 01-15-2018, 08:46 PM
alansidman Re: Loop Through Subfolders -... 01-15-2018, 09:01 PM
  1. #1
    Registered User
    Join Date
    08-25-2017
    Location
    Savannah, GA
    MS-Off Ver
    2010
    Posts
    9

    Loop Through Subfolders - Sub only copies the first positive result in each folder

    I have a Sub that works fine except for one thing, it only copies the first file that it comes across that matches the criteria (contains "Test" in the name). When I open up the locals window, I see that the CurrFile object does reflect each different file with "Test" in the name, but for some reason, after it copies and pastes the first one in that particular folder, it skips past the other files even though they meet the same criteria. Say for example I have a folder that contains two subfolders in it, and the first subfolder has files titled Test1.csv, Test2.csv, Test3.csv, the second folder contains Test4.csv, Test5.csv, and Test6.csv, the sub will only copy Test1.csv and Test4.csv. I can't seem to figure out why.

    Sub LoopThroughSubfolders()
    
    Dim FSO As Object
    Dim Folder As Object
    Dim Subfolders As Object
    Dim WB As Workbook
    Dim MainWB As Workbook
    Dim CurrFile As Object
    
    Set MainWB = ThisWorkbook
    
    With Application
        '.ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set Folder = FSO.GetFolder(Environ("USERPROFILE") & "\Desktop\Temp" & "\")
        Set Subfolders = Folder.Subfolders
        
        For Each Subfolders In Subfolders
            Set CurrFile = Subfolders.Files
                For Each CurrFile In CurrFile
                    If CurrFile.Name = Dir(Subfolders.Path & "\" & "*Test*.csv") Then
                        Set WB = Workbooks.Open(CurrFile)
                        WB.Activate
                        Range("A1").Select
                        Range("A1").CurrentRegion.Select
                        Selection.Copy
                        MainWB.Activate
                        Worksheets("Sheet1").Select
                        If Range("A2").Value = "" Then
                           Range("A1").Select
                           Else
                           Range("A100000").End(xlUp).Offset(2, 0).Select
                        End If
                        
                        ActiveSheet.Paste
                        WB.Activate
                        Application.CutCopyMode = False
                        WB.Close
                        MainWB.Activate
                    End If
                Next
            Next
    Set FSO = Nothing
    Set Folder = Nothing
    Set Subfolders = Nothing
    Set WB = Nothing
    Set MainWB = Nothing
    Set CurrFile = Nothing
    
    End Sub
    Last edited by alansidman; 01-15-2018 at 09:01 PM. Reason: code tags

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] list of subfolders in folder - without files and sub-subfolders
    By MartyZ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2022, 10:56 AM
  2. Get Value Function Loop through all files in folder and its subfolders
    By Cheeseburger in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-27-2016, 06:19 PM
  3. [SOLVED] Loop through and summarize all subfolders of selected top level folder
    By Webbers in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-04-2015, 10:56 AM
  4. [SOLVED] Extend Existing Code to Loop Through All Subfolders in a Folder
    By XOR LX in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-08-2014, 08:47 AM
  5. Macro to Loop within all subfolders in a folder in three levels
    By sarakhalatbari in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-22-2011, 06:20 AM
  6. Loop Thru Wkbks in folder and subfolders
    By Diddy in forum Excel General
    Replies: 2
    Last Post: 10-19-2011, 02:25 PM
  7. Macro to loop through subfolders within a root folder upto one level only
    By Roop in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-20-2011, 10:01 PM

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