Find and Replace and Update Links issue

[Geoff]

Hi

the following snippet opens a workbook in Excel 2003, copies a sheet with
the previous month's data and renames it as the current month. It also needs
to replace references to the previous month's workbook with references to the
corresponding month's workbook:

Set tempBk = Workbooks.Open(temp, 0)
Set tempWs = tempBk.Worksheets(prevMon)
tempWs.Copy Before:=tempWs
Set newWs = ActiveSheet
newWs.Name = currMon
newWs.Range("C9:J132").Replace data1Part, data2Part

The problem I am having is that when the replace occurs, each of the
individual cells altered brings up a prompt to update the link to the new
data. Given there are nearly 1000 cells, I don't want the user to have to
answer all of these individually. Is there a way to automate this process?

 

[Dave Peterson]

The only time I've seen this kind of prompt is when I change the formula and try
to link to a workbook or worksheet that doesn't exist.

Any chance that your replace is changing to an invalid source
workbook/worksheet?

And while your testing, change the range to something much smaller than those
992 cells.

 

[Geoff]

Thanks Dave

the wrinkle here is that the copy of excel I and the users have is shared
through the Novell Application Explorer. One of the complications this brings
is that the references I am altering are shown in the address bar with the
user's default file location (on a network drive) replaced by "C:\Documents
and Settings\'USERNAME'\Application Data\Microsoft\Excel\XLSTART\". I am
going to try replacing these references in the workbook with my default file
location hardcoded in, as this seems to be the best workaround for the
moment. It's not ideal as other users may have mapped the drive differently,
but it'll do until I can figure out something better. And yes, definitely
testing on a small range rather than the whole lot

Thanks

--
There are 10 types of people in the world - those who understand binary and
those who don't.

The only time I've seen this kind of prompt is when I change the formula and try
to link to a workbook or worksheet that doesn't exist.

Any chance that your replace is changing to an invalid source
workbook/worksheet?

And while your testing, change the range to something much smaller than those
992 cells.

 

[Dave Peterson]

First, I've never used anything Novell -- but I'm not sure if that's causing a
problem.

Second, Any chance you can dedicate a folder on everyone's C: drive so that the
path wouldn't have to change for all the users?

This would work if you're links are refering to an addin, too.

C:\Utils\GeoffUtils.xla

Would always be in the same location.

Thanks Dave

the wrinkle here is that the copy of excel I and the users have is shared
through the Novell Application Explorer. One of the complications this brings
is that the references I am altering are shown in the address bar with the
user's default file location (on a network drive) replaced by "C:\Documents
and Settings\'USERNAME'\Application Data\Microsoft\Excel\XLSTART\". I am
going to try replacing these references in the workbook with my default file
location hardcoded in, as this seems to be the best workaround for the
moment. It's not ideal as other users may have mapped the drive differently,
but it'll do until I can figure out something better. And yes, definitely
testing on a small range rather than the whole lot

Thanks

 

[Dave Peterson]

ps. Then it becomes a training issue. Just make sure that everyone knows where
to store the files.

Thanks Dave

the wrinkle here is that the copy of excel I and the users have is shared
through the Novell Application Explorer. One of the complications this brings
is that the references I am altering are shown in the address bar with the
user's default file location (on a network drive) replaced by "C:\Documents
and Settings\'USERNAME'\Application Data\Microsoft\Excel\XLSTART\". I am
going to try replacing these references in the workbook with my default file
location hardcoded in, as this seems to be the best workaround for the
moment. It's not ideal as other users may have mapped the drive differently,
but it'll do until I can figure out something better. And yes, definitely
testing on a small range rather than the whole lot

Thanks

 

[Geoff]

Thanks again Dave

after talking to a few people who have been around a while, it seems that
the C: drive reference is more of a legacy issue than a Novell issue - this
report has been updated every month since April 96, at which point the
network structure was entirely different, let alone the version of Excel!

I'll stick with the network drive location I've put in there for the time
being I think, as others need to access the report regularly, and this looks
like it will work without too much hassle. I'm sure I'll hear about it fairly
quickly if this causes any problems. The organisation is looking at
installing Office 2007 in any case so the whole reporting structure may need
to be revisited once that happens - oh joy.

Thanks for your help

--
There are 10 types of people in the world - those who understand binary and
those who don't.

ps. Then it becomes a training issue. Just make sure that everyone knows where
to store the files.