maro loops

J

jsmonje

I have a recurring need to open the same 12 filenames in a large numbe
of directories.
I am trying to write a macro that would:

1. Read the directory structure (I can put this in a worksheet i
needed)
2. Open the first of the 12 files
3. Do some work (this part is done)
4. Loop thru the files names until done
5. Loop thru the directories names until done

Any help would be greatly appreciated
 
D

Dave Peterson

How about this to get you started:

Option Explicit
Sub testme()
Dim FolderNameRng As Range
Dim WkbkNameRng As Range
Dim fldCell As Range
Dim wkbkCell As Range
Dim KeyWks As Worksheet
Dim myFileName As String
Dim testStr As String
Dim wkbk As Workbook

Set KeyWks = Worksheets("sheet1")

With KeyWks
Set FolderNameRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set WkbkNameRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))

If Application.CountA(FolderNameRng) <> FolderNameRng.Cells.Count Then
MsgBox "Please remove blank cells from the folder list"
Exit Sub
End If

If Application.CountA(WkbkNameRng) <> WkbkNameRng.Cells.Count Then
MsgBox "Please remove blank cells from the workbook list"
Exit Sub
End If

For Each fldCell In FolderNameRng.Cells
For Each wkbkCell In WkbkNameRng.Cells
myFileName = fldCell.Value
If Right(myFileName, 1) <> "\" Then
myFileName = myFileName & "\"
End If
myFileName = myFileName & wkbkCell.Value
testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0
If testStr = "" Then
MsgBox myFileName & " doesn't exist!"
Else
Set wkbk = Workbooks.Open(Filename:=myFileName)
'do the real work
Call YourExistingMacro(wkbk)
wkbk.Close savechanges:=True 'false???
End If
Next wkbkCell
Next fldCell
End With

End Sub

sub yourexistingmacro(myWkbk as workbook)
msgbox mywkbk.name
end sub
 
Top