I have some code which updates the links for named files

Sub UpdateLink1()
ActiveWorkbook.ChangeLink _
"\Forecasting\FY2006\1106\OpsPlan\1106JudgementPaperLinksv003.xls", _
"\Forecasting\FY2007\0307\OpsPlan\0307JudgementPaperLinksv001.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

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
<PhilipJSm...@discussions.microsoft.com> wrote:
> I have some code which updates the links for named files
>
> Sub UpdateLink1()
> ActiveWorkbook.ChangeLink _
> "\Forecasting\FY2006\1106\OpsPlan\1106JudgementPaperLinksv003.xls", _
> "\Forecasting\FY2007\0307\OpsPlan\0307JudgementPaperLinksv001.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

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").Value, _
.Worksheets("sheetName").Range("TextForNewLink").Value, xlExcelLinks
End With
End Sub

---
Jay

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
<PhilipJSm...@discussions.microsoft.com> wrote:
> I have some code which updates the links for named files
>
> Sub UpdateLink1()
> ActiveWorkbook.ChangeLink _
> "\Forecasting\FY2006\1106\OpsPlan\1106JudgementPaperLinksv003.xls", _
> "\Forecasting\FY2007\0307\OpsPlan\0307JudgementPaperLinksv001.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

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").Value, _
> .Worksheets("sheetName").Range("TextForNewLink").Value, xlExcelLinks
> End With
> End Sub
>
> ---
> Jay

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
> <PhilipJSm...@discussions.microsoft.com> wrote:
> > I have some code which updates the links for named files
> >
> > Sub UpdateLink1()
> > ActiveWorkbook.ChangeLink _
> > "\Forecasting\FY2006\1106\OpsPlan\1106JudgementPaperLinksv003.xls", _
> > "\Forecasting\FY2007\0307\OpsPlan\0307JudgementPaperLinksv001.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
>
>
>

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").Value, _
> .Worksheets("sheetName").Range("TextForNewLink").Value, xlExcelLinks
> End With
> End Sub
>
> ---
> Jay

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").Value, _
> > .Worksheets("sheetName").Range("TextForNewLink").Value, xlExcelLinks
> > End With
> > End Sub
> >
> > ---
> > Jay

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").Value, _
> > .Worksheets("sheetName").Range("TextForNewLink").Value, xlExcelLinks
> > End With
> > End Sub
> >
> > ---
> > Jay

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").Value, _
> > > .Worksheets("sheetName").Range("TextForNewLink").Value, xlExcelLinks
> > > End With
> > > End Sub
> > >
> > > ---
> > > Jay