PDA

View Full Version : I want my macro to do the same thing in 25 folders...



robin
04-30-2007, 01:48 PM
I have a macro that searches a folder for a text file, opens this text file and gets some data from it, pastes the data into a worksheet, and then closes the text file. The macro works great.... but I have 24 other folders that I would like to do the same thing for. All 25 of these folders are in the same directory (named "cd01", "cd02", "cd03" up to "cd25").

Can anyone help me? Thanks...

shasur
04-30-2007, 07:13 PM
Robin

This code will give you the sub folders. Specify the path in sPath and insert your current macro sub/function .

Sub Exec_Macro_For_Dir()

Dim sPath As String
Dim sFile As String
Dim sDir As String
Dim sDirLocationForText As String
Dim i1 As Long
Dim iMax As Long

On Error GoTo Err_Clk

sPath = "D:\Temp" ' Your Path
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"

sDir = Dir$(sPath & "cd*", vbDirectory)
Do Until LenB(sDir) = 0
' This will be the location for the sub directory
sDirLocationForText = sPath & sDir
' Exec_MyMacro() ' Your MAcro here
sDir = Dir$
Loop

Err_Clk:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub



I hope your existing macro will be a function accepting path as an argument. If it doesn't you can tailor this code for ur requirements