Q: Changing a link without link validation

  • Thread starter Thread starter Mike Frederick
  • Start date Start date
M

Mike Frederick

Hello,

I have a script that changes an Excel link from one Excel document to
another. How do I supress Excel's link validation process? The new link
will not be available until after more file migration has completed.

Example:

File ABC.XLS, cell A1 contains - ='C:\users\mike\[remote.xls]Sheet1'!A1
and I want to change it to - ='D:\mike\[remote.xls]Sheet1'!A1

But the file D:\mike\remote.xls does not exist at the time of the change
being made to the file ABC.XLS. All of my automation is working, but Excel
prompts me with a dialog box asking me to locate D:\mike\remote.xls. How do
I supress this dialog box?

TIA!
 
Application.displayalerts = false

Rember that when this is off no display alerts come up
such as do you want to save over that file. It just does
it. So I recomend that as soon as you can yuo should turn
them back on again.

Jase
 
OK, that seems to work; the dialog boxes are suppressed. But what about
stopping the actual attempt that Excel makes at validating the reference?
My script works, but in order to change 68 links in one file the script
takes 10 minutes! Watching the script run shows a 20 second delay upon each
link change. Is there a way to avoid the link validation?

TIAA "Thanks In Advance Again"

--
Mike Frederick
(e-mail address removed)

Jase said:
Application.displayalerts = false

Rember that when this is off no display alerts come up
such as do you want to save over that file. It just does
it. So I recomend that as soon as you can yuo should turn
them back on again.

Jase
-----Original Message-----
Hello,

I have a script that changes an Excel link from one Excel document to
another. How do I supress Excel's link validation process? The new link
will not be available until after more file migration has completed.

Example:

File ABC.XLS, cell A1 contains - ='C:\users\mike\[remote.xls]Sheet1'!A1
and I want to change it to - ='D:\mike\[remote.xls] Sheet1'!A1

But the file D:\mike\remote.xls does not exist at the time of the change
being made to the file ABC.XLS. All of my automation is working, but Excel
prompts me with a dialog box asking me to locate D:\mike\remote.xls. How do
I supress this dialog box?

TIA!

--
Mike Frederick
(e-mail address removed)


.
Click to expand...
Click to expand...
 
OK, a little more info. The problem is that the formula being replaced
looks like

=VLOOKUP(C3,"C:\users\mike\data.xls",...

So I guess what I need to do is somehow turn off VLOOKUP evaluation during
my update. I tried doing this manually with Tools->Options/Calculation set
to "manual" and clearing all the other settings, but this didn't fix it. It
still tries to find the new file listed in the VLOOKUP function when I
replace the contents of the cell.

--
Mike Frederick
(e-mail address removed)

Mike Frederick said:
OK, that seems to work; the dialog boxes are suppressed. But what about
stopping the actual attempt that Excel makes at validating the reference?
My script works, but in order to change 68 links in one file the script
takes 10 minutes! Watching the script run shows a 20 second delay upon each
link change. Is there a way to avoid the link validation?

TIAA "Thanks In Advance Again"

--
Mike Frederick
(e-mail address removed)

Jase said:
Application.displayalerts = false

Rember that when this is off no display alerts come up
such as do you want to save over that file. It just does
it. So I recomend that as soon as you can yuo should turn
them back on again.

Jase
-----Original Message-----
Hello,

I have a script that changes an Excel link from one Excel document to
another. How do I supress Excel's link validation process? The new link
will not be available until after more file migration has completed.

Example:

File ABC.XLS, cell A1 contains - ='C:\users\mike\[remote.xls]Sheet1'!A1
and I want to change it to - ='D:\mike\[remote.xls] Sheet1'!A1

But the file D:\mike\remote.xls does not exist at the time of the change
being made to the file ABC.XLS. All of my automation is working, but Excel
prompts me with a dialog box asking me to locate D:\mike\remote.xls. How do
I supress this dialog box?

TIA!

--
Mike Frederick
(e-mail address removed)


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top