Webtramp
06-15-2009, 12:47 AM
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
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