Update Link into different files

[neeraj2050]

Greetings!!

I have say 10 number of excel files in a folder. There is a file through which data is updated into all these 10 files.

I have to open all the 10 files one by one and have to update link individually

Is there any way to update link into all the 10 files at once

Further to that is it also possible to make those files paste special later on without opening each and every file

Thanks in advance!!

Regards
Neeraj Kumar Agarwal

 

[Thangavel]

Hello,
Try this code:

Const OldPath = "C:\NewPath\"
Const NewPath = "C:\Old Path\"

Sub UpdateFormulas()
    Dim FormulaCells As Range, Cell As Range
    Dim strFormula As String
    Dim curFormula As String
   
'  Create a Range object for all formula cells
    On Error Resume Next
    Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
   
'  Exit if no formulas are found
    If FormulaCells Is Nothing Then
        MsgBox "No Formulas."
        Exit Sub
    End If
   
   
'  Process each formula
    For Each Cell In FormulaCells
            curFormula = Cell.Formula
            If InStr(1, curFormula, OldPath) > 0 Then
              strFormula = Replace(curFormula, OldPath, NewPath)
              Cell.Formula = strFormula
            End If
    Next Cell
 
End Sub

 

[neeraj2050]

@Thangavel: Thanks for your time. I shall give it a try. But I would appreciate if any Non VBA method could be suggested

 

[neeraj2050]

May i request for any Non VBA solution