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

[ktech35]

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.

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

[Hack]

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

[His Nibbs]

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.

[ktech35]

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.

[incidentals]

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

[incidentals]

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?

[ktech35]

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

[ktech35]

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.

[westconn1]

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

[incidentals]

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

[ktech35]

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