Paste active link with a macro
 

Excel 97 - SR2

My programming skills are limited, a week ago I was on the =Sum(A1:A3)
stage, so bare with me. I am trying to to Visual Basic editor to take the
contents of a cell, (i.e 12345), and create a file path from that, with a
link to a certain cell in that file.
For example,

The user will enter 12345 into cell A1 of '12345.xls', then click onto a
macro button which will create in cells B1,B2 & B3 in 'Workbook2.xls' an
active path to cells C1,C2 & C3 '12345.xls'. The path would be, I think,
something like C:\Dave\[12345.xls]Sheet1!C$1$

I have tried to use the concatenate command '=concatenate("=C:\Dave\[",
"A1", ".xls]Sheet1!C$1$") then use the macro to paste special, and value in a
row of cells then paste that row into the 'workbook2' but workbook2 does not
recognise it as a link, just as =C:\Dave\[12345.xls]Sheet1!C$1$ in cell B2.

The solution could be easy, but the little annoying paperclip just kept
winking at me instead of helping. Any help would be great. Cheers,


--
Dave

Paste active link with a macro
 

Try selecting that range (that you just did the paste special|values) and do one
more edit|replace:

edit|replace
what: =
with: =
replace all

You could record a macro when you do it manually.

You may want to make sure that the range is formatted as General (if that didn't
work).

DaveyJones wrote:

Excel 97 - SR2

My programming skills are limited, a week ago I was on the =Sum(A1:A3)
stage, so bare with me. I am trying to to Visual Basic editor to take the
contents of a cell, (i.e 12345), and create a file path from that, with a
link to a certain cell in that file.
For example,

The user will enter 12345 into cell A1 of '12345.xls', then click onto a
macro button which will create in cells B1,B2 & B3 in 'Workbook2.xls' an
active path to cells C1,C2 & C3 '12345.xls'. The path would be, I think,
something like C:\Dave\[12345.xls]Sheet1!C$1$

I have tried to use the concatenate command '=concatenate("=C:\Dave\[",
"A1", ".xls]Sheet1!C$1$") then use the macro to paste special, and value in a
row of cells then paste that row into the 'workbook2' but workbook2 does not
recognise it as a link, just as =C:\Dave\[12345.xls]Sheet1!C$1$ in cell B2.

The solution could be easy, but the little annoying paperclip just kept
winking at me instead of helping. Any help would be great. Cheers,

--
Dave

--

Dave Peterson