Update value on multiple .xlsm files from parent .xlsm file

[JackDanIce]

Hi,

I have a Master file with multiple child files in sub folders below it.

Each child file has a named range for a value and I want to update it from the Master file; the child files have Workbook_Open events.

The following approach will work but I suspect there are faster methods, could anyone suggest? Code is untested, assume Range("Child_Files") is a 2D range containing full UNC paths and at least 100 child files. Application.EnableEvents = False to prevent child file triggering any workbook_open events it has:

Sub Update_Value(byref vari as string)

Dim x as Long
Dim arr() as Variant

arr = Range("Child_Files").Value

With Application
  .ScreenUpdating = False
  .EnableEvents = False
End With

For x = lbound(arr, 1) to ubound(arr, 1)
  With workbooks.open arr(x,1)
      wAdmin.Range("Variable1").Value = vari
      .Close SaveChanges:=True
  End With
Next x
  
With Application
  .ScreenUpdating = True
  .EnableEvents = True
End With

Erase arr

End Sub

There may be 2 variables to update, it will also be updated on sheet object wAdmin in the child files.

TIA,
Jack

 

[JackDanIce]

Bump: Are there any faster ways to update a variable from parent file into multiple child files?

 

[JackDanIce]

Bump: guessing by lack of replies, apart from looping, opening, updating variable and closing each .xlsm file, there isn't a faster approach?