Excel macros updating other files
Hold down the ALT F11 keys to open the Microsoft Visual Basic for Applications window. Click Insert Sub Loop Through Files() Dim x Fd As File Dialog Dim x Fd Item As Variant Dim x File Name As String Set x Fd = Application.
File Dialog(mso File Dialog Folder Picker) If x Fd.
We will look into the Excel file format later in the post, but for now, let’s prove that the linking file does cache the data: The link is no longer valid as the file does not exist, So what happens if you change the lookup value? If Excel caches external data in this way, it means any file may contain data from linked files even if the file cannot be found.
Any users receiving that file could access the cache and see data they should not see.
Let’s suggest a HR department sends workbooks with salary information to each individual employee.
When some Excel users discover the ability to link to other files, they go a bit loopy; linking everything in sight.For example, if Cell A1 is dragged to Cell B1, the file which links to Cell A1 will continue to link to Cell A1 (which is now the wrong cell – ooops! The risk can be mitigated slightly by using Named Ranges.However, if it is a colleagues file, what is to stop them deleting the Named Ranges, or renaming worksheets, just because they want to (work colleagues are nice like that!Example: Data can only be retrieved from the last saved version of a file.If your colleague opens a file and changes some values, the link will not retrieve the updated value until the file is saved.