User Profile
Elias_Rul
Copper Contributor
Joined Jan 13, 2023
User Widgets
Recent Discussions
Re: 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 SubDisable "Create a Copy" (Recovery file and Blocked by another user)
Hello, I have several workstations, where each station captures information on its own excel file. These files are saved through SharePoint. The information is later imported into another database, therefore, it is very important that the information is saved in the shared file. The people who are capturing the information have limited experience with excel and are prone to many mistakes. Therefore, I am trying to reduce the probability of an error happening. One of the recurring mistakes is creating a copy of the original file. This copy is created locally, therefore I can not access the information. I want to disable the possibility of creating a copy of the file. These copies are created in two ways: 1. File is in use. I prefer that they wait and notify that another user has the file open, rather than them creating a copy. Is there a way to disable the option "Save and edit a copy of the file"? 2. Recovery file. Sometimes excel recommends opening a recovery file, which also creates a copy. I am using macros to capture the information. Each time a capture is made, the file saves automatically, therefore I am not interested in opening a recovery file. Is there a way to disable recovery files? Thanks. Best regards, Elias RulBulk 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
