How to convert concatenated text to a link to an external workbook

[Sam]

Here is the detail of my problem:
$A$1 is the directory where the external excel files are, like
C:/temp/data/;
B1:B1000 are the excel files names without the .xls extension, like
File001 to File1000;

C1:C1000 where I want to link to the same cell in the corresponding
files FILE001~1000.xls.

In order to make the link to an external workbook, I need to use the
concatendate() function to provide a link, combining the file diretory
$A$1, filename B1, ".xls", worksheetname and cell reference, something
like 'C:/temp/data/[File001.xls]Sheet1'!$F$1. But concatendate() only
returns the text, not the value of the text. So how can I use the
value of text, not the text of text?

Any suggestions will be welcomed.

Thanks a lot in advance.

 

[Tom Ogilvy]

A1: C:\temp\data\
B1: File001
C1: Sheet1'A1 <= note the single quote

Sub Tester1()
Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
rng.Offset(0, 3).Formula = "=""='""&A1&""[""&B1&"".xls]""&C1"
rng.Offset(0, 3).Value = rng.Offset(0, 3).Value
rng.Offset(0, 3).Replace "=", "="
End Sub

Will put in a hard coded reference formula in column D

D1 (formula): ='C:\temp\data\[File001.xls]Sheet1'!A1

 

[Sam]

Tom,

Thanks a lot to your help. It works pretty well. But one more
question: I have more than one field from each file, how can I make
the code more dynamic?

A1: C:\temp\data
B1: File001
C1: Sheet1'A1
D1: Sheet1'B3
E1: Sheet1'D4

Here is the new code modified from yours:

Sub Tester1()
For Counter = 3 To 5
Set curCell = Worksheets("Sheet1").Cells(1, Counter)
curCell.Offset(0, 5).Formula = "=""='""&A1&""[""&B1&"".xls]" &
curCell.Text
curCell.Offset(0, 5).Value = curCell.Offset(0, 5).Value
curCell.Offset(0, 5).Replace "=", "="

' From "Tom Ogilvy" <[email protected]>
' Sub Tester1()
' Dim rng As Range
' Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
' rng.Offset(0, 3).Formula = "=""='""&A1&""[""&B1&"".xls]""&C1"
' rng.Offset(0, 3).Value = rng.Offset(0, 3).Value
' rng.Offset(0, 3).Replace "=", "="
' End Sub

Next Counter
End Sub

For the following line:
curCell.Offset(0, 5).Formula = "=""='""&A1&""[""&B1&"".xls]" &
curCell.Text
I keep getting Run-time error '1004': Application-defined or object
defined error. But I can't see what is wrong with it. I know it is
something basic, but I just can't find it. How can I get around this?

Thanks a lot.

Sam

A1: C:\temp\data\
B1: File001
C1: Sheet1'A1 <= note the single quote

Sub Tester1()
Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
rng.Offset(0, 3).Formula = "=""='""&A1&""[""&B1&"".xls]""&C1"
rng.Offset(0, 3).Value = rng.Offset(0, 3).Value
rng.Offset(0, 3).Replace "=", "="
End Sub

Will put in a hard coded reference formula in column D

D1 (formula): ='C:\temp\data\[File001.xls]Sheet1'!A1

--
Regards,
Tom Ogilvy

Here is the detail of my problem:
$A$1 is the directory where the external excel files are, like
C:/temp/data/;
B1:B1000 are the excel files names without the .xls extension, like
File001 to File1000;

C1:C1000 where I want to link to the same cell in the corresponding
files FILE001~1000.xls.

In order to make the link to an external workbook, I need to use the
concatendate() function to provide a link, combining the file diretory
$A$1, filename B1, ".xls", worksheetname and cell reference, something
like 'C:/temp/data/[File001.xls]Sheet1'!$F$1. But concatendate() only
returns the text, not the value of the text. So how can I use the
value of text, not the text of text?

Any suggestions will be welcomed.

Thanks a lot in advance.

 

[Tom Ogilvy]

curCell.Offset(0, 5).Formula = "=""='""&A1&""[""&B1&"".xls]" & curCell.Text

there should be two doublequotes after .xls] you have .xls]" should be
..xls]""

accoding to my original code.

--
Regards,
Tom Ogilvy

Tom,

Thanks a lot to your help. It works pretty well. But one more
question: I have more than one field from each file, how can I make
the code more dynamic?

A1: C:\temp\data
B1: File001
C1: Sheet1'A1
D1: Sheet1'B3
E1: Sheet1'D4

Here is the new code modified from yours:

Sub Tester1()
For Counter = 3 To 5
Set curCell = Worksheets("Sheet1").Cells(1, Counter)
curCell.Offset(0, 5).Formula = "=""='""&A1&""[""&B1&"".xls]" &
curCell.Text
curCell.Offset(0, 5).Value = curCell.Offset(0, 5).Value
curCell.Offset(0, 5).Replace "=", "="

' From "Tom Ogilvy" <[email protected]>
' Sub Tester1()
' Dim rng As Range
' Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
' rng.Offset(0, 3).Formula = "=""='""&A1&""[""&B1&"".xls]""&C1"
' rng.Offset(0, 3).Value = rng.Offset(0, 3).Value
' rng.Offset(0, 3).Replace "=", "="
' End Sub

Next Counter
End Sub

For the following line:
curCell.Offset(0, 5).Formula = "=""='""&A1&""[""&B1&"".xls]" &
curCell.Text
I keep getting Run-time error '1004': Application-defined or object
defined error. But I can't see what is wrong with it. I know it is
something basic, but I just can't find it. How can I get around this?

Thanks a lot.

Sam





"Tom Ogilvy" <[email protected]> wrote in message

A1: C:\temp\data\
B1: File001
C1: Sheet1'A1 <= note the single quote

Sub Tester1()
Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
rng.Offset(0, 3).Formula = "=""='""&A1&""[""&B1&"".xls]""&C1"
rng.Offset(0, 3).Value = rng.Offset(0, 3).Value
rng.Offset(0, 3).Replace "=", "="
End Sub

Will put in a hard coded reference formula in column D

D1 (formula): ='C:\temp\data\[File001.xls]Sheet1'!A1

--
Regards,
Tom Ogilvy

Here is the detail of my problem:
$A$1 is the directory where the external excel files are, like
C:/temp/data/;
B1:B1000 are the excel files names without the .xls extension, like
File001 to File1000;

C1:C1000 where I want to link to the same cell in the corresponding
files FILE001~1000.xls.

In order to make the link to an external workbook, I need to use the
concatendate() function to provide a link, combining the file diretory
$A$1, filename B1, ".xls", worksheetname and cell reference, something
like 'C:/temp/data/[File001.xls]Sheet1'!$F$1. But concatendate() only
returns the text, not the value of the text. So how can I use the
value of text, not the text of text?

Any suggestions will be welcomed.

Thanks a lot in advance.