Stop Excel from Updating Formula to external Workbook when copying the formula

[Webtramp]

Hello,
Ok what I am trying to do is the following.
I have two excel files. One (let's call it source) contains several formulas that reference other excel files. I need to copy those formulas from the source to the destination file. This is of course no problem. However as soon as I copy this formula excel updates the formula in my destination file. As I have a bad connection to the server where the files are residing it takes several seconds to update the links.
As I need to loop through a huge amount of cells ( there are several checks to be done on each cell) in this way it would take many hours to do the copy action.
What I would like to do is to first copy all the formulas and then force Excel to recalculate/refresh the external links. So far I tried all the options that came to my mind like setting manual calculation, stop updating external links and so on. However it did not work. As soon as I copy a formula that contains a link to an exernal workbook it is being refreshed.

Any ideas how to prevent Excel from updating those external links?

PS: Here the code I use. It is very simple, just the external links give me headaches

If .Range(rcell.Address).HasFormula Then 'Formel kopieren
rcell.FormulaLocal = .Range(rcell.Address).FormulaLocal
'
else
.
.
.

Thank you

Uwe

[Jan Karel Pieterse]

Why not:

- copy linked file to your local drive
- change links (temporarily) to local version of file
- do your formula work
- change links back to network version.

[Webtramp]

Actually I was thinking about this (copying the referenced files) but this would take a while too, as this concerns several files.
In fact I would have to analyze the formulas and find out which files are being referenced as I do not know before executing the macro. The Vlookups that reference the files are being changed by the users and just at runtime I would know which files are being concerned.
The application has been growing and would actually better fit into a DB but the boss decided to keep it as Excel app.
Isn't there any way to prevent Excel from updating them?

Best regards

Uwe

[Jan Karel Pieterse]

You could of course open the edit links dialog, click each link and choose "Open source" and then save-as all files locally.

[Webtramp]

Hi Jan,
you are right. I guess this can be done also using VBA (just have to figure out the correct way). I will have look at this option. As far as I remember in this way the formulas will also change automatically and therefore no longer pointing to the network folders.
I guess this might do as a workaround as it seems there is no other way to stop the updating procedures when the formula is set.

I'll try this solution and will keep you updated on the outcome.

Regards

Uwe

[Webtramp]

Hi Jan,

so I programmed the concept you proposed and it is sufficient as a workaround. Just took some time as it has to be very dynamically first reading the used LinkSources, saving them in an array, Copying all those files to a temp folder then changing the links.
After this doing all the other actions. In the end changing back the links to the original sources.
It works in this way, however I was hoping to find something like application.calculation that would work also for linksources.
Is there a possibility in Office 2007?

Best regards Uwe