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

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, 😎 = 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, 😎 = 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