Fixing 1,000s of links

  • Thread starter Thread starter Rudy
  • Start date Start date
R

Rudy

Here's the challenge.......

Our organization is upgrading our network from NT to 2000
on the servers, including the share data servers. As part
of the upgrade the engineers either have or are going to
rename the servers with a new convention. One thing they
forgot about was linked information in Excel spreadsheets
(along with other Office apps). So all of our existing
documents (possibly up to 100,000) will become broken if
they have linked cells. Now, from experimentation, I have
found that some will survive if the links were created
with the target book being opened through the default
mapped share, but others were linked with the target
being opened through net..hood, or through a direct path
shortcut.

For example:
A ='S:\marketing\east\[sample.xls]sheet1'!$a$1
B ='\\servername\share\marketing\east\[sample.xls]sheet1'!
$a$1

In the above examples, A will survive, but B will have to
have the servername either removed or replaced with the
S:\.

So....

Is there a way to either create a script or macro that
can be run against a workbook to rewrite the links? Could
we do this without opening the workbooks? Does anybody
know of a utility that could do this?

Any script or macro would have to have enough flexibility
to allow us to add server names, and be stringent enough
so that it only applies itself against links, and not any
data that may emulate one of the obsolete server names.

The closest I have gotten is a macro that displays
formulas then does a find and replace
for "=\\servername\share" and replaces it with "S:\", but
I can't get it to run on all sheets, I have to manually
change sheets. It would have to be smart enough to run on
all sheets (anywhere from 1 to 50 or more).

This is extremely urgent.......

Thanks in advance,

Rudy
 
Seems like you are there already. To get your macro to work on all
sheets :-

'-----------------------------------------
For Each ws in ActiveWorkbook.WorkSheets
' put your code here
Next
'-----------------------------------------

You have to open a workbook before doing anything with it - but you
can, of course, do this using code.


Regards
BrianB
==================================================
 
Rudy,

Here's a small piece of code that gets each and every file
name in a specified directory (including subfolders), with
its full path (in variable filenam).

You'll have to change the folder name, of course, and the
called sub name (subst in my example) that actually does
the job (open workbook, replace, save, close workbook).


