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.


"Dave Peterson" wrote:

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 wrote:

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 Befo=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?

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

--

Dave Peterson