Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PASTE LINK option not available when I select PASTE SPECIAL to link an image in Excel to a Word document. | Links and Linking in Excel | |||
Macro to paste in the active cell the contents of a cell from another file?? | Excel Programming | |||
macro to paste active cell's full path into another cell | Excel Programming | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming | |||
Macro: Copy and Paste Boomberg Link | Excel Discussion (Misc queries) |