In solving a problem where a bunch of workbooks needed to be updated using data from the same database, I found a way to automate the process using Excel VBA. I chose to design a "controller" worksheet that would have the list of Workbook files to change, and had access to the data to update in each of the workbooks.

I first created a function to read a list of filenames that were in a defined range of cells in an Excel worksheet.

Function GetFilenames() As Variant
    Dim WSD As Worksheet
    Set WSD = Worksheets("Parameters")

    ' The filenames are all in column A on the Parameters worksheet
    ' Find the last row with data
    Dim finalRow As Long
    finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    
    Dim Result() As String
    
    ' return a variant from the range of values
    Dim j As Long
    Dim i As Integer
    i = 1
    ' starting at row 2, because I have a column header at the top of the column
    For j = 2 To finalRow
        ' dynamically resize the array
        ReDim Preserve Result(1 To i)
        ' put the value of the cell into the array
        Result(i) = WSD.Cells(j, 1).value
        i = i + 1
    Next j
    GetFilenames = Result
End Function

Now that I have the list of filenames, I want to open each of the Workbooks up, do something to the Workbook, then Save and Close. I also want to be able to detect if the Workbook is already open so I can handle any case.

Sub WorkbooksLoop()    
    ' get the list of filenames
    Dim filenames() As String
    filenames = GetFilenames()

    ' an error will be thrown if there are no files, just skip loop and end normally
    On Error GoTo NoFilenames

    ' save a handle to the current workbook so we can switch back and forth between workbooks
    Dim controllerwb As Workbook
    Set controllerwb = ActiveWorkbook
    Dim wb As Workbook
    Dim fname As Variant
    
    ' Find the current path for this file to use in opening workbooks in the same directory
    Dim rootPath As String
    rootPath = ThisWorkbook.Path
    rootPath = rootPath & "\"

    For Each fname In filenames
        ' Make the controller active
        controllerwb.Activate
   
        On Error Resume Next
        ' If activate fails, then the workbook isn't open
        Workbooks(fname).Activate
        ' If activate fails, then the workbook isn't open
        If Err <> 0 Then
            ' open the workbook
            Set wb = Workbooks.Open(rootPath & fname)
            ' then activate it
            wb.Activate
        ' Otherwise, workbook is already open, refer to it by name
        Else
            Set wb = Workbooks(fname)
        End If
        
        ' do something to the open workbook
        wb.Cells(1,1).Value = "Sweet!"
    
        ' Save and Close the workbook
        wb.Save
        wb.Close
    Next fname
NoFilenames:
End Sub