Link a cell to another workbook with a different path

[Guest]

VBA for Excel treats path names differently for the Workbooks open and close
methods on the one hand and for use in cell formulae to a cell value to
another in a different workbook.
As long as the two workbooks have the same path, there is no problem, simply
use =[filename.xls]sheetname!celladdress. But should a path name be required,
what do I do?
Also in the workbook properties, path and file names are distinct paths,
both read only. How do I update the path property?

 

[Dave Peterson]

If you open workbook A, then create your formula that links to that workbook A,
you'll see how excel handles the path when you close workbook A.

And you update the path by saving the workbook to a different location. Same
kind of thing with the name--you have to save it with a different name.

VBA for Excel treats path names differently for the Workbooks open and close
methods on the one hand and for use in cell formulae to a cell value to
another in a different workbook.
As long as the two workbooks have the same path, there is no problem, simply
use =[filename.xls]sheetname!celladdress. But should a path name be required,
what do I do?
Also in the workbook properties, path and file names are distinct paths,
both read only. How do I update the path property?

 

[Guest]

Thanks for your reply. I am able to save a link to a cell in another open
workbook and when that "Source" workbook is closed, then the link in the
destination workbook cell formula automatically changes to reflect the full
path and file name. However, if I am developing an application where the
source workbook is not yet there, but would be created at some point in the
future, there is apparantly no way for me to save the link to this yet to be
created workbook.

WHile my current problem has been nicely taken care of, I wonder whether
it is too much to ask for such a capability?

If you open workbook A, then create your formula that links to that workbook A,
you'll see how excel handles the path when you close workbook A.

And you update the path by saving the workbook to a different location. Same
kind of thing with the name--you have to save it with a different name.

VBA for Excel treats path names differently for the Workbooks open and close
methods on the one hand and for use in cell formulae to a cell value to
another in a different workbook.
As long as the two workbooks have the same path, there is no problem, simply
use =[filename.xls]sheetname!celladdress. But should a path name be required,
what do I do?
Also in the workbook properties, path and file names are distinct paths,
both read only. How do I update the path property?

 

[Dave Peterson]

I think I'd link to an always existing dummy workbook.

Then when the other workbook is actually created, I'd change the link
(edit|link) to point at the real one. (or fix the individual formula if you
have other links that can't be changed).

Thanks for your reply. I am able to save a link to a cell in another open
workbook and when that "Source" workbook is closed, then the link in the
destination workbook cell formula automatically changes to reflect the full
path and file name. However, if I am developing an application where the
source workbook is not yet there, but would be created at some point in the
future, there is apparantly no way for me to save the link to this yet to be
created workbook.

WHile my current problem has been nicely taken care of, I wonder whether
it is too much to ask for such a capability?

If you open workbook A, then create your formula that links to that workbook A,
you'll see how excel handles the path when you close workbook A.

And you update the path by saving the workbook to a different location. Same
kind of thing with the name--you have to save it with a different name.

VBA for Excel treats path names differently for the Workbooks open and close
methods on the one hand and for use in cell formulae to a cell value to
another in a different workbook.
As long as the two workbooks have the same path, there is no problem, simply
use =[filename.xls]sheetname!celladdress. But should a path name be required,
what do I do?
Also in the workbook properties, path and file names are distinct paths,
both read only. How do I update the path property?