-
Feb 15th, 2012, 08:52 AM
#1
Thread Starter
New Member
[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
-
Feb 15th, 2012, 08:56 AM
#2
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
-
Feb 15th, 2012, 09:50 AM
#3
Addicted Member
Re: Modify files by looping through all folders and subfolders
Originally Posted by ktech35
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.
-
Feb 15th, 2012, 10:23 AM
#4
Thread Starter
New Member
Re: Modify files by looping through all folders and subfolders
Originally Posted by His Nibbs
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.
-
Feb 15th, 2012, 05:49 PM
#5
Frenzied Member
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
-
Feb 15th, 2012, 05:59 PM
#6
Frenzied Member
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?
-
Feb 16th, 2012, 09:05 AM
#7
Thread Starter
New Member
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
-
Feb 16th, 2012, 09:23 AM
#8
Thread Starter
New Member
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.
-
Feb 16th, 2012, 03:40 PM
#9
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
-
Feb 16th, 2012, 06:02 PM
#10
Frenzied Member
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
-
Feb 17th, 2012, 08:29 AM
#11
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|