Stop Excel from updating external links when copying in VBA
 

Is there any way to stop Excel from trying to udate external links when
copying ranges (with External links) from one sheet to another in VBA.

Thanks

Stop Excel from updating external links when copying in VBA
 

xlmonkey -

I might be wrong but you probably would be better off making a copy of the
file to work on as I dont think that excel pushes data to linked objects.

I think they get from the data source.

I havent tried different settings but probably depending on your security
settings when you open a file which is linked to a excel file as a data
source it asks if you would like to update / check for new source data.

HTH

Rob

"ExcelMonkey" wrote in message
...
Is there any way to stop Excel from trying to udate external links when
copying ranges (with External links) from one sheet to another in VBA.

Thanks

Stop Excel from updating external links when copying in VBA
 

What I am doing is copying an existing sheet over to a new empty sheet. I
need to do this as I want to see how the formulas update when I copy it
across by 1 column. For example, I copy the range A1:C10 from the first
sheet to the new sheet in cell B1 (the new range now resides in the new sheet
B1:D10). I then assess the formulas in this new range relative to the
formulas in the old range. However when I do this, I get "File not Found"
Message. I am assuming I am getting this as the original formulas have links
to files which may not exist anymore. I actually do not care about the data
in the cells only the formulas. So I do not even want the data to be
updated. I only want to see the formulas updated. I originally thought that
I had to make the DisplayAlerts = False. This didn't work. Then I tried
Application.AskToUpdateLinks = False. This doesn't work either. The
dialogue box keeps coming up. I do not want to see the dialogue box at all.
What can I do if I want to keep this routine in tact and not see the dialogue
box?

EM

"Rob Hargreaves" wrote:

xlmonkey -

I might be wrong but you probably would be better off making a copy of the
file to work on as I dont think that excel pushes data to linked objects.

I think they get from the data source.

I havent tried different settings but probably depending on your security
settings when you open a file which is linked to a excel file as a data
source it asks if you would like to update / check for new source data.

HTH

Rob

"ExcelMonkey" wrote in message
...
Is there any way to stop Excel from trying to udate external links when
copying ranges (with External links) from one sheet to another in VBA.

Thanks

Stop Excel from updating external links when copying in VBA
 

application.displayalerts = false

will stop you getting those messages.
formulas that cannot be evaluated are inserted as TEXT

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ExcelMonkey wrote :

What I am doing is copying an existing sheet over to a new empty
sheet. I need to do this as I want to see how the formulas update
when I copy it across by 1 column. For example, I copy the range
A1:C10 from the first sheet to the new sheet in cell B1 (the new
range now resides in the new sheet B1:D10). I then assess the
formulas in this new range relative to the formulas in the old range.
However when I do this, I get "File not Found" Message. I am
assuming I am getting this as the original formulas have links to
files which may not exist anymore. I actually do not care about the
data in the cells only the formulas. So I do not even want the data
to be updated. I only want to see the formulas updated. I
originally thought that I had to make the DisplayAlerts = False.
This didn't work. Then I tried Application.AskToUpdateLinks = False.
This doesn't work either. The dialogue box keeps coming up. I do
not want to see the dialogue box at all. What can I do if I want to
keep this routine in tact and not see the dialogue box?

EM

"Rob Hargreaves" wrote:

xlmonkey -

I might be wrong but you probably would be better off making a copy
of the file to work on as I dont think that excel pushes data to
linked objects.

I think they get from the data source.

I havent tried different settings but probably depending on your
security settings when you open a file which is linked to a excel
file as a data source it asks if you would like to update / check
for new source data.

HTH

Rob

"ExcelMonkey" wrote in
message ...
Is there any way to stop Excel from trying to udate external
links when copying ranges (with External links) from one sheet to
another in VBA.

Thanks