none
Open multiple excel files in same folder

    Question

  • 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:
      ActiveWorkbook.Close

    Thanks in advance of any insight.

    -VBAnoob
    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
        Loop
        
    End Sub
    
    

    Cheers www.andypope.info
    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!

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

    Cheers www.andypope.info
    Wednesday, June 03, 2009 3:58 PM