Code for looping through all excel files in a specified folder, and pulling data from specific cells

First start with this google query and click the first link that comes up, which takes you to an article showing how to iterate through a group of Excel files in a folder.

Sub RunCodeOnAllXLSFiles()Dim lCount As LongDim wbResults As WorkbookDim wbCodeBook As WorkbookApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseApplication.EnableEvents = FalseOn Error Resume Next    Set wbCodeBook = ThisWorkbook        With Application.FileSearch            .NewSearch            'Change path to suit            .LookIn = "C:\MyDocuments\TestResults"            .FileType = msoFileTypeExcelWorkbooks            'Optional filter with wildcard            '.Filename = "Book*.xls"                If .Execute > 0 Then 'Workbooks in folder                    For lCount = 1 To .FoundFiles.Count 'Loop through all                        'Open Workbook x and Set a Workbook variable to it                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)                        'DO YOUR CODE HERE                        wbResults.Close SaveChanges:=False                    Next lCount                End If        End WithOn Error GoTo 0Application.ScreenUpdating = TrueApplication.DisplayAlerts = TrueApplication.EnableEvents = TrueEnd Sub

To get the name of the workbook, you'll want to adapt the code at "DO YOUR CODE HERE" to include wbResults.Name. If it's the filename you want, use wbResults.FullName, which returns the name of the workbook including its path on disk as a string.

A search for a VBScript variation on the same thing yields a number of results that are useful, including this script:

strPath = "C:\PATH_TO_YOUR_FOLDER"Set objExcel = CreateObject("Excel.Application")objExcel.Visible = TrueobjExcel.DisplayAlerts = FalseSet objFso = CreateObject("Scripting.FileSystemObject")Set objFolder = objFso.GetFolder (strPath)For Each objFile In objFolder.FilesIf objFso.GetExtensionName (objFile.Path) = "xls" Then   Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)   ' Include your code to work with the Excel object here   objWorkbook.Close True 'Save changesEnd IfNextobjExcel.Quit

I would do it in VBScript or even, VB.NET or Powershell if you feel so inclined.

Using VB.NET, you can access Excel spreadsheets as if they were databases, via the OLEDB provider. The code to select a range of values might look like this :

 Try        Dim MyConnection As System.Data.OleDb.OleDbConnection        Dim DtSet As System.Data.DataSet        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter        MyConnection = New System.Data.OleDb.OleDbConnection _        ("provider=Microsoft.Jet.OLEDB.4.0;"  _        " Data Source='testfile.xls'; " _         "Extended Properties=Excel 8.0;")        MyCommand = New System.Data.OleDb.OleDbDataAdapter _            ("select * from [Sheet1$]", MyConnection)        MyCommand.TableMappings.Add("Table", "TestTable")        DtSet = New System.Data.DataSet        MyCommand.Fill(DtSet)        MyConnection.Close()    Catch ex As Exception        MsgBox(ex.ToString)    End Try

Once you get the data you can elaborate on it, then insert the result into another Excel spreadsheet, using the same API.

Getting the list of files is easy in .NET with a call to System.IO.Directory.GetFiles(); just specify the "*.xls" wildcard. Once you have the list, just use a for loop to iterate through it, opening each file in turn, then doing the query on that file, and so on.

If you use VBScript, then the preferred way to get the list of Excel files is to use the Scripting.FileSystemObject, specifically the GetFolder method. It works basically the same way but the syntax is slightly different.

If it's VBScript or VB.NET it will probably run outside of Excel itself. You'd run it by double-clicking or from a batch file or something like that. The advantage to using VB.NET is you could put up a graphical form for interaction - it could show a progress bar, tracking how many files you've gone through, status updates, that kind of thing.

Whenever you are accessing that many Excel files in succession, you can generally get better performance using ADODB rather than Excel's automation object.