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

by

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

More Related Contents:

  1. How can I call a macro which contains a loop but only run one iteration of that loop?
  2. Run Excel Macro from Outside Excel Using VBScript From Command Line
  3. How to set recurring schedule for xlsm file using Windows Task Scheduler
  4. Export each sheet to a separate csv file
  5. Loop through each row of a range in Excel
  6. Bad data selection using VBScript , on date field values
  7. SumIf using Excel VBA
  8. Test or check if sheet exists
  9. Copy from one workbook and paste into another
  10. How to copy Outlook mail message into excel using VBA or Macros
  11. For Loop not fully cycling in excel VBA [duplicate]
  12. Unique Random Numbers using VBA
  13. Populate unique values into a VBA array from Excel
  14. Excel VBA App stops spontaneously with message “Code execution has been halted”
  15. Closing Excel Application using VBA
  16. How to clear memory to prevent “out of memory error” in excel vba?
  17. What are the benefits of setting objects to “Nothing”
  18. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  19. Uncheck all checkboxes across entire workbook via CommandButton
  20. VBA workbooks.Close without being prompted to if the user wants to save?
  21. Extract Data from Word Document to an Excel SpreadSheet
  22. VBA equivalent to Excel’s mod function
  23. Pointers needed for speeding up nested loop macro in VBA
  24. ‘1004’: “The sort reference is not valid.”
  25. Select entire column in table using Excel VBA
  26. Create hyperlink to another sheet
  27. Reducing WithEvent declarations and subs with VBA and ActiveX
  28. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  29. Access files with long paths (over 260)
  30. Concatenate multiple ranges using vba

Leave a Comment