Macro to open all files in one particular directory

[Andre Croteau]

Hi,

I would like to be able to have a macro that would automatically open ALL
the files in one particular directory.

Say I have various directories, each with 10 files of different names: (the
number of files in each directory can vary)

C:\temp\200410\
C:\temp\200411\
C:\temp\200412\

In my file named OPENFILES.xls, in cell A1 of sheet1, I would like to input
the directory name (say 200411), and then run a macro that would open each
file of that particular directory, regardless of their name. I tried to
record a macro, but it listed the individual files to open,. The problem is
that the name and number of files in that directory might vary from time to
time.

Can this be done?

Thanks in advance for your help.

André

 

[Jim May]

Try: (in a std module).

Sub OpenMyFiles()
Dim sPath As String, sName As String
Dim bk As Workbook
sPath = "C:\My Documents\FolderName?\" '<<Supply as needed
sName = Dir(sPath & "*.xls")
Do While sName <> ""
Set bk = Workbooks.Open(sPath & sName)
sName = Dir
Loop
End Sub

HTH

 

[Andre Croteau]

Hello Jim,

This is absolutely perfect! Thank you

Aren't newsgroups the best thing since sliced bread???

André