Excel 2007 : How to automatically change the file a cell links to?

[jaymanre]

Hey everyone,

I'm using Excel 2007 and I'm trying to make things a bit easier on my coworkers (and really I'm just an intern, so I'm still new to Office 07).

We have a file I'll call "Expected Emergence 2008 4.xlsm" with a lot of cells that refer to other files, ie:
='O:\2008 4 LRC Studies\[AMC Petroleum 2008 4.xlsm]9 AL Net'!D54
Among other files. The one thing the file names have in common, however, is the "2008 4".

What I'd like to know is if there is a way to automatically change all of those references to our "2009 1" files instead of our "2008 4" files. I would want this to happen when a certain cell in the "Expected Emergence 2009 1" file is changed from "2008 4" to "2009 1". Is there a way to make this work? Thanks in advance, and sorry if my description is no good.

[shg]

Edit > Find & Replace, Replace 'O:\2008 4 with 'O:\2009 1, look in Formulas

[jaymanre]

Thanks for the reply shg.

Sorry I didn't elaborate enough. There are some references to "2008 4" that I do not want to change. I realize find and replace would work for the others, but there are a ton of references in the file to the "2008 4" group of files, so manually choosing which ones to change would be a pain. But thanks.

Any other suggestions?

[JBeaucaire]

Search/Replace can be run on a large selection, too. Perhaps it is all the references in one column?

[jaymanre]

JBeaucaire,

Three columns need to be changed per sheet, but there are many sheets. But I suppose that would be the way I should do it if there's no easier way. Thanks
Otherwise I was just going to use the "edit links" function in the data tab (or whatever it's called).

But really I'm just trying to convert 15 minutes of file updating to 5 seconds, if at all possible. If there isn't any way to do that it wouldn't be a huge deal...I just didn't have much to do at work yesterday, so I thought of doing something about this issue.

[JBeaucaire]

If it's the SAME columns that need the search/replace done, then record a macro of you doing it on one page. Then insert the "guts" of that macro into this code and it would then run the same code on every sheet in a workbook.

Sub SearchReplaceSpecific()
Dim ws as Worksheet

    For each ws in Worksheets
        ws.Activate
        'your search/replace code here
    Next ws

End Sub

[jaymanre]

They are the same columns for each sheet. I'll check that out on Monday then (don't have 2007 myself), but it looks like it would work well (although I've never used a macro...). Thanks a lot!

[martindwilson]

what about
=INDIRECT("'E:\["&C1&".xls]martin'!$A$1") where c1 holds the workbook name
note other workbook needs to be open for link to update
or better still with the morefunc addin installed and activated
(get it from here http://xcell05.free.fr/morefunc/english/)
=INDIRECT.EXT("'E:\["&C1&".xls]martin'!$A$1")

[jaymanre]

martindwilson,
Well, the INDIRECT formula did exactly what I wanted, except for needing the other workbook open. Is there any way to use this method without having that problem?

JBeaucaire,
For some reason, this site wouldn't open anymore after I tried the INDIRECT method, so I wasn't able to try the macro Maybe tomorrow.

[JBeaucaire]

After you've recorded you doing the search/replace once yourself, post the resulting code here and we'll help you merge that properly into the macro I suggested. Recorded code always can use with some streamlining and cleaning up.

[jaymanre]

Ok, I ran the macro, and here's the code I got (along with yours JB)

Sub SearchReplaceSpecific()
Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Activate
            Sub MacroTest()
            '
            ' MacroTest Macro
            ' Attempting to convert 2008 3 files to 2008 4.
            '
            
            '
                Range("B5:B34").Select
                Selection.Replace What:="2008 3", Replacement:="2008 4", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
            End Sub
            Next ws
                    
End Sub

It's been a while since I've coded anything (and it was really basic stuff anyway), so I know something's up with it.
I'm getting two errors:

Expected End Sub, apparently for ws
and
For control variable already in use, at For Each ws In

Any tips?

[JBeaucaire]

Yea, I said paste in the "guts" of the macro, and you pasted in the whole thing, including the header/footer. Oops.

Sub SearchReplaceSpecific()
Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Activate
        Range("B5:B34").Replace What:="2008 3", Replacement:="2008 4", _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
    Next ws
                    
End Sub

[jaymanre]

Ah, yeah that makes more sense. And it worked perfectly. Thanks for all the help!