Open multiple excel files in same folder


  • Hello all,
    I am trying to scan a range from multiple excel files in the same folder through vba.

    I can achieve one file at a time using:
      .Workbooks.Open FileName:="filename".

    Another one I've tried but it only opens the first file in the folder: 
      Const MyDir As String = "C:\multipleExcel\"
      .Workbooks.Open (MyDir & "*.xls")

    If this is possible, will I still be able to close all the excel files with:

    Thanks in advance of any insight.

    Tuesday, June 02, 2009 1:19 AM

All replies

  • Hi,

    You can use the DIR command to return files within a folder.

    Sub x()
        Dim strFilename As String
        Dim strPath As String
        Dim wbkTemp As Workbook
        strPath = "C:\multipleExcel\"
        strFilename = Dir(strPath & "*.xls")
        Do While Len(strFilename) > 0
            Set wbkTemp = Workbooks.Open(strPath & strFilename)
            ' do your code with the workbook
            ' save and close it
            wbkTemp.Close True
            strFilename = Dir
    End Sub

    Tuesday, June 02, 2009 8:30 AM
  • Hi Andy,

    Thanks for the code.  It still opens only the first file in the folder.  Does the "*.xls" suppose to take care of all the files in the folder?

    Thanks much!

    Tuesday, June 02, 2009 6:04 PM
  • The code should open, 1 at a time, all the .xls files in the specified directory.

    Wednesday, June 03, 2009 3:58 PM