Substitute Excel-Workbook reference for another I have an Excel-Workbook, where we have an sheet where we consolidate data. Basically we link to other Excel-Workbooks. I wrote the following Macro, in order to update the path and filename. We cannot just update the whole link, since other parts need to reference the old path (and filename).

Sub neueDatei()

Dim Pfad As String, DateiName As String, DateiName_alt As String, h As String, formel As String
Dim tblRow As Long, test As Long
Dim wb As Workbook
Dim IVsh As Worksheet

Application.ScreenUpdating = False

Set wb = ActiveWorkbook

Set IVsh = wb.Sheets("Input Vektor")

With IVsh
    Pfad = .Range("C3").Value 'set the path
    DateiName = .Range("C4").Value 'set the filename
    tblRow = .Cells(.Rows.Count, 1).End(xlUp).Row 'find last row
End With

If Mid(Pfad, Len(Pfad) - 1, 1) &lt;&gt; "\" Then
    Pfad = Pfad &amp; "\" 'append \ if necessary
End If

If Mid(Pfad, 1, 1) &lt;&gt; "'" Then
    Pfad = "'" &amp; Pfad 'excel needs a quote at the start of the path.
End If

DateiName_alt = DateiName

'add brackets to the filename
If Mid(DateiName, 1, 1) &lt;&gt; "[" Then
    DateiName = "[" &amp; DateiName 
    DateiName = DateiName &amp; "]"
End If

'substitute the filenames
For i = 19 To tblRow
    If IVsh.Cells(i, 7).Value &lt;&gt; "" Then
        Exit For
    End If
    h = IVsh.Cells(i, 2).Formula
    If InStr(1, h, "[") &gt; 0 Then
        IVsh.Cells(i, 2).Formula = "=" &amp; Pfad &amp; DateiName &amp; Mid(h, InStr(1, h, "]") + 1)
    Else
        IVsh.Cells(i, 2).Formula = "=" &amp; Pfad &amp; DateiName_alt &amp; Mid(h, InStr(1, h, "xlsx") + 4)
    End If
Next i

Application.ScreenUpdating = True

End Sub


The problem is, that the code runs relatively slow (a few minutes for only 2-3 thousand lines). Any suggestions, how I can speed this up?
Try adding Application.Calculation = xlManual where you turn updating off, and then turn it back to xlAutomatic at the end.

In addition, turn events off (and then on again): Application.EnableEvents = False and Application.EnableEvents = True.

I suspect that everytime you change a file name in a cell, the workbook tries to access the link and update the cell. 

Small point - doesn't affect speed:

If Mid(DateiName, 1, 1) &lt;&gt; "[" Then
    DateiName = "[" &amp; DateiName 
    DateiName = DateiName &amp; "]"
End If


could be

If Mid(DateiName, 1, 1) &lt;&gt; "[" Then
    DateiName = "[" &amp; DateiName &amp; "]"
End If


Instead of Set wb = ActiveWorkbook, you could use ThisWorkbook - it is a little more assertive if that is what you need. I don't have the context on how this function is used, so it may be called when another workbook is active.