Results 1 to 11 of 11

Thread: [RESOLVED] Modify files by looping through all folders and subfolders

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    5

    Resolved [RESOLVED] Modify files by looping through all folders and subfolders

    Hi all,

    This is my first time on the forums, even though I've been using code off here for the past few weeks. I'm working with Excel 2007 and VBA 6.5 and only have about 2 weeks experience with VBA under my belt so bear with me here. I've done C++ and Matlab before, but macros are still new.

    I've been looking for a way to run a macro sub to loop through all files in a chosen folder and all its subfolders. I've found ways to list off files in folders and subfolders, but I can't seem to make them work for my code. I'm basically looking for the simplest code possible that will do this.

    My code below opens a data file and template, transfers the data into the template, then overwrites the data file with the nice data template form. Right now it will only loop through all files in a given folder. Thanks in advance for any help.

    Code:
    Sub Step_Response_FIXED()
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim strDir As String, strFileName As String
    
     'Select a folder dialog popup sets active directory
         With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Please Select a Folder"
            .ButtonName = "Select Folder"
            .InitialFileName = "C:\Documents and Settings\ktech\Desktop\"
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.count > 0 Then
                strDir = .SelectedItems(1) & "\"
            Else
                MsgBox "No folder was chosen." & vbLf & vbLf & "Please try again.", vbExclamation, "User Cancelled."
                Exit Sub
            End If
          End With
        
    strFileName = Dir(strDir & "*GFE DV 3.4.9 Step Response & Overshoot ID_*.xls")
    
    Do While strFileName <> ""
                
        'Define Workbooks
        Dim wbk1        As Workbook
        Dim wbk2        As Workbook
        Set wbk1 = Workbooks.Open("C:\Documents and Settings\ktech\Desktop\NH Step Response Overshoot Template.xls")
        Set wbk2 = Workbooks.Open(strDir & strFileName)
            
            'Transfer data between files
            wbk1.Sheets("DATA").Range("A1:AG29").Value = _
            wbk2.Sheets("DATA").Range("A1:AG29").Value
            wbk1.Sheets("SUMARIZED DATA").Range("A1:AJ2").Value = _
            wbk2.Sheets("SUMARIZED DATA").Range("A1:AJ2").Value
            wbk1.Sheets("CSV DATA").Range("A1:H15000").Value = _
            wbk2.Sheets("CSV DATA").Range("A1:H15000").Value
            wbk1.Sheets("TABLES").Range("A1:C17").Value = _
            wbk2.Sheets("TABLES").Range("A1:C17").Value
            wbk1.Sheets("CHART NAMES").Range("A1:P2").Value = _
            wbk2.Sheets("CHART NAMES").Range("A1:P2").Value
            
            'Closes old data file
            Windows(strFileName).Activate
            ActiveWorkbook.Close False
            
            'Activates template with newly copied data
            Windows("NH Step Response Overshoot Template.xls").Activate
            Sheets("Pass-Fail Summary").Select
            
            'Pauses for user input
            'MsgBox "Press Enter to continue"
            
            'Change directory and save
            ChDir strDir
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs filename:=strFileName, FileFormat:=xlNormal
                
            'Close active windows
            Windows(strFileName).Activate
            ActiveWorkbook.Close False
            
        strFileName = Dir
            
    Loop
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    MsgBox "YOU ARE FINISHED!!!!"
    
    End Sub

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Modify files by looping through all folders and subfolders

    Welcome to the forums

    The simplest way would be to pass the folder name to the sub. This presumes you know what folders are going to be involved. Do you know that information?

    If you do not, then it gets more complicated as you would be required to do a recursive folder search and that would involve the use of several APIs

  3. #3
    Addicted Member
    Join Date
    Jul 2009
    Posts
    208

    Re: Modify files by looping through all folders and subfolders

    Quote Originally Posted by ktech35 View Post
    I've been looking for a way to run a macro sub to loop through all files in a chosen folder and all its subfolders.
    The simplest way is a recursive loop with the FileSystemObject. Whilst the Dir command in your code can be used for this, it requires more supporting code to handle the recursion.

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    5

    Re: Modify files by looping through all folders and subfolders

    Quote Originally Posted by His Nibbs View Post
    The simplest way is a recursive loop with the FileSystemObject. Whilst the Dir command in your code can be used for this, it requires more supporting code to handle the recursion.
    Calling each folder is not an option seeing as there are too many folders with ever changing names. fso is what I've been trying to use, but I don't have enough understanding of it. I tried modifying others code, but with no success. Is there any code specific to my situation that someone could post, using fso? And to help me learn, well commented code even on the "he probably already knows that" parts, would be great.

  5. #5
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Modify files by looping through all folders and subfolders

    the problem with begining with folder traversal is that you are now in the new gui world and the old more static approach to root folder and subfolders is a little bit lost in the murky mists of the gui-ness.

    what most people do is expect the folders at the root of the tree they are travesing to stay where it is and allow you to go back to it and work forwards from it... the problem is that the tree changes shape all the time and getting back is often a nightmare for the newbie...

    the best thing to do is to record the structure of the tree at the point you start you traversal and keep a copy of that point so that you can move back to it and then step 1 directory further always remember ing to keep enough of the structure in memmory somewhere so that you can rebuild the starting points when required.

    i tend to build the structures using the old drive directory anf file boxes because i am old and understand them.

    you an also capture the directory structures into datadase or spread sheet and then itterate your way through them

    just some thoughts

  6. #6
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Modify files by looping through all folders and subfolders

    just looked at you code -

    i do not know the directory structure but id it is a plie of directories from a single root with no odd files floating about and no sub directories you could use that dir reference

    strDir = .SelectedItems(1) & "\"

    note the (1) means its the 1st in a set of items

    the number of items will be itemcount i am sure

    so you loop would wrap around the current loop

    from strDir = .SelectedItems(1) & "\" to strDir = .SelectedItems(itemcount) & "\"

    so that will be a loop variable like

    for i = 1 to .itemcount

    stuff

    next i

    probably

    does that make sense to you?

  7. #7

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    5

    Re: Modify files by looping through all folders and subfolders

    Yeah that makes a lot of sense to me now. I'm gonna try to go with that but its the syntax and lack of intuitiveness that keeps slowing me down. If only they made a brain to code converter, I'd be rich

  8. #8

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    5

    Re: Modify files by looping through all folders and subfolders

    Actually I just thought this up...couldn't I use Application.FileSearch to compile a list of all subfolders in my chosen directory with the folder name I need? Then set my main code to loop through the subfolder list, top to bottom.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Modify files by looping through all folders and subfolders

    couldn't I use Application.FileSearch
    should work as long as you do not upgrade to office 07 or later

    here is an example i posted to work with all subfolders from any given folder, this particular example use a shell object to put all the folder tree into a treeview, but the basic idea is the same, just change what you do with each folder
    http://www.vbforums.com/showthread.php?t=649292
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Modify files by looping through all folders and subfolders

    brainto code converters would still en dup with most people writing code with out recourse to any real thought, its the struggle that makes us better programmers.

    what do they say - oh yes - NO pain NO gain!

    its as true here as in any other sport!

    here to taunt

  11. #11

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    5

    Re: Modify files by looping through all folders and subfolders

    westconn, that is perfect...thanks for all the help!

    And yes programming is just as taxing as most sports I've played, maybe even harder at times

Posting Permissions

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



Click Here to Expand Forum to Full Width