VBA: Open and close all source files to refresh Master Workbook data

[cyanidesun]

I am looking for a way to use VBA in combination with a macro button to force my Master Workbook to open and close all source files in order to refresh all of the external link data I have coming into my workbook. I have about 240 other workbooks that could be potentially pulled from, within 8 different folders (30 in each folder).

I know the real answer is to update values in my links, but I am investigating an issue that is preventing my links from updating (despite everything working when workbooks are open). Therefore I need this workaround. I am using Microsoft 365 for Enterprise.

 

[t0ny84]

Something like this should work. You will need to add a sheet called OPEN to the workbook and list the full path and filenames that you want to reopen.
It will then close all Excel Spreadsheets except for the one the script is running in. It will then go through the list in column A on sheet OPEN and open the sheets again.

Sub CloseAndOpenRefresh()
    Dim currentWorkbook As Workbook
    Dim wsOpenList As Worksheet
    Dim cell As Range
    Dim filePath As String
    
    ' Set reference to the current workbook
    Set currentWorkbook = ThisWorkbook
    
    ' Set reference to the OPEN sheet
    Set wsOpenList = currentWorkbook.Sheets("OPEN")
    
    ' Close all workbooks except the current one
    For Each wb In Application.Workbooks
        If wb.Name <> currentWorkbook.Name Then
            wb.Close SaveChanges:=False
        End If
    Next wb
    
    ' Open workbooks listed with file paths in column A of the OPEN sheet
    For Each cell In wsOpenList.Range("A1:A" & wsOpenList.Cells(wsOpenList.Rows.Count, "A").End(xlUp).Row)
        filePath = cell.Value
        If filePath <> "" Then
            Workbooks.Open filePath
        End 
If
    Next cell
End Sub

t0ny84