Are relative file locations possible in linking workbooks?

[Angyl]

1st: At my company there is no network...that's right. No shared drives
(and this isn't a small company).

So...

How do I link workbooks together with a relative path so I can send them to
someone else and still have them work.

I.E. if I want a cell in Workbook 2 to be the same as a cell in Workbook 1 I
simply enter

=

and click on the cell in Workbook 1.

What Excel translates that to is ='C:\Documents and Settings\Tallen\My
Documents\Reports\Commercial Discharges\[WorkbookName & Cell Range]')

I need to be able to EMAIL both of these documents to someone and have them
function. I've tried deleting all of the extra pathname garbage from the
formula so hopefully the workbook would just look in the same directory it is
in for the other file...but that doesn't work, Excel just puts the path
information right back in after I delete it.

So is there a way to get Excel to look for a file RELATIVE to its current
location (like the same directory) rather than at a full FIXED PATH?

 

[Boris]

.

So is there a way to get Excel to look for a file RELATIVE to its current
location (like the same directory) rather than at a full FIXED PATH?

Well, if they are in the same folder, there is no problem with paths.
There is also no problem if the folder structure is intact, i.e. if you
have your working workbook in folder X, and linked workbook in folder
X\Data and you move them (or email them) to another place with the exact
same foder structure, it will work as expected.

Otherwise, you can change link sources with simple subroutine, using:

ActiveWorkbook.ChangeLink Name:=sourcepath, _
NewName:=newsourcepath, _
xlExcelLinks

Regards,
B.

 

[RagDyer]

Combine the workbooks into ONE!

 

[Myrna Larson]

On Thu, 1 Nov 2007 11:17:01 -0700, Angyl <[email protected]>
wrote:

No, there isn't. You can use RagDyer's suggestion to combine the sheets into a
single workbook. If you don't do that, then the user will have to put the
files in whatever directory they choose, then open the book with the links and
go to Edit/Links to correct the path to the linked workbook.

 

[Angyl]

Thanks for the suggestion, but I can't. I'm doing running reports for the
company and they are done on a weekly basis, i.e. I'm working on the report
from 10-26 to 11-1 now and when I'm done with it (today) on Monday I will
start the report that covers the period 11-2 through 11-8.

If I combined them, you can see how very quickly this workbook would be
fairly massive in size...hmmm...maybe I'll talk to my boss about doing a
single workbook monthly with the weeks divided into sheets...

Thanks!

Combine the workbooks into ONE!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

1st: At my company there is no network...that's right. No shared drives
(and this isn't a small company).

So...

How do I link workbooks together with a relative path so I can send them
to
someone else and still have them work.

I.E. if I want a cell in Workbook 2 to be the same as a cell in Workbook 1
I
simply enter

=

and click on the cell in Workbook 1.

What Excel translates that to is ='C:\Documents and Settings\Tallen\My
Documents\Reports\Commercial Discharges\[WorkbookName & Cell Range]')

I need to be able to EMAIL both of these documents to someone and have
them
function. I've tried deleting all of the extra pathname garbage from the
formula so hopefully the workbook would just look in the same directory it
is
in for the other file...but that doesn't work, Excel just puts the path
information right back in after I delete it.

So is there a way to get Excel to look for a file RELATIVE to its current
location (like the same directory) rather than at a full FIXED PATH?

 

[Angyl]

No, there isn't. You can use RagDyer's suggestion to combine the sheets into a
single workbook. If you don't do that, then the user will have to put the
files in whatever directory they choose, then open the book with the links and
go to Edit/Links to correct the path to the linked workbook.

Suckage. Thanks for the info.

 

[RagDyeR]

That single, monthly WB is a good idea.

However, what I meant was:
***Combine the 2 WBs *just* for the sending (e-mailing).***

XL *automatically* adjusts the links !

On the receiving end, simply separate them !

XL *automatically* adjusts the links ... *again* ! ! !
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Thanks for the suggestion, but I can't. I'm doing running reports for the
company and they are done on a weekly basis, i.e. I'm working on the report
from 10-26 to 11-1 now and when I'm done with it (today) on Monday I will
start the report that covers the period 11-2 through 11-8.

If I combined them, you can see how very quickly this workbook would be
fairly massive in size...hmmm...maybe I'll talk to my boss about doing a
single workbook monthly with the weeks divided into sheets...

Thanks!

Combine the workbooks into ONE!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

1st: At my company there is no network...that's right. No shared
drives
(and this isn't a small company).

So...

How do I link workbooks together with a relative path so I can send them
to
someone else and still have them work.

I.E. if I want a cell in Workbook 2 to be the same as a cell in Workbook
1
I
simply enter

=

and click on the cell in Workbook 1.

What Excel translates that to is ='C:\Documents and Settings\Tallen\My
Documents\Reports\Commercial Discharges\[WorkbookName & Cell Range]')

I need to be able to EMAIL both of these documents to someone and have
them
function. I've tried deleting all of the extra pathname garbage from
the
formula so hopefully the workbook would just look in the same directory
it
is
in for the other file...but that doesn't work, Excel just puts the path
information right back in after I delete it.

So is there a way to get Excel to look for a file RELATIVE to its
current
location (like the same directory) rather than at a full FIXED PATH?

 

[SK]

I was wondering if you ever found a solution to this solution. I'm going to
attempt to do it tomorrow. I also so this webpage:
http://support.microsoft.com/kb/328440 but haven't gone through it
completely.