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?

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.