Sub GetFileList()
Dim fs, f, f1, f2, fc, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("c:\documents\spreadsheets\")
Set sf = f.subfolders
For Each f1 In sf
Set fc = f1.Files
For Each f2 In fc
filenam = f1 & "\" & f2.Name
Call subst(filenam)
Next
Next
End Sub

It won't be fast, but in lack of a better alternative it
will do the job.

Good luck!

Nikos Y. (nyannaco at in dot gr)
 
Hi Randy,

It seems that you've already solved the most critical part of the puzzle in
being able to read the formula and substitute the new target.

You should be able to use a For ... Each or For ... Next control loop on the
collection of sheets within a workbook.

You need a database of all current server names and replacement values. It
would be easy enough to simply create that database in a master workbook.
You'll also need a control loop that steps thru that database replacing each
occurrence of \\Servername\share with its replacement value.

You'll need yet another database or all of the workbooks you want to
convert. It, too, will require a control loop to step you through each
workbook.

You've already gotten to the nitty gritty part. The most tedious part will
be completing the two databases referenced above. Once that's done you'll
end up with nested control loops that will step thru every element in one
database by every element in the second database (actually, us Access folks
call Excel "databases" TABLES). I doubt that you'll need much more than 100
lines of code all told.

Your existing macro will be inside the innermost control loop; it's what's
doing the work. All of the rest of the code will be just pointing it to the
right places and passing arguments into the variables for the routines doing
the work. For ease of maintenance and future utility I recommend
generalizing the code as much as possible.

I imagine you'll do some testing on real targets as you go but I recommend
that you put the whole thing into code so that you'll have all of the parts
for reference rather than later trying to remember what you've done. Once
done, save your utility workbook. What they've done once they'll do again.
It won't be too long before newer, big bit servers start coming on line -
they, too will probably have new names.

HTH
--
-Larry-
--

Rudy said:
Here's the challenge.......

Our organization is upgrading our network from NT to 2000
on the servers, including the share data servers. As part
of the upgrade the engineers either have or are going to
rename the servers with a new convention. One thing they
forgot about was linked information in Excel spreadsheets
(along with other Office apps). So all of our existing
documents (possibly up to 100,000) will become broken if
they have linked cells. Now, from experimentation, I have
found that some will survive if the links were created
with the target book being opened through the default
mapped share, but others were linked with the target
being opened through net..hood, or through a direct path
shortcut.

For example:
A ='S:\marketing\east\[sample.xls]sheet1'!$a$1
B ='\\servername\share\marketing\east\[sample.xls]sheet1'!
$a$1

In the above examples, A will survive, but B will have to
have the servername either removed or replaced with the
S:\.

So....

Is there a way to either create a script or macro that
can be run against a workbook to rewrite the links? Could
we do this without opening the workbooks? Does anybody
know of a utility that could do this?

Any script or macro would have to have enough flexibility
to allow us to add server names, and be stringent enough
so that it only applies itself against links, and not any
data that may emulate one of the obsolete server names.

The closest I have gotten is a macro that displays
formulas then does a find and replace
for "=\\servername\share" and replaces it with "S:\", but
I can't get it to run on all sheets, I have to manually
change sheets. It would have to be smart enough to run on
all sheets (anywhere from 1 to 50 or more).

This is extremely urgent.......

Thanks in advance,

Rudy
Click to expand...
 
Rudy,

Sorry I botched your name in the earlier post. I'm almost blind (no joke)
and carelessly misread your name.

--
-Larry-
--

Larry Daugherty said:
Hi Randy,

It seems that you've already solved the most critical part of the puzzle in
being able to read the formula and substitute the new target.

You should be able to use a For ... Each or For ... Next control loop on the
collection of sheets within a workbook.

You need a database of all current server names and replacement values. It
would be easy enough to simply create that database in a master workbook.
You'll also need a control loop that steps thru that database replacing each
occurrence of \\Servername\share with its replacement value.

You'll need yet another database or all of the workbooks you want to
convert. It, too, will require a control loop to step you through each
workbook.

You've already gotten to the nitty gritty part. The most tedious part will
be completing the two databases referenced above. Once that's done you'll
end up with nested control loops that will step thru every element in one
database by every element in the second database (actually, us Access folks
call Excel "databases" TABLES). I doubt that you'll need much more than 100
lines of code all told.

Your existing macro will be inside the innermost control loop; it's what's
doing the work. All of the rest of the code will be just pointing it to the
right places and passing arguments into the variables for the routines doing
the work. For ease of maintenance and future utility I recommend
generalizing the code as much as possible.

I imagine you'll do some testing on real targets as you go but I recommend
that you put the whole thing into code so that you'll have all of the parts
for reference rather than later trying to remember what you've done. Once
done, save your utility workbook. What they've done once they'll do again.
It won't be too long before newer, big bit servers start coming on line -
they, too will probably have new names.

HTH
--
-Larry-
--

Rudy said:
Here's the challenge.......

Our organization is upgrading our network from NT to 2000
on the servers, including the share data servers. As part
of the upgrade the engineers either have or are going to
rename the servers with a new convention. One thing they
forgot about was linked information in Excel spreadsheets
(along with other Office apps). So all of our existing
documents (possibly up to 100,000) will become broken if
they have linked cells. Now, from experimentation, I have
found that some will survive if the links were created
with the target book being opened through the default
mapped share, but others were linked with the target
being opened through net..hood, or through a direct path
shortcut.

For example:
A ='S:\marketing\east\[sample.xls]sheet1'!$a$1
B ='\\servername\share\marketing\east\[sample.xls]sheet1'!
$a$1

In the above examples, A will survive, but B will have to
have the servername either removed or replaced with the
S:\.

So....

Is there a way to either create a script or macro that
can be run against a workbook to rewrite the links? Could
we do this without opening the workbooks? Does anybody
know of a utility that could do this?

Any script or macro would have to have enough flexibility
to allow us to add server names, and be stringent enough
so that it only applies itself against links, and not any
data that may emulate one of the obsolete server names.

The closest I have gotten is a macro that displays
formulas then does a find and replace
for "=\\servername\share" and replaces it with "S:\", but
I can't get it to run on all sheets, I have to manually
change sheets. It would have to be smart enough to run on
all sheets (anywhere from 1 to 50 or more).

This is extremely urgent.......

Thanks in advance,

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