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

I have about 50 or so Excel workbooks that I need to pull data from. I need to take data from specific cells, specific worksheets and compile into one dataset (preferably into another excel workbook). I am looking for some VBA so that I can compile the results into the workbook I am using to run the code. So, one of the xls or xlsx files I need to pull the data from, worksheet("DataSource"), I need to evaluate cell(D4), and if its not null, then pull data from cell(F4), and put into a new row into the compiled data set. Looping through all the Excel files in that folder as mentioned above. And if possible, I would like the first data field in the first column the name of the file the data is being pulled from in the resulting dataset. Can someone help me with this? I am looking for VBA because I am more familiar with that, but also interested in VBScript (as I am trying to get into that and learn the differences).

Sounds like you're most of the way there with the planning. Do you have specific questions on syntax or methods?

yeah. just basically the syntax used to accomplish this overall. How would I loop through all excel files (xls or xlsx) files within a specified folder?

What version of Excel does it need to work in? For example the Application.FileSearch approach won't work in 2007/10

5 Answers 5

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 Long Dim wbResults As Workbook Dim wbCodeBook As Workbook Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On 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 With On Error GoTo 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True End 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 = True objExcel.DisplayAlerts = False Set objFso = CreateObject("Scripting.FileSystemObject") Set objFolder = objFso.GetFolder (strPath) For Each objFile In objFolder.Files If 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 changes End If Next objExcel.Quit