Dynamically Change Linked Spreadsheet Location

Question

Help!

I need to dynamically change the location of a linked spreadsheet.  I have MANY cells in MANY sheets within my workbook that are linked to another workbook.  The location of the other workbook will change based on the month we are in.  Example: 2010-01-01Fin.xls  2010-02-01Fin.xls and so on. 

What i need to happen is for the address of the file (which i have entered into a cell) will change all of the links for that workbook.

ie: in my cell (a1) i will programatically build the path to the file including filename (\\server\share\2010-01-01Fin.xls and so on) and i need all the linked cells to look at a1 and link to certain cells in that file.

Thanks so much for your help!

Answer 1

Under Edit - Links, there's a button to "Change source". Allows you to change the source for any linked workbook(s) you have. If you record a macro of this, you can get the coding structure. A basic layout would like this:

Sub ChangeAddress()

Dim NewName As String

Dim CurrentName as String

NewName = ActiveSheet.Range("A1").Value

CurrentName = ActiveSheet.Range("A2").Value 'Develop some way of building this

ActiveWorkbook.ChangeLink Name:=CurrentName, NewName:= _

NewName, Type:=xlExcelLinks

End Sub

Answer 2

Luke - Nice!!!!

This is what I ended up with - it works since i only have one linked sheet - if i had more, i would use your idea of building out the one i wanted to change based on a sample linked cell....  THANK YOU SO MUCH!

    Dim VariableNewName As String

    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

    VariableNewName = "http://wss/sites/a/b/c/d/Financials/2010-10-29_ProjectFinancials.xls"

    ActiveWorkbook.ChangeLink Name:=aLinks(1), NewName:=VariableNewName, Type:=xlExcelLinks