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?