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]

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.

According to the logic that you have been using the Sheet name and range remains the same regardless of the workbook name. In which case my method would work.

But you are saying that it won't work. Please explain.

 

[ukkz001]

Correct, the source file name changes based on date, but the worksheet and data range remains the same.
When I replaced the formula with what you suggested:
="'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"

How does it know which worksheet and data range to use when that info has been eliminated? I'm guessing it is able to find the correcr file source but then does not know
what data to update the links with.

Hope that makes sense.

 

[GlennUK]

Excel will try to use the same sheet names as for the current link. Ranges are stored as part of cell formulas. Changing the link tries to simply change the workbook referenced.

 

[ukkz001]

That makes sense, but when I test it I receive:

Method 'ChangeLink' of object '_Workbook' failed

I'm using the code you suggested:

alinks = ThisWorkbook.LinkSources
ThisWorkbook.ChangeLink Name:=alinks(1), NewName:= _
Worksheets("Lookup Tables").Range("F13").Value, Type:=xlExcelLinks

 

[GlennUK]

It sounds like the file that's in sheet "Lookup Tables" Range "F13" does not exist. Did you check that?

 

[ukkz001]

I was able to verify that it does exist, and was able to the Edit Links to open and check the source.

Any ideas??

 

[GlennUK]

What happens if you write a one line macro of:

Sub OpenTest
Workbooks.Open(Worksheets("Lookup Tables").Range("F13").Value)
End Sub

?

 

[ukkz001]

OH, that was insightful. I had a [ in there that should not have been. Sorry for the goose chase. Thanks for your expertise.