I have a program that breaks down a large Excel report into individual
team reports. It searches through finds all of the teams and then
copies out only that team's info into identically formatted sheets per
team for them to update. As the VBA is compiling the sheets, I have
the fields that will get updated on the team reports remote referenced
by the main report. All the team reports get saved as shared on our
large and slow file server so that all may update. As the teams
complete their reports the "Main" report is completed via the remote
references.

The problem is the length of time it takes this report to run, and I've
pinned it down to the remote references in the main sheet.

When debugging this on my personal server space, the report will build
in about 5 minutes. When building out to the live file server, the
report takes 30 minutes to complete.

So far here is what I have tried:

I have tired to change the cell calculation to manual, and turned off
the update remote references.

I have tried setting all the cells with references as text. The file
runs very quickly, but when I go back and change the cells to general
format, the references wont update without entering and exiting the
cell's edit mode (i.e. F2 or double click). The CalcNow button wont
even update the cells.

I have tried running on the personal server space, and then moving the
sheets to the public drive and updating with the find dialog box. This
works, but it doesn't keep the new file paths and you have to reselect
all of the team files upon every opening of the main file.

I have suppressed the Application.EnableEvents, The .DisplayAlerts(this
at least keeps the find file box from popping up during the code
execution) and the auto calculation.

My problem that I'm excruciatingly detailing is this:

When adding a remote reference to a file on our shared server, it seems
that even though all calculations and alerts are suppressed, Excel is
still going out to the bandwidth-choked server and looking for the
file... very slowly. Depending on the number of references, some
rows take 3+ seconds to update. When I run the same code on a lower
overhead server I can have report completed in 5 minutes. The only
solution I have come up with is to run on a low overhead server, copy
all the formulas to notepad, and replace the low overhead server path
with the high overhead server path and paste all of it back into Excel.


Is there any version of the above work arounds that would work, or is
there a something completely different that would solve this problem.

Thanks,