Change link source on 1000 + excel workbooks

redekopmfg
redekopmfg used Ask the Experts™
on
We have over a thousand Excel workbooks that we have to move to a new server as part as an upgrade.  Each of these workbooks have vLookup links in them to another shared workbook.  

Now that these workbooks have moved, this link location is now wrong.  can a script be developed to open each of these workbooks and change the source of these links to the new location.  all the workbooks are in the same folder, and the source is the same in each as well.

current location is \\serverA\jobs\...... new location is \\serverB\jobs\.....

as a furher complication, this same link location is used inside a vba module in each of these workbooks.  it is not as important to update these, but it would be nice.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
please post a sample workbook

Author

Commented:
I was able to record a macro that updates the links, and I can run it every time I open one of the workbooks.  works good, but takes a long time.

What I really need is to get a script that traverses through all the folders opens the workbook then executes the macro when the workbook opens then save and close.

If someone can help with this, that would be great.  The structure is always the same, but the names are different.

the parent folder is called "Job Files"  then in there, there is a folder for each of the jobs...ie. "JF11234".  then within that folder, there is the excel workbook that is named in reference to its parent folder...ie. "11234Readme.xls"

is that doable?
Top Expert 2011

Commented:
Are the files .xls (up to XL 2003) or .xlsm (2007 and above)?  For the later format I have written a utility which can bulk update links very quickly.  It would not do the macro code though.

Author

Commented:
They are all .xls (2003) files! :(

We are using office 2010 for the most part though
Top Expert 2014

Commented:
* You can run VBA code in any office application to do the update.
* You can run VBScript code to do the update
* I wouldn't go the route of a workbook_open event.
* still waiting for you to post a workbook
Top Expert 2014

Commented:
@redekopmfg

please post a workbook

Author

Commented:
sorry for the delay........50,000 other things going on....... :)
11630---110819-Erick.zip
Top Expert 2014

Commented:
Now that I can see your workbook, I observe the following:
* There are two worksheets
* all the external references are on server "\\main"

=============
You need to change all the "\\main\" strings in all worksheets to some other server name in all .xls workbook files in a directory tree?
Top Expert 2014
Commented:
The following solution allows you to break up the work on different PCs or different CPUs on the same PC.

1. open a command prompt window
2. navigate to the top of the directory tree where your Excel files reside.
3. Issue the following command:
dir /b /s *.xls > ExcelFiles.txt

Open in new window


4. OPTIONAL: edit the ExcelFiles.txt file and create smaller files from subsets of the original file.

5. Add the following code to a new workbook
Public Sub ChangeLinks(parmMyList, parmFindWhat, parmReplaceWith)
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim oFS As Object       'New Scripting.FileSystemObject
    Dim oTS_In As Object    'Textstream
    Dim oTS_Out As Object   'TextStream
    Dim dicProcessed As Object  'New Scripting.Dictionary
    Dim strLine As String
    Dim boolChanged As Boolean
    Dim rngFind As Range
    
    Set oFS = CreateObject("scripting.filesystemobject")
    Set dicProcessed = CreateObject("scripting.dictionary")
    
    If oFS.FileExists(parmMyList) Then
    Else
        MsgBox "File of workbook names does not exist", vbCritical
        Exit Sub
    End If
    
    If oFS.FileExists(oFS.GetParentFolderName(parmMyList) & "\" & oFS.GetBaseName(parmMyList) & "_Done.txt") Then
        Set oTS_In = oFS.OpenTextFile(oFS.GetParentFolderName(parmMyList) & "\" & oFS.GetBaseName(parmMyList) & "_Done.txt", ForReading)
        Do Until oTS_In.AtEndOfStream
            strLine = oTS_In.ReadLine
            dicProcessed.Add strLine, 1
        Loop
        oTS_In.Close
    End If
    
    Set oTS_In = oFS.OpenTextFile(parmMyList, ForReading)
    Set oTS_Out = oFS.OpenTextFile(oFS.GetParentFolderName(parmMyList) & "\" & oFS.GetBaseName(parmMyList) & "_Done.txt", ForAppending, True)
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    Do Until oTS_In.AtEndOfStream
        strLine = oTS_In.ReadLine
        If dicProcessed.Exists(strLine) Then
        Else
            Application.StatusBar = "Processing: " & strLine
            boolChanged = False
            Set wkb = Application.Workbooks.Open(strLine, False)
            For Each wks In wkb.Worksheets
                Set rngFind = wks.UsedRange.Find(parmFindWhat)
                If rngFind Is Nothing Then
                Else
                    boolChanged = True
                    wks.UsedRange.Replace parmFindWhat, parmReplaceWith, xlPart
                End If
            Next
            wkb.Close boolChanged
            oTS_Out.WriteLine strLine
        End If
        strLine = oTS_In.ReadLine
    Loop
    
End Sub

Open in new window


6. In the Immediate Window, issue the following command:
ChangeLinks "C:\users\aikimark\excelfiles.txt", "\\main\","\\mainB\"

Open in new window

Where C:\users\aikimark\ is the top of your directory tree and the second and third parameters are the old and new server names.

Note: as each file is processed, it appends a new line to a related file to prevent duplicate processing, allow restarts, and improve performance.

You should backup your directory tree before you test this.

=============
If you breakup the original list of Excel files, you would follow similar subsequent steps on different PCs or use the START command to assign CPU affinity.

Author

Commented:
Thanks Aikimark!  along with what I had figured out, this got me there!
Top Expert 2014

Commented:
Did you do the server name code changes yourself?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial