Posted this in Link section but no response yet so posting here. I need to
link many cells in 2 workbooks to transfer data from one to the other; the
path to the
source workbook will always be the same except for one folder, which
is the project number and will vary every time. Is there an easier or more
elegant way to insert this variable folder into the link path other than
CONCATENATE? I have several options on how to get user to enter the project
number, but am missing that final step of getting user's input into the path
link. Example: '\\server\abc\def\PROJECT NUMBER GOES
HERE\[filename.xls]worksheetname'!a1. Thanks!

Lynn wrote:
Posted this in Link section but no response yet so posting here. I need to
link many cells in 2 workbooks to transfer data from one to the other; the
path to the
source workbook will always be the same except for one folder, which
is the project number and will vary every time. Is there an easier or more
elegant way to insert this variable folder into the link path other than
CONCATENATE? I have several options on how to get user to enter the project
number, but am missing that final step of getting user's input into the path
link. Example: '\\server\abc\def\PROJECT NUMBER GOES
HERE\[filename.xls]worksheetname'!a1. Thanks!



Seems pretty straightforward... obtain the user input with a form
control or input box and build up the path string via concatenation. Or
am I missing something?

Sorry for the late reply, away from email for a few days. That was the whole
point of my question - is there a way to do it without having to concatenate
several strings?
Still hoping for another way to do it...

"smartin" wrote:

Lynn wrote:
Posted this in Link section but no response yet so posting here. I need to
link many cells in 2 workbooks to transfer data from one to the other; the
path to the
source workbook will always be the same except for one folder, which
is the project number and will vary every time. Is there an easier or more
elegant way to insert this variable folder into the link path other than
CONCATENATE? I have several options on how to get user to enter the project
number, but am missing that final step of getting user's input into the path
link. Example: '\\server\abc\def\PROJECT NUMBER GOES
HERE\[filename.xls]worksheetname'!a1. Thanks!



Seems pretty straightforward... obtain the user input with a form
control or input box and build up the path string via concatenation. Or
am I missing something?

Hi Lynn,

I cannot think of any other way...

PathPart1 = "\\server\abc\def\"
PathPart2 = "\[filename.xls]worksheetname!A1"
UserInput = ' whatever you use to obtain from UI

MyPath = PathPart1 & UserInput & PathPart2

There is nothing inelegant about this; that is the way it is done. I
still feel like I'm missing something. Why the aversion to this way?


Lynn wrote:
Sorry for the late reply, away from email for a few days. That was the whole
point of my question - is there a way to do it without having to concatenate
several strings?
Still hoping for another way to do it...

"smartin" wrote:

Lynn wrote:
Posted this in Link section but no response yet so posting here. I need to
link many cells in 2 workbooks to transfer data from one to the other; the
path to the
source workbook will always be the same except for one folder, which
is the project number and will vary every time. Is there an easier or more
elegant way to insert this variable folder into the link path other than
CONCATENATE? I have several options on how to get user to enter the project
number, but am missing that final step of getting user's input into the path
link. Example: '\\server\abc\def\PROJECT NUMBER GOES
HERE\[filename.xls]worksheetname'!a1. Thanks!


Seems pretty straightforward... obtain the user input with a form
control or input box and build up the path string via concatenation. Or
am I missing something?

Just because I'm lazy and I'm going to have to code this independently into
every cell the user has a link in, since PathPart2 is different in every
cell. I did start working on it using concatenation function earlier today
since you were the only one to respond and I need to get this done. Going to
have to change each cell at some point I guess.
"smartin" wrote:

Hi Lynn,

I cannot think of any other way...

PathPart1 = "\\server\abc\def\"
PathPart2 = "\[filename.xls]worksheetname!A1"
UserInput = ' whatever you use to obtain from UI

MyPath = PathPart1 & UserInput & PathPart2

There is nothing inelegant about this; that is the way it is done. I
still feel like I'm missing something. Why the aversion to this way?


Lynn wrote:
Sorry for the late reply, away from email for a few days. That was the whole
point of my question - is there a way to do it without having to concatenate
several strings?
Still hoping for another way to do it...

"smartin" wrote:

Lynn wrote:
Posted this in Link section but no response yet so posting here. I need to
link many cells in 2 workbooks to transfer data from one to the other; the
path to the
source workbook will always be the same except for one folder, which
is the project number and will vary every time. Is there an easier or more
elegant way to insert this variable folder into the link path other than
CONCATENATE? I have several options on how to get user to enter the project
number, but am missing that final step of getting user's input into the path
link. Example: '\\server\abc\def\PROJECT NUMBER GOES
HERE\[filename.xls]worksheetname'!a1. Thanks!


Seems pretty straightforward... obtain the user input with a form
control or input box and build up the path string via concatenation. Or
am I missing something?

Sounds like a link maintenance nightmare.

Perhaps with some effort, you could build a table somewhere that maps
cells to workbook names. Then your code could refer to that table to
obtain the correct target workbook name for a given cell. Assuming your
workbook structure is stable (things don't move around).

Lynn wrote:
Just because I'm lazy and I'm going to have to code this independently into
every cell the user has a link in, since PathPart2 is different in every
cell. I did start working on it using concatenation function earlier today
since you were the only one to respond and I need to get this done. Going to
have to change each cell at some point I guess.
"smartin" wrote:

Hi Lynn,

I cannot think of any other way...

PathPart1 = "\\server\abc\def\"
PathPart2 = "\[filename.xls]worksheetname!A1"
UserInput = ' whatever you use to obtain from UI

MyPath = PathPart1 & UserInput & PathPart2

There is nothing inelegant about this; that is the way it is done. I
still feel like I'm missing something. Why the aversion to this way?


Lynn wrote:
Sorry for the late reply, away from email for a few days. That was the whole
point of my question - is there a way to do it without having to concatenate
several strings?
Still hoping for another way to do it...

"smartin" wrote:

Lynn wrote:
Posted this in Link section but no response yet so posting here. I need to
link many cells in 2 workbooks to transfer data from one to the other; the
path to the
source workbook will always be the same except for one folder, which
is the project number and will vary every time. Is there an easier or more
elegant way to insert this variable folder into the link path other than
CONCATENATE? I have several options on how to get user to enter the project
number, but am missing that final step of getting user's input into the path
link. Example: '\\server\abc\def\PROJECT NUMBER GOES
HERE\[filename.xls]worksheetname'!a1. Thanks!

Seems pretty straightforward... obtain the user input with a form
control or input box and build up the path string via concatenation. Or
am I missing something?

You bring up an interesting point that I hadn't considered. The source
workbook is generated by the cost estimating software we use. If they change
their template we're hosed! I also need to verify with them that the sheet
we're linking to in this workbook is the same for every estimate processed.
Would make sense that it is, since they pull numbers from this breakdown for
their own summaries, but making sense doesn't make it so! This all could have
been avoided if someone here hadn't decided he could do it instead of paying
the software company to link their workbook to our standard estimate summary
workbook.

And don't even get me started on them changing our summary workbook!

"smartin" wrote:

Sounds like a link maintenance nightmare.

Perhaps with some effort, you could build a table somewhere that maps
cells to workbook names. Then your code could refer to that table to
obtain the correct target workbook name for a given cell. Assuming your
workbook structure is stable (things don't move around).

Lynn wrote:
Just because I'm lazy and I'm going to have to code this independently into
every cell the user has a link in, since PathPart2 is different in every
cell. I did start working on it using concatenation function earlier today
since you were the only one to respond and I need to get this done. Going to
have to change each cell at some point I guess.
"smartin" wrote:

Hi Lynn,

I cannot think of any other way...

PathPart1 = "\\server\abc\def\"
PathPart2 = "\[filename.xls]worksheetname!A1"
UserInput = ' whatever you use to obtain from UI

MyPath = PathPart1 & UserInput & PathPart2

There is nothing inelegant about this; that is the way it is done. I
still feel like I'm missing something. Why the aversion to this way?


Lynn wrote:
Sorry for the late reply, away from email for a few days. That was the whole
point of my question - is there a way to do it without having to concatenate
several strings?
Still hoping for another way to do it...

"smartin" wrote:

Lynn wrote:
Posted this in Link section but no response yet so posting here. I need to
link many cells in 2 workbooks to transfer data from one to the other; the
path to the
source workbook will always be the same except for one folder, which
is the project number and will vary every time. Is there an easier or more
elegant way to insert this variable folder into the link path other than
CONCATENATE? I have several options on how to get user to enter the project
number, but am missing that final step of getting user's input into the path
link. Example: '\\server\abc\def\PROJECT NUMBER GOES
HERE\[filename.xls]worksheetname'!a1. Thanks!

Seems pretty straightforward... obtain the user input with a form
control or input box and build up the path string via concatenation. Or
am I missing something?

Another thought... maybe you can inspect the cell formulas in code and
parse out the link targets on the fly. Avoids maintaining a table, and
possibly dodges the impact of cells moving around.

Never tried it myself, mind you.

Lynn wrote:
You bring up an interesting point that I hadn't considered. The source
workbook is generated by the cost estimating software we use. If they change
their template we're hosed! I also need to verify with them that the sheet
we're linking to in this workbook is the same for every estimate processed.
Would make sense that it is, since they pull numbers from this breakdown for
their own summaries, but making sense doesn't make it so! This all could have
been avoided if someone here hadn't decided he could do it instead of paying
the software company to link their workbook to our standard estimate summary
workbook.

And don't even get me started on them changing our summary workbook!

"smartin" wrote:

Sounds like a link maintenance nightmare.

Perhaps with some effort, you could build a table somewhere that maps
cells to workbook names. Then your code could refer to that table to
obtain the correct target workbook name for a given cell. Assuming your
workbook structure is stable (things don't move around).

Lynn wrote:
Just because I'm lazy and I'm going to have to code this independently into
every cell the user has a link in, since PathPart2 is different in every
cell. I did start working on it using concatenation function earlier today
since you were the only one to respond and I need to get this done. Going to
have to change each cell at some point I guess.
"smartin" wrote:

Hi Lynn,

I cannot think of any other way...

PathPart1 = "\\server\abc\def\"
PathPart2 = "\[filename.xls]worksheetname!A1"
UserInput = ' whatever you use to obtain from UI

MyPath = PathPart1 & UserInput & PathPart2

There is nothing inelegant about this; that is the way it is done. I
still feel like I'm missing something. Why the aversion to this way?


Lynn wrote:
Sorry for the late reply, away from email for a few days. That was the whole
point of my question - is there a way to do it without having to concatenate
several strings?
Still hoping for another way to do it...

"smartin" wrote:

Lynn wrote:
Posted this in Link section but no response yet so posting here. I need to
link many cells in 2 workbooks to transfer data from one to the other; the
path to the
source workbook will always be the same except for one folder, which
is the project number and will vary every time. Is there an easier or more
elegant way to insert this variable folder into the link path other than
CONCATENATE? I have several options on how to get user to enter the project
number, but am missing that final step of getting user's input into the path
link. Example: '\\server\abc\def\PROJECT NUMBER GOES
HERE\[filename.xls]worksheetname'!a1. Thanks!

Seems pretty straightforward... obtain the user input with a form
control or input box and build up the path string via concatenation. Or
am I missing something?