How can I update ALOT of excel workbooks? 3

[BobSchleicher]

I am in the process of moving files from one server to another. There are 100's of excel files with formulas in them referencing the server name, so when I move the document to the new server the location needs to be updated.

Does anyone know of a way to change the formulas in the workbooks quickly?

Thanks,

BobSchleicher

 

[PHV]

If xl2k or above you may consider the Replace function against the FormulaR1C1 property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BobSchleicher]

My first thought was to Replace but I was hoping for something more productive than opening each workbook one at a time.

Thanks for the idea.

BobSchleicher

 

[PHV]

You hoped to change formulas without opening the workbooks ?

 

[AnotherHiggins]

Turn on the macro recorder for what to do within a given file.

To loop through all the files in a folder, wrap the resulting code in something like:

Set fs = Application.FileSearch
    With fs
    .LookIn = "C:\Your\Path\Here"
    .Filename = "*.xls"
        If .Execute > 0 Then
            For i = 1 To .FoundFiles.Count
                Workbooks.Open .FoundFiles(i)
                    'Your Code Here
            Next i
        End If
    End With
Set fs = Nothing

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Just posting to tie this thread to the same question in the Microsoft: Office forum.

see also thread68-1206951

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[CautionMP]

What is your folder structure for the links in the formulas? (i.e. are all the workbooks in one folder or are there numerous folders?).

Is ask because opening the workbooks and making the changes are pretty easy. Depending on how many folders we're looking at will determine if the mechanism to interate through the folders needs to be recursive or not.

CMP

 

[CautionMP]

Ok, here is a first attempt at putting all the pieces together. PLEASE NOTE: I have not tested this, so please start with 'dummy' data and workbooks and forgive any syntax errors.

Public Sub BatchUpdateWorkbookFormulaPaths()
Dim wkbToUpdate As Workbook
Dim wksToUpdate As Worksheet
Dim rngToUpdate As Range
Dim objFileSearch As FileSearch
Dim intCurrentFile As Integer

Set objFileSearch = Application.FileSearch

'Find the files in Directory 'YourDirectoryHere' in the
'format 'C:\Directory'
With objFileSearch
  .LookIn = "[i]YourDirectoryHere[/i]"
  .Filename = "*.xls"
  .SearchSubFolders = True
  If .Execute(SortBy:=msoSortByFileName, _
  SortOrder:=msoSortOrderAscending) = 0 Then
    MsgBox "There were no files found."
    Exit Sub
  End If
End With
For intCurrentFile = 1 To objFileSearch.FoundFiles.Count
  Set wkbToUpdate = Workbooks.Open(objFileSearch.FoundFiles(intCurrentFile))
  For Each wksToUpdate In wkbToUpdate.Worksheets
    'Need a range for the Replace method, grab A-Z
    Set rngToUpdate = wksToUpdate.Columns("A:Z")
    'Make the replacement, update both strings
    rngToUpdate.Replace "[i]YourOldPathHere[/i]", "[i]YourNewPathHere[/i]"
  Next wksToUpdate
  [b]'Might be a good idea to do a .SaveAs and move to the new
  'server location in the same step. This way you don't 'taint'
  'the original files if the code is wrong[/b]
  wkbToUpdate.Save
  wkbToUpdate.Close
Next intCurrentFile
CleanUp:
Set rngToUpdate = Nothing
Set wksToUpdate = Nothing
Set wkbToUpdate = Nothing
End Sub

You will notice that I made some assumptions in the coding, and because I think it's pretty important I will say this again:

Might be a good idea to do a .SaveAs and move to the new server location in the same step. This way you don't 'taint' the original files if the code is wrong

because it amazing how quickly a macro can screw up a couple hundred files before you know it.

Hope this helps,
CMP

 

[BobSchleicher]

These are some great ideas. I am going to start working with them. Thanks for the ideas, I'll keep everyone posted.

BobSchleicher

 

[BobSchleicher]

anotherhiggins your formula works great, but it didn't go to the next folder.

CautionMP your formula works great also, but it does go to sub folders.

Thanks for all the help.
Greatly appreciated.

BobSchleicher