Referencing a closed workbook

The time it takes will just depend on how big your files are and how many you have to open/close. Below is my macro recommendation. MY DISCLAIMER: This is really general code...without knowing the details of your files, I'm just giving some basics.

(Open Developer, Use the "This Workbook" Excel Object)

Private Sub Workbook_Open()
    Application.ScreenUpdating = False   'This turns off screen updating and speeds up macro
    Workbooks.Open Filename:="L:\Folder\Filename.xlsx"    'Opens the file (rinse)
    ActiveWorkbook.UpdateLink Name:= _
        "L:\Folder\Filename.xlsx", _
        Type:=xlExcelLinks    'Updates links (lather)
    Workbooks.Close Filename:="L:\Folder\Filename.xlsx"    'Close the file (repeat)
    '*********************
    'Copy paste the rinse/lather/repeat sections ;) here and change the file names for each file you have to open. (delete this line)
    '*********************   
    Application.ScreenUpdating = True    'This turns screen updating back on
End Sub

As for this:

Can I set the macro so that it occurs when a cell or cells are changed?

Do you mean when you change cells in the main data sources?

---

If you found this post to be helpful, or if it at least brought a smile to your face, please say "Thank You" by clicking Add Reputation. Thank you!