choose which file to link to?

[eye_in_the_sky]

It seems like this should be possible, just not sure how to pull it off...

I have a workbook that is linked to multiple other workbooks but only one at a time. I have formulas all over the place pulling data from a file called bravo01.xlsx. I want to be able to change all of the formulas at once to pull info from the from the same cells but in bravo02.xlsx, or bravo03, or bravo04, etc...

Is there a way to do this with a data validation list where the user selects a file from the drop down which in turn changes all of the formulas to link to the new file chosen? I'm open to anything.

Thanks!

 

[xenou]

Hi,
You can write a VBA routine for that. Sample code here (I'll let you take care of the validation list part, unless you need help with that do):

[COLOR="Navy"]Sub[/COLOR] TestIt()
[COLOR="Navy"]Dim[/COLOR] newLink [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    newLink = "C:\myTemp\Book22.xlsx"
    [COLOR="Navy"]Call[/COLOR] Change_Links(ThisWorkbook, newLink)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] Change_Links(ByRef wb [COLOR="Navy"]As[/COLOR] Workbook, newLink [COLOR="Navy"]As[/COLOR] String)
    [COLOR="Navy"]Dim[/COLOR] arLinks [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] intIndex [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    arLinks = wb.LinkSources(xlExcelLinks)
        
        Application.DisplayAlerts = False
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] IsEmpty(arLinks) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] intIndex = LBound(arLinks) [COLOR="Navy"]To[/COLOR] UBound(arLinks)
                wb.ChangeLink Name:=arLinks(intIndex), newName:=newLink, Type:=xlExcelLinks
            [COLOR="Navy"]Next[/COLOR] intIndex
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] 0
        Application.DisplayAlerts = True

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

For what it's worth you can change the link without VBA by using the Edit Links dialog.


**Note that this code assumes that there is only ONE linked workbook and all links are changed to the new link you provide. It will go haywire if you add a second linked workbook - in that case, you'd have to resolve which links to change and how to change them to the correct new source.

 

[eye_in_the_sky]

Thanks for the reply. I'm never used VBA so that might as well have been in Portuguese!! I was hoping there was a way to do this with formulas but the more reading plus trial and error I do, the bleaker things are looking. I guess I need to figure out VBA!

Is there a way to list a formula as text in a cell and then refer to that cell from another cell with an if,then or conditional formatting?

 

[xenou]

Okay, well you can use the Edit Links dialog as your "listbox" and do change the sources for links right there by clicking Change on the link you want to change. But you would need to browse to the file you want to change it to as the way to select the new source.

As for listing a formula as text in a cell, no no no. You can put the formula in the same cell as your IF/Then:

=IF([condition],A1+B1,C1+D1)

In the above formula, A1+B1 is one "formula" and C1+D1 is another.