How to update data of all excel files by opening only the file that is used as a source?

Question

excel

I have a master file in which data is loaded. Next, there are 10 Excel files that take data only from the master file (data source).

How can I update all these 10 files automatically so that I don't have to go into each of them, but only into the master to update the data?

Answer 1

You can check the box in the settings so that the data is updated when the file is opened.
You can also use vba. In your "master file" make a button that will run the code that updates your files.
Something like that:

Sub RefreshWBs()
    Dim CurFile As String, DirLoc As String
    Dim DestWB As Workbook
    Dim ws As Object 'allows for different sheet types
    Dim OrigWB As Workbook
    [COLOR=#0000ff]DirLoc = "H:\MSO\Documents"[/COLOR]
    CurFile = Dir(DirLoc & "\*.xls*")
    
    Application.ScreenUpdating = True
    Application.EnableEvents = False
            
    Do While CurFile <> vbNullString
        Application.DisplayAlerts = False
        
        'Workbooks.Open Filename:=DirLoc & "\" & CurFile
        Set OrigWB = Workbooks.Open(Filename:=DirLoc & "\" & CurFile)
        
        OrigWB.RefreshAll
        Application.DisplayAlerts = True
        OrigWB.Close SaveChanges:=True
        CurFile = Dir
    Loop
 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
 
      Set DestWB = Nothing
  End Sub