Formula linking to new merged workbook.

[brems]

Hi,

I'm using the following code to merge all worksheets of the two workbooks to one new workbook.

The formulas in the new document still link to the previous (unmerged) documents.
Is there a way this can be solved by adding some code?

The formula links must link to the data in the new (merged) document. I've been browsing the forum but I didn't find a solution for this.

Thanks in advance for your assistance!

Best regards,
Wim

Sub CombineFiles()
   
    Dim Path            As String
    Dim FileName        As String
    Dim Wkb            As Workbook
    Dim Ws              As Worksheet
   
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Path = "I:\Kader\Rapporteringen\Coachingsdocumenten\samenvoegen"
    FileName = Dir(Path & "\*.xls", vbNormal)
    Do Until FileName = ""
        Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
        For Each Ws In Wkb.Worksheets
            Ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Next Ws
        Wkb.Close False
        FileName = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
End Sub

 

[NARAYANK991]

Hi Wim ,

Can you go through this link , and try the Changelink method ?

http://www.mrexcel.com/forum/excel-...ng-sheets-another-workbook-without-links.html

Narayan

 

[brems]

Hi Narayan,

Looks fine to me!

I tried the code below with the two workbooks open but I got an error message 1004.

ActiveWorkbook.ChangeLink Name:="I:\Kader\Rapporteringen\Coachingsdocumenten\coachingcijfers db.xlsx", _
NewName:="I:\Kader\Rapporteringen\Coachingsdocumenten\test.xlsx", Type:=xlExcelLinks

Did I write (copy) the code correct? I specified the location of the new and the old file.
The 2 workbooks are identical but in the new workbooks all the links link to the old workbook.

Thank you for your assisantance.