Opening excel files from EXcel-VBA macro

  • Thread starter Thread starter anandmr
  • Start date Start date
A

anandmr

Hi,

I want to open all the excel files in a folder one by one and get dat
from each file for some calculations. In VB i was able to accomlish th
same since I was using the dir list box, fil list box etc. In exce
Macro can I open all the excel files one by one in a folder by givin
the folder location.

Please help with an example if possible

Thanks
Anan
 
Anand,

Sub All_Speadsheets()
Dim fs, f, f1
fldr = "C:\Documents\Spreadsheets\" 'change to what you want
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder(fldr)
Set f1 = f.files
For Each f2 In f1
filenam = fldr & f2.Name
If Right(filenam, 4) = ".xls" Then
Workbooks.Open filenam
'code to do what you want
ActiveWorkbook.Close
End If
Next
End Sub

HTH,
Nikos
 
Back
Top