Apply an Excel VBA function to many files

Question

I have an Excel VBA function and around 30 files with data (and the same data structure) to which I would like to apply the function to.

My approach would be to write a macro in a separate Excel file which opens the Excel files and performs the necessary modifications on each of the 30 files.

Answer 1

Writing the macro in a seperate file seems to be the best way. Found relevant code here regarding how to loop over my Excel files:

Sub ProcessFiles()
Dim Filename, Pathname As String
Dim wb As Workbook

Pathname = ActiveWorkbook.Path & "\Files\"
Filename = Dir(Pathname & "*.xls")
Do While Filename <> ""
    Set wb = Workbooks.Open(Pathname & Filename)
    DoWork wb
    wb.Close SaveChanges:=True
    Filename = Dir()
Loop
End Sub

Sub DoWork(wb As Workbook)
With wb
    'Do your work here
    .Worksheets(1).Range("A1").Value = "Hello World!"
End With
End Sub

"In this example DoWork() is your macro that you apply to all of your files. Make sure that you do all your processing in your macro is always in the context of the wb (currently opened workbook)."

Found additional code on how to insert the relevant VBA code into each of my 30 files here:

Public Sub AddNewModule()

Dim proj As VBIDE.VBProject
Dim comp As VBIDE.VBComponent

Set proj = ActiveWorkbook.VBProject
Set comp = proj.VBComponents.Add(vbext_ct_StdModule)
comp.Name = "MyNewModule"

Set codeMod = comp.CodeModule

  With codeMod
  lineNum = .CountOfLines + 1
  .InsertLines lineNum, "Public Sub ANewSub()"
  lineNum = lineNum + 1
  .InsertLines lineNum, "  MsgBox " & """" & "I added a module!" & """"
  lineNum = lineNum + 1
  .InsertLines lineNum, "End Sub"
  End With

End Sub