loop through workbooks
 

"Rhonda" wrote in message
...
I need a macro to loop through a directory
(C:\MyDocuments) and identify files with the .bak
extension. Then open them one at a time because I need to
copy columns to a master. I found this macro, can it be
changed to my needs?


Not really, that routine loops through the worksheets in an open
workbook

You probably need to use the dir function to return a
list of the files in your directory and then use the workbook.Open
method

Something like

Dim myfilelist(1000) As String, myfilecount As Long
Dim myfile as string, xlbook as Workbook

myfile = Dir("C:MyDocuments\*.bak", vbNormal)

Do While myfile < ""
myfilelist(myfilecount) = mydirlist(i) & "\" & myfile
myfilecount = myfilecount + 1
myfile = Dir
Loop

For i = 0 To myfilecount
If myfilelist(i) < "" Then
Set xlbook = xWorkbooks.Open(myfilelist(i))

' Do Your Stuff
xlbook.Close
Set xlbook = Nothing
End If
Next i

Keith

loop through workbooks
 

"Rhonda" wrote in message
...
What should dirlist be?


Sorry I cut this from a more complex example which
looped through directories too and mydirlist was the
folder path, I thought I cut that out but missed that one

Here's the fixed version

Dim myfilelist(1000) As String, myfilecount As Long
Dim myfile as string, xlbook as Workbook

myfile = Dir("C:MyDocuments\*.bak", vbNormal)

Do While myfile < ""
myfilelist(myfilecount) = myfile
myfilecount = myfilecount + 1
myfile = Dir
Loop

For i = 0 To myfilecount
If myfilelist(i) < "" Then
Set xlbook = xWorkbooks.Open(myfilelist(i))

' Do Your Stuff
xlbook.Close
Set xlbook = Nothing
End If
Next i

Keith

loop through workbooks
 

Thanks Keith, appreciate the help!!


-----Original Message-----

"Rhonda" wrote in message
...
What should dirlist be?


Sorry I cut this from a more complex example which
looped through directories too and mydirlist was the
folder path, I thought I cut that out but missed that
one

Here's the fixed version

Dim myfilelist(1000) As String, myfilecount As Long
Dim myfile as string, xlbook as Workbook

myfile = Dir("C:MyDocuments\*.bak", vbNormal)

Do While myfile < ""
myfilelist(myfilecount) = myfile
myfilecount = myfilecount + 1
myfile = Dir
Loop

For i = 0 To myfilecount
If myfilelist(i) < "" Then
Set xlbook = xWorkbooks.Open(myfilelist
(i))

' Do Your Stuff
xlbook.Close
Set xlbook = Nothing
End If
Next i

Keith


.