Reference cell data to update links

[ukkz001]

I have a cell containing the file path and name. I would like to refernce that cell in a macro so that it uses that file info to updates links.

EX. of the cell contiain the file link info:
="'Z:\Department\\ACCTG\Statements\"&TEXT('Daily Rec'!$A$3,"yyyy")&"\"&TEXT('Daily Rec'!$A$3,"mmmm")&"\[Daily Report "&TEXT('Daily Rec'!$A$3,"mm dd.yyyy")&" xls (2).xlsx]Daily Cash Report'!$B$8:$K$500"


Any ideas??

Thanks

 

[GlennUK]

Are you saying that your workbook already has links, and that you'd life to change the link source to be whatever that cell results are?

Can you also say whether your workbook has one link source or many?

 

[ukkz001]

Yes. The workbook already contains links but the link source changes, the user has to manually change the link source. I would like the macro to automatically change the link source based on the path and file name located in a cell that gets updated.

Thanks,
Kevin

 

[GlennUK]

This might work:

Sub ChangeMyLinks()
    alinks = ThisWorkbook.LinkSources
    ThisWorkbook.ChangeLink Name:=alinks(1), NewName:= _
        Worksheets("my sheet name").Range("A1").Value, Type:=xlExcelLinks
End Sub

.. changing the sheet name and ref to suit.

 

[ukkz001]

I received the following error:
"Formula in worksheet contians one or more invalid references"

Here is what I have listed in the refence cell:

="'Z:\Department\ACCTG\Statements\"&TEXT('Daily Rec'!$A$3,"yyyy")&"\"&TEXT('Daily Rec'!$A$3,"mmmm")&"\[Daily Report "&TEXT('Daily Rec'!$A$3,"mm dd.yyyy")&" xls (2).xlsx]Daily Cash Report'!$B$8:$K$500"

 

[GlennUK]

If you are just changing source workbook, you don't need to specify sheet and reference.

 

[ukkz001]

So the new code would be:

Sub ChangeMyLinks()
alinks = ThisWorkbook.LinkSources
ThisWorkbook.ChangeLink Name:=alinks(1), NewName:= _
Range("F13").Value, Type:=xlExcelLinks
End Sub

Where cell F13 containg the formula to the dynamicly updated link:

="'Z:\Department\ACCTG\Statements\"&TEXT('Daily Rec'!$A$3,"yyyy")&"\"&TEXT('Daily Rec'!$A$3,"mmmm")&"\[Daily Report "&TEXT('Daily Rec'!$A$3,"mm dd.yyyy")&" xls (2).xlsx]Daily Cash Report'!$B$8:$K$500"

 

[GlennUK]

Cell F13 formula:

="'Z:\Department\ACCTG\Statements\"&TEXT('Daily Rec'!$A$3,"yyyy")&"\"&TEXT('Daily Rec'!$A$3,"mmmm")&"\Daily Report "&TEXT('Daily Rec'!$A$3,"mm dd.yyyy")&" xls (2).xlsx"

 

[ukkz001]

Yeah, that won't work because the last part of the formula:
Daily Cash Report'!$B$8:$K$500" is the linked data that need to be updated.

The first part of the formula is the directory path and Daily Cash Report file name based on the date range found in the "Daily Rec" worksheet. The file is updated daily and the save name by date ex:

Thanks for your help Glenn.

 

[ukkz001]

forgot to include the ex: of the file name:

Participations Daily Report 03 14.2011 xls (2).xlsx