References to other Excel Files - UNC

[Alex Mortusewicz]

Hi everybody!

Our small business company (200 users) is about to migrate
its current IT infrastructure into a new server and file
system.

This migration will affect a great many of our excel
spreadsheets as they refer to other excel files and source
and destination files will be moved into other network-
paths.

--> is there any way to replace existing static references

(like "='F:\Controlling\fy2002\Average Sales
Prices\[Average Prices Q1.xls]Average Price'!$E$15/$X$70")

with both

- UNC links (rather than the reference to a network-
drive 'F:\)

- and the new network path

without having to go through each and every single sheet
in every workbook?

Also: can you set up excel such that it automatically
generates an UNC-link at the time you refer to another
file?


Thanks for your help!
Alex

 

[Mark Rosenkrantz]

Alex;

A brief reply for it is a thing which I'm not totally sure about.

As you mention "static", a bit of the answer might say it all.
That is : Probably not.

There is some VBA code to be written with which one can test if a
file-location is available, or
if a file exists in a specific directory.

But if that might help ?
You will then get into a stiff amount of working around efforts, that you do
not want.
I think it is about balancing between costs and benefits of such an
operation, but I would say : go through each file seperately instead
of spending time on macro's and developping vba-code to do your job.
That might take more time as it seems very complecated !!

I'm very interested in reactions of other users !!

Mark Rosenkrantz.

More Excel ? www.rosenkrantz.nl or (e-mail address removed)

 

[Mike]

I frequently move my Excel files between my work PC, work
server and home PC with no loss of link integrety. How?
Simple. As long as you use the exact same path then it
doesn't matter what drive mapping is used. For example,
if I have 2 files C:\Reports\MyFirstFile.xls and
C:\Reports\MySecondFile.xls which have links to each
other, I can use Windows Explorer (or a file sync program)
to move both of these to S:\Reports\ and the links will
maintain themselves.

So you can backup all the files onto one drive by copying
the whole path, then do your upgrades, then copy from the
backup drive to the new drives without fear of loosing
links. In fact, I would suggest that your users keep
their files on their PC's and run them from there. Only
use the server as backup.

-----Original Message-----
Alex;

A brief reply for it is a thing which I'm not totally sure about.

As you mention "static", a bit of the answer might say it all.
That is : Probably not.

There is some VBA code to be written with which one can test if a
file-location is available, or
if a file exists in a specific directory.

But if that might help ?
You will then get into a stiff amount of working around efforts, that you do
not want.
I think it is about balancing between costs and benefits of such an
operation, but I would say : go through each file seperately instead
of spending time on macro's and developping vba-code to do your job.
That might take more time as it seems very complecated !!

I'm very interested in reactions of other users !!

Mark Rosenkrantz.

More Excel ? www.rosenkrantz.nl or (e-mail address removed)

----------------------------------------------------------

------

Hi everybody!

Our small business company (200 users) is about to migrate
its current IT infrastructure into a new server and file
system.

This migration will affect a great many of our excel
spreadsheets as they refer to other excel files and source
and destination files will be moved into other network-
paths.

--> is there any way to replace existing static references

(like "='F:\Controlling\fy2002\Average Sales
Prices\[Average Prices Q1.xls]Average Price'!$E$15/ $X$70")

with both

- UNC links (rather than the reference to a network-
drive 'F:\)

- and the new network path

without having to go through each and every single sheet
in every workbook?

Also: can you set up excel such that it automatically
generates an UNC-link at the time you refer to another
file?


Thanks for your help!
Alex

.

 

[Alex Mortusewicz]

Hi Mark:

thanks for your feedback - we are (based on similar
feedback from other sides) actually thinking about doing
it file by file... It'll be an awful lot of work though -
20 interacting users, 5 fiscal years, ... only in my
department. :-(

Regards,
Alex

-----Original Message-----
Alex;

A brief reply for it is a thing which I'm not totally sure about.

As you mention "static", a bit of the answer might say it all.
That is : Probably not.

There is some VBA code to be written with which one can test if a
file-location is available, or
if a file exists in a specific directory.

But if that might help ?
You will then get into a stiff amount of working around efforts, that you do
not want.
I think it is about balancing between costs and benefits of such an
operation, but I would say : go through each file seperately instead
of spending time on macro's and developping vba-code to do your job.
That might take more time as it seems very complecated !!

I'm very interested in reactions of other users !!

Mark Rosenkrantz.

More Excel ? www.rosenkrantz.nl or (e-mail address removed)

----------------------------------------------------------

------

Hi everybody!

Our small business company (200 users) is about to migrate
its current IT infrastructure into a new server and file
system.

This migration will affect a great many of our excel
spreadsheets as they refer to other excel files and source
and destination files will be moved into other network-
paths.

--> is there any way to replace existing static references

(like "='F:\Controlling\fy2002\Average Sales
Prices\[Average Prices Q1.xls]Average Price'!$E$15/ $X$70")

with both

- UNC links (rather than the reference to a network-
drive 'F:\)

- and the new network path

without having to go through each and every single sheet
in every workbook?

Also: can you set up excel such that it automatically
generates an UNC-link at the time you refer to another
file?


Thanks for your help!
Alex

.