title: Modify VBS – Excel Macro Loop Apply to all files in folder
body: <p>I have a script that applies a macro to multiple excel spreadsheets. The code below opens specific file names and runs the script. I would love to modify this to run on all xls files within a specified folder. Any help would be great!</p>
  
  <pre><code>Dim objExcel, objWorkbook, xlModule, strCode
  
  If ReportFileStatus("C:\Billing\Import\IL\3.xls") = "True" Then
      OpenFile "C:\Billing\Import\IL\3.xls, ""
  
  If ReportFileStatus("C:\Billing\Import\IL\3.xls") = "True" Then
      OpenFile "C:\Billing\Import\IL\3.xls", ""   
  
  If ReportFileStatus("C:\Billing\Import\IL\3.xls") = "True" Then
      OpenFile "C:\Billing\Import\IL\3.xls", ""   
  
  End If
  
  
  On Error Resume Next
  Set xlModule = Nothing
  Set objWorkbook = Nothing
  objExcel.Quit
  Set objExcel = Nothing
  On Error GoTo 0
  
  '~~&gt; Sub to open the file
  Sub OpenFile(sFile, DestFile)
      Set objExcel = CreateObject("Excel.Application")
  
      objExcel.Visible = false
      objExcel.DisplayAlerts = False
  
      Set objWorkbook = objExcel.Workbooks.Open(sFile)
      Set xlModule = objWorkbook.VBProject.VBComponents.Add(1)
  
      strCode = _
  "Sub MACRO()" &amp; vbCr &amp; _
  
  '~~&gt; My Macro Here
  
  "End Sub"
  
      xlModule.CodeModule.AddFromString strCode
  
  
  
      objExcel.Run "MACRO"
  
      objWorkbook.Close (False) '&lt;~~ Change false to true in case you want to save changes
      objExcel.Application.Quit
  End Sub
  
  '~~&gt; Function to check if file exists
  Function ReportFileStatus(filespec)
      Dim fso, msg
  
      Set fso = CreateObject("Scripting.FileSystemObject")
  
      If (fso.FileExists(filespec)) Then
          msg = "True"
      Else
          msg = "False"
      End If
  
     ReportFileStatus = msg
  End Function
  </code></pre>
  
  <p>Thanks</p>