Excel: Can using external links improve performance?

Question

I'm wondering if there is a performance gain when using external links vs. opening a spreadsheet and copying cells (using VBA).

I would imagine that Excel has to open the file just the same, but when I test using the following code, external linking comes out faster:

Dim t As Double
Dim rng As Range

For Each rng In shtId.Range("A1:A5")
    t = Now
    ThisWorkbook.Names("rngID").RefersToRange = rng
    ThisWorkbook.UpdateLink "H:\manualData.xlsx", xlExcelLinks
    Debug.Print "link: " & Format(Now - t, "######.0000000")

    t = Now
    Workbooks.Open "H:\manualData.xlsx", readonly:=True
    ActiveWorkbook.Close False
    Debug.Print "open: " & Format(Now - t, "######.0000000")
Next


Results:

link: .0000116
open: .0000231
link: .0000116
open: .0000347
link: .0000000
open: .0000347
link: .0000000
open: .0000347
link: .0000000
open: .0000347

The workbook has a range of cells with lookup formulas keyed on an ID field. These formulas have external links. To test it, I change the ID and force an update. To test the file open approach, I just open and close the source file.

I'm looking to speed up a process that's having an issue due to low bandwidth over the network. I've already explored various options and would just like to understand if this is a valid one. I searched on this topic and some people say external links could cause performance issues while some say otherwise. I'd like to get a better idea of the mechanism behind external links so that I can understand what to expect when implemented.

Any thoughts?

Behka

Good question. I don't work much with external links, but it makes sense to me that merely updating an external link (i.e., directly accessing its contents) would be faster than opening the file, accessing its contents, and closing the file. But I also recall some cases where updating links is faster when both workbooks are open. So, good question, +1 for you, anxious to see what others say about it. – Spiers

Measure again with (1) ScreenUpdating set false; and (2) sufficient data valume to drive the measured times > 2 or 3 seconds. There are far too many confounds with performance measurments less than that, making them useless for meaningful comparisons. – Kayceekaye

Also, quite frankly, I regard spreadsheets with external links as "broken" spreadsheets. It is just a matter of time until incorrect results are published, possibly with disasterous consequences. Put your data in a database, where it can be properly managed, instead of inside an analytical tool with no support for proper data management. – Kayceekaye

I'm interested in the mechanics of external links. For instance, when I have the source file open on another computer, external links were slower, to the point that it took the same time (or more) as opening the file directly. If the source file is not open, external links were noticeably quicker. This made me wonder if Excel caches linked data and just updates when it detects a stale cache. I’m just speculating here. I’m hoping someone has a more definite explanation of how external links work so I can understand from a performance perspective. – Behka

I don't have a more definitive answer, but I agree with your speculation. I will also echo @PieterGeerkens concerns about spreadsheet linking being error prone, especially since users can opt out of updating and a misplaced file can completely break the whole thing. A database solution may be overkill however since the amount of data to be managed is not clear. – Discomfortable

I understand the concerns well and have a background that includes data warehouse/BI. And I do appreciate you guys pointing it out for my sake and for others. I welcome any new caveats, but hope to get more insight into my question and also want to avoid having the conversation straying off a bit. You guys gotta trust me that at this time exploring this option is legit. There's more to the situation than meets the eye and I won't bore you (or complain) with the details. – Behka

Update: I searched some more and did find hints into what I suspected. I ended up looking into the file archive (I'm using Office 2010). I renamed the extension to zip. I found within the folder structure xl\externalLinks. I opened one of the larger xml files and saw that it contained all the data from the source file I link to. So, I assume snapshots of external links are stored within the file itself which would explain why it's so quick (i.e. accessing from memory). I wonder at this point what triggers a refresh and is there a size limit to this caching? Also can this be cleared? – Behka

Answer 1

It's a lot more work, but reading / writing the data from / to an XML file (using MSXML) would solve the performance issue. It's a route I've been forced to adopt under certain circumstances where bandwidth has been low.

The speed at which VBA can retrieve the data and run calculations is a lot quicker than using multiple links. Depending on the circumstances; you could do this on a Workbook Open Event, or even a specific Change Event (ComboBox etc.) since you're only working with kB of data.

Sisal