Forum Discussion

Elias_Rul's avatar
Elias_Rul
Copper Contributor
Jan 13, 2023

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

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

 

 

excel
Excel for web
Macros and VBA

1 Reply

Sort By
  • Elias_Rul's avatar
    Elias_Rul
    Copper Contributor
    Jan 25, 2023

    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

Resources