Bulk Action: Refresh update links on multiple input and output files

[Elias_Rul]

Hello,

I am collecting information from multiple computers which all save the information on their own files. These files are all saved in OneDrive. (Not sure if this is important, but the files are saved as .xlsm to run macros)

Within each file, I can have 2 or more databases (Sheets). For example, on computer 1 I have the databases (Sheet) A, B, and C. On computer 2, I also have the same A, B, and C databases, however, the information that is captured is different since the products are different, however, the format of each database is the same. 

To collect the information I have 1 file for each database. For example, on the file for database collection of A, I import the information from 1A, 2A, 3A... The same is done for the other databases.

With this, I have a single file for each database that merges the information captured from each computer. (ie. BD_A, BD_B, BD_C.....)

Then I import this information to another file which is my "Master Database", where I import all the summary databases into a single file, where each database is saved on an excel sheet.

The information in the "Master Database" file is then used by different departments.

For this process to work, I need to be able to update the import links on all files, so that I have the current information. 

Is there a way to make a bulk action with VBA, Power Automate, or another tool, to update all links on all files at once?

Thank you.

Best regards,

Elias Rul

[#2]

@Elias_Rul 

Solved

I am running a macro that opens the files, updates the links, saves the file, and closes the file. This is done on a time loop that starts when the file is open.

To add flexibility to the file, I created a table with the list of files I wanted to update. I also created different levels, as some files need to update before the next.

Additionally, I created a dashboard where I can stop or start the auto-update, set the frequency of each loop, and also call the macro without a time delay.

Code bellow:

Option Explicit
Dim RunTimer As Date

Sub Update_Links_All_Files()

RunTimer = Now() + Sheets("Dashboard").Range("O13").Value
Application.OnTime RunTimer, "Update_Links_All_Files"

'Variables
Dim WS_Active, MacroName As String
Dim WS_Nivel1, WS_Nivel As String
Dim num_Rows, num_Nivel1, num_Nivel2, nivel, a, b, c, i As Integer
Dim update As Date

Workbooks("Update_All_Links.xlsm").Activate
Sheets("AdressBook").Select

a = 1
b = 1

num_Rows = WorksheetFunction.CountA(Range("A:A")) - 1
num_Nivel1 = WorksheetFunction.CountIfs(Range("f:f"), 1)
num_Nivel2 = WorksheetFunction.CountIfs(Range("f:f"), 2)

ReDim WS_Nivel1(num_Nivel1), WS_Nivel2(num_Nivel2) As Variant

'Get links by Level
For i = 1 To num_Rows
nivel = Cells(1 + i, 6)

If (nivel = 1) Then
WS_Nivel1(a) = Cells(1 + i, 7)
a = a + 1

End If

If (nivel = 2) Then
WS_Nivel2(b) = Cells(1 + i, 7)
b = b + 1
End If
Next i

'Update Links level 1
For i = 1 To (a - 1)
WS_Active = WS_Nivel1(i)

Workbooks.Open WS_Active

If "Update_All_Links.xlsm" = Workbooks(1).Name Then
Workbooks(2).Activate
Else
Workbooks(1).Activate
End If

UpdateLinks
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks("Update_All_Links.xlsm").Activate
Sheets("AdressBook").Select
c = WorksheetFunction.Match(WS_Active, Range("G:G"))
update = Now()
Cells(c, 8) = update

Next i

'Update Links Level 2
For i = 1 To (b - 1)
WS_Active = WS_Nivel2(i)

Workbooks.Open WS_Active

If "Update_All_Links.xlsm" = Workbooks(1).Name Then
Workbooks(2).Activate
Else
Workbooks(1).Activate
End If

UpdateLinks
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks("Update_All_Links.xlsm").Activate
Sheets("AdressBook").Select
c = WorksheetFunction.Match(WS_Active, Range("G:G"))
update = Now()
Cells(c, 8) = update
Next i
Sheets("Dashboard").Select

End Sub

Sub UpdateLinks()
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
End Sub

Sub StopUpdateLinks()
If Range("O4") = True Then
Application.OnTime RunTimer, "Update_Links_All_Files", , False
Range("O4") = False
End If
End Sub

Sub StartUpdateLinks()
If Range("O4") = False Then
Application.OnTime RunTimer, "Update_Links_All_Files", , True
Range("O4") = True
End If

End Sub