join location string with variable

  • Thread starter Thread starter igor
  • Start date Start date
I

igor

Hi,

I'm having trouble connecting file location with a file
name. here is what i mean:
In the column A i have file names listed as

aba
abb
abc

these files are located on c:\locations\transfer
i'm trying to get to the Sheet3 and cell E5 on each file.
So at the end i should have
A B
aba value of c:\locations\transfer\[ABA.xls]Sheet3'!e$5
abb value of c:\locations\transfer\[ABB.xls]Sheet3'!e$5
abc value of c:\locations\transfer\[ABC.xls]Sheet3'!e$5

Thank you very much
 
sFirst = "='C:\locations\transfer\["
sLast = ".xls]Sheet3'!e$5"

set cell = Range("A1")
do while not isempty(cell)
cell.offset(0,1).formula = sFirst & cell.value & sLast
set cell = cell.offset(1,0)
Loop
 
First concatenate the text values correctly
A1 value is: abc
B1 value is: c:\locations\transfer\

so C1 =A1 & "[" & B1 & "]sheet3'!E5"

th evalue in C1 should look like the correct formula for
pointing at the cell. I think you;'re still m,issing a
leading single quote.
Now use the INDIRECT function to get th edata out of the
workbopok

D1 = Indirect(C1)


Patrick Molloy
Microsoft Excel MVP
 
You have A1 and B1 reversed between what values are stored and your formula
and you left out the leading single quote in B1 - regardless, Indirect
doesn't work with a closed workbook which is the format of the linking
formula shown.

--
Regards,
Tom Ogilvy

Patrick Molloy said:
First concatenate the text values correctly
A1 value is: abc
B1 value is: c:\locations\transfer\

so C1 =A1 & "[" & B1 & "]sheet3'!E5"

th evalue in C1 should look like the correct formula for
pointing at the cell. I think you;'re still m,issing a
leading single quote.
Now use the INDIRECT function to get th edata out of the
workbopok

D1 = Indirect(C1)


Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Hi,

I'm having trouble connecting file location with a file
name. here is what i mean:
In the column A i have file names listed as

aba
abb
abc

these files are located on c:\locations\transfer
i'm trying to get to the Sheet3 and cell E5 on each file.
So at the end i should have
A B
aba value of c:\locations\transfer\[ABA.xls]Sheet3'!e$5
abb value of c:\locations\transfer\[ABB.xls]Sheet3'!e$5
abc value of c:\locations\transfer\[ABC.xls]Sheet3'!e$5

Thank you very much

.
Click to expand...
Click to expand...
 
I posted a VBA solution ~3.75 hrs after you posted the question. You
haven't said why that was rejected.

--
Regards,
Tom Ogilvy

Igor said:
Tom,
you are correct, that suggestion helped with thinking but
did not resolve the problem. Any ideas on how to do it??

Thank you to all
-----Original Message-----
You have A1 and B1 reversed between what values are stored and your formula
and you left out the leading single quote in B1 - regardless, Indirect
doesn't work with a closed workbook which is the format of the linking
formula shown.

--
Regards,
Tom Ogilvy

First concatenate the text values correctly
A1 value is: abc
B1 value is: c:\locations\transfer\

so C1 =A1 & "[" & B1 & "]sheet3'!E5"

th evalue in C1 should look like the correct formula for
pointing at the cell. I think you;'re still m,issing a
leading single quote.
Now use the INDIRECT function to get th edata out of the
workbopok

D1 = Indirect(C1)


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,

I'm having trouble connecting file location with a file
name. here is what i mean:
In the column A i have file names listed as

aba
abb
abc

these files are located on c:\locations\transfer
i'm trying to get to the Sheet3 and cell E5 on each
file.
So at the end i should have
A B
aba value of c:\locations\transfer\[ABA.xls]Sheet3'! e$5
abb value of c:\locations\transfer\[ABB.xls]Sheet3'! e$5
abc value of c:\locations\transfer\[ABC.xls]Sheet3'! e$5

Thank you very much

.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top