Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace and Update Links issue
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace and Update Links issue
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace and Update Links issue
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace and Update Links issue
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. Geoff wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace and Update Links issue
ps. Then it becomes a training issue. Just make sure that everyone knows where
to store the files. Geoff wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace and Update Links issue
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. "Dave Peterson" wrote: ps. Then it becomes a training issue. Just make sure that everyone knows where to store the files. Geoff wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND & REPLACE ISSUE | Excel Discussion (Misc queries) | |||
Excel Issue: Links within a Workbook don't appear to update | Excel Discussion (Misc queries) | |||
find, replace, update links | Excel Discussion (Misc queries) | |||
A troubling issue with external links update and workboon_open macro | Excel Programming | |||
Replace Update Links prompt | Excel Programming |