Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.ChangeLink
I have some code which updates the links for named files
Sub UpdateLink1() ActiveWorkbook.ChangeLink _ "\Forecasting\FY2006\1106\OpsPlan\1106JudgementPap erLinksv003.xls", _ "\Forecasting\FY2007\0307\OpsPlan\0307JudgementPap erLinksv001.xls", xlExcelLinks End Sub I want to make the macro dynamic so that it picks up these filenames from named ranges TextForOldLink and TextForNewLink. I tried the following substitution but it didn't work Sub UpdateLink2() ActiveWorkbook.ChangeLink _ Worksheet.Range("TextForOldLink").Value, _ Worksheet.Range("TextForNewLink").Value, xlExcelLinks End Sub Can anyone tell me please, how do I use the contents of Named Ranges in VBA Code? In this case the contents are lables. Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.ChangeLink
Worksheet.Range("TextForNewLink").Value, xlExcelLinks
try taking out the quotes........... Worksheet.Range(TextForNewLink).Value, xlExcelLinks :) susan On Apr 18, 11:10 am, Philip J Smith wrote: I have some code which updates the links for named files Sub UpdateLink1() ActiveWorkbook.ChangeLink _ "\Forecasting\FY2006\1106\OpsPlan\1106JudgementPap erLinksv003.xls", _ "\Forecasting\FY2007\0307\OpsPlan\0307JudgementPap erLinksv001.xls", xlExcelLinks End Sub I want to make the macro dynamic so that it picks up these filenames from named ranges TextForOldLink and TextForNewLink. I tried the following substitution but it didn't work Sub UpdateLink2() ActiveWorkbook.ChangeLink _ Worksheet.Range("TextForOldLink").Value, _ Worksheet.Range("TextForNewLink").Value, xlExcelLinks End Sub Can anyone tell me please, how do I use the contents of Named Ranges in VBA Code? In this case the contents are lables. Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.ChangeLink
Hi Phillip -
Given that your original static approach worked, a small change should be all that is necessary. Change Worksheet.Range to ActiveSheet.Range or to Worksheets("sheetName"). In fact, it might help to restructure your code just a bit. For example: Sub UpdateLink2() With ActiveWorkbook .ChangeLink _ .Worksheets("sheetName").Range("TextForOldLink").V alue, _ .Worksheets("sheetName").Range("TextForNewLink").V alue, xlExcelLinks End With End Sub --- Jay |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.ChangeLink
Worksheet.Range("TextForNewLink").Value, xlExcelLinks
try taking out the quotes............ Worksheet.Range(TextForNewLink).Value, xlExcelLinks :) susan On Apr 18, 11:10 am, Philip J Smith wrote: I have some code which updates the links for named files Sub UpdateLink1() ActiveWorkbook.ChangeLink _ "\Forecasting\FY2006\1106\OpsPlan\1106JudgementPap erLinksv003.xls", _ "\Forecasting\FY2007\0307\OpsPlan\0307JudgementPap erLinksv001.xls", xlExcelLinks End Sub I want to make the macro dynamic so that it picks up these filenames from named ranges TextForOldLink and TextForNewLink. I tried the following substitution but it didn't work Sub UpdateLink2() ActiveWorkbook.ChangeLink _ Worksheet.Range("TextForOldLink").Value, _ Worksheet.Range("TextForNewLink").Value, xlExcelLinks End Sub Can anyone tell me please, how do I use the contents of Named Ranges in VBA Code? In this case the contents are lables. Regards |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.ChangeLink
Hi Phillip -
As Susan suggests, also remove the quotes from the code in my previous post. Thanks Susan! -- Jay "Jay" wrote: Hi Phillip - Given that your original static approach worked, a small change should be all that is necessary. Change Worksheet.Range to ActiveSheet.Range or to Worksheets("sheetName"). In fact, it might help to restructure your code just a bit. For example: Sub UpdateLink2() With ActiveWorkbook .ChangeLink _ .Worksheets("sheetName").Range("TextForOldLink").V alue, _ .Worksheets("sheetName").Range("TextForNewLink").V alue, xlExcelLinks End With End Sub --- Jay |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.ChangeLink
Thanks for your input. I tried it and got the response
Runtime error - Object Required. Regards Phil "Susan" wrote: Worksheet.Range("TextForNewLink").Value, xlExcelLinks try taking out the quotes........... Worksheet.Range(TextForNewLink).Value, xlExcelLinks :) susan On Apr 18, 11:10 am, Philip J Smith wrote: I have some code which updates the links for named files Sub UpdateLink1() ActiveWorkbook.ChangeLink _ "\Forecasting\FY2006\1106\OpsPlan\1106JudgementPap erLinksv003.xls", _ "\Forecasting\FY2007\0307\OpsPlan\0307JudgementPap erLinksv001.xls", xlExcelLinks End Sub I want to make the macro dynamic so that it picks up these filenames from named ranges TextForOldLink and TextForNewLink. I tried the following substitution but it didn't work Sub UpdateLink2() ActiveWorkbook.ChangeLink _ Worksheet.Range("TextForOldLink").Value, _ Worksheet.Range("TextForNewLink").Value, xlExcelLinks End Sub Can anyone tell me please, how do I use the contents of Named Ranges in VBA Code? In this case the contents are lables. Regards |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.ChangeLink
Hi Jay.
Thanks for your response. I tried the code, after ammending for the sheet name and recieved the message "Compile Error - Argument not Optional" .ChangeLink was highlighted. Regards Phil "Jay" wrote: Hi Phillip - Given that your original static approach worked, a small change should be all that is necessary. Change Worksheet.Range to ActiveSheet.Range or to Worksheets("sheetName"). In fact, it might help to restructure your code just a bit. For example: Sub UpdateLink2() With ActiveWorkbook .ChangeLink _ .Worksheets("sheetName").Range("TextForOldLink").V alue, _ .Worksheets("sheetName").Range("TextForNewLink").V alue, xlExcelLinks End With End Sub --- Jay |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.ChangeLink
Hi again Phillip...
I just tested the code without the quotes around the range names and it failed. If it doesn't work without the quotes, put them back in ("TextForOldLink"). -- Jay "Jay" wrote: Hi Phillip - As Susan suggests, also remove the quotes from the code in my previous post. Thanks Susan! -- Jay "Jay" wrote: Hi Phillip - Given that your original static approach worked, a small change should be all that is necessary. Change Worksheet.Range to ActiveSheet.Range or to Worksheets("sheetName"). In fact, it might help to restructure your code just a bit. For example: Sub UpdateLink2() With ActiveWorkbook .ChangeLink _ .Worksheets("sheetName").Range("TextForOldLink").V alue, _ .Worksheets("sheetName").Range("TextForNewLink").V alue, xlExcelLinks End With End Sub --- Jay |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.ChangeLink
Hi Phillip -
After making the quote adjustment in my third post (above), do you still receive the compile error ? If so, check that your puncutation is correct in the ChangeLink statement. Especially check that a space and the continuation underscore character "_" follow the keyword ChangeLink. This sometimes gets automatically deleted and might cause the Compile Error. The compile error is telling us that either the oldlink name or the newlink name is not being provided to the ChangeLink method. If the comma is missing between the two, that would be a likely cause of the compile error. --- Jay "Philip J Smith" wrote: Hi Jay. Thanks for your response. I tried the code, after ammending for the sheet name and recieved the message "Compile Error - Argument not Optional" .ChangeLink was highlighted. Regards Phil "Jay" wrote: Hi Phillip - Given that your original static approach worked, a small change should be all that is necessary. Change Worksheet.Range to ActiveSheet.Range or to Worksheets("sheetName"). In fact, it might help to restructure your code just a bit. For example: Sub UpdateLink2() With ActiveWorkbook .ChangeLink _ .Worksheets("sheetName").Range("TextForOldLink").V alue, _ .Worksheets("sheetName").Range("TextForNewLink").V alue, xlExcelLinks End With End Sub --- Jay |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.ChangeLink
Hi Jay.
Thanks for your help. Ive tried the routine both with and without quotes with the same result. When I run the macro I get an error message. "Compile error Argument not optional" and .ChangeLink is highlighted in VBE Debug. I think that the filenames are not being passed to the .ChangeLink Regards Phil "Jay" wrote: Hi again Phillip... I just tested the code without the quotes around the range names and it failed. If it doesn't work without the quotes, put them back in ("TextForOldLink"). -- Jay "Jay" wrote: Hi Phillip - As Susan suggests, also remove the quotes from the code in my previous post. Thanks Susan! -- Jay "Jay" wrote: Hi Phillip - Given that your original static approach worked, a small change should be all that is necessary. Change Worksheet.Range to ActiveSheet.Range or to Worksheets("sheetName"). In fact, it might help to restructure your code just a bit. For example: Sub UpdateLink2() With ActiveWorkbook .ChangeLink _ .Worksheets("sheetName").Range("TextForOldLink").V alue, _ .Worksheets("sheetName").Range("TextForNewLink").V alue, xlExcelLinks End With End Sub --- Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ChangeLink error | Excel Programming | |||
ChangeLink | Excel Programming | |||
ChangeLink | Excel Programming | |||
ChangeLink | Excel Programming | |||
ChangeLink | Excel Programming |