Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChangeLink with Worksheet Named Ranges as Arguments
I saw on an earlier post that the way to update links is to use the
ChangeLink method I used VBA help and derived the following. Sub MSLinks2() ' This Works ActiveWorkbook.ChangeLink _ "\\manches-user1\Common\FINANCE\Forecasting\FY2007\0307\OpsPl an\0307JudgementPaperLinksv001.xls", _ "\\manches-user1\Common\FINANCE\Forecasting\FY2006\1106\OpsPl an\1106JudgementPaperLinksv003.xls", xlExcelLinks End Sub As noted in the comment this worked. I was emboldened to try to get the method to accept strings contained in named ranges TextForOldLink:= "\\manches-user1\Common\FINANCE\Forecasting\FY2007\0307\OpsPl an\0307JudgementPaperLinksv001.xls" This is obtained from: =E21&MID(Sheet2!$B$3,FIND("\",Sheet2!$B$3,20),FIND ("\",Sheet2!$B$3,56)-FIND("\",Sheet2!$B$3,20)+1)&MID(Sheet2!$B$3,FIND("[",Sheet2!$B$3,1)+1,FIND("]",Sheet2!$B$3,1)-FIND("[",Sheet2!$B$3,1)-1) TextForNewLink:= €œ\\manches-user1\Common\FINANCE\Forecasting\FY2006\1106\OpsPl an\1106JudgementPaperLinksv003.xls€ This is obtained from =CONCATENATE($E$23,$C$5,$C$7,"\",$D$9&$D$7,"\OpsPl an\",$D$9&$D$7,"JudgementPaperLinks"&$D$11) An earlier respondent (Jay) suggested that the following would work Sub UpdateLink1() With ActiveWorkbook .ChangeLink _ .ActiveSheet.Range("TextForOldLink").Value , _ .ActiveSheet.Range("TextForNewLink").Value, xlExcelLinks End With End Sub However I get the error message: 'Compile error: argument not optional. Jay has unfortunately stopped monitoring the thread and I have been unable to proceed further. Essentially I just want to pass the contents of a named range (a string) as an argument of the ChangeLink Method. I would be grateful if someone could check the syntax and offer advice on where to look next. Regards Phil Smith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
relative Named Ranges based on worksheet | Excel Worksheet Functions | |||
trying to copy a worksheet containing named ranges to anotherworksheet | Excel Worksheet Functions | |||
Copy worksheet with named ranges to new workbook and keep names | Excel Worksheet Functions | |||
Using worksheet templates with named ranges | Excel Programming | |||
named ranges at workbook and worksheet levels | Excel Programming |