Removing External File References - Suppressing File Open Dialog

[Snowman58]

I am working with a client who has a habit of copying formulas from a old version of a SS to a new one and in the process creating problems with external references.

I have written a utility to step through the formulas and remove all the external references. However when I reassign the corrected string to the formula, Excel pop's up the File Open Dialog. I have tried to suppress this by shutting off re-calc, events, etc, to no avail. Anyone know how to suppress the File Open Dialog?

Sub RemoveExtRef()    
    Dim sht As Worksheet
    Dim i, m, NumOfRef, RefCounter As Integer
    Dim sPwd, FormulaStr As String
    Dim cell As Range
    Dim n As Long
    Dim Protected As Boolean

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Application.AskToUpdateLinks = False

    On Error Resume Next
    ' Need to provide Password if sheets are protected
    sPwd = "xxxxxxx"

    'Loop through all sheets in the workbook - make it visible and unprotected for editing
    i = 0 ' Counter for references removed
    For Each sht In Worksheets

        With sht
            ' make sht active and unprotected
            .Activate
            If Not .Visible = 1 Then
                .Visible = 1
            End If ' .Visible
            If .ProtectContents = True Then
                Protected = True
                .Unprotect Password:=sPwd
            Else
                Protected = False
            End If ' .ProtectContents

            ' Remove external reference
            For Each cell In .Cells.SpecialCells(xlFormulas)
                FormulaStr = cell.Formula
                NumOfRef = (Len(FormulaStr) - Len(Replace(LCase(FormulaStr), LCase("]"), ""))) / Len("]")
                If NumOfRef > 0 Then
                    For RefCounter = 1 To NumOfRef
                        ' search backwards from end of string
                        n = InStrRev(cell.Formula, "]", Len(FormulaStr), vbTextCompare)
                        m = InStrRev(cell.Formula, "'", n, vbTextCompare)
                        FormulaStr = Left(FormulaStr, m) & Mid(FormulaStr, n + 1, Len(FormulaStr) - n)
                    Next RefCounter
                    cell.Formula = FormulaStr
                    i = i + 1
                End If ' NumOfRef
            Next cell
 
            If Protected Then ' If it was protected, set it back to protected
                .Unprotect Password:=sPwd
            End If ' Protected
        End With

    On Error GoTo 0
    Next sht

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.AskToUpdateLinks = True
    Application.Calculation = xlAutomatic

    MsgBox "Done, Removed " & i & " External References. "

End Sub

 

[mole999]

it wouldn't be this kicking in at the last moment would it

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.AskToUpdateLinks = True
Application.Calculation = xlAutomatic

so how about

with Application
.AskToUpdateLinks = True
.EnableEvents = True
.Calculation = xlAutomatic
.ScreenUpdating = True
end with

 

[Snowman58]

it wouldn't be this kicking in at the last moment would it

Don't think that's the problem - the File Open pops up each time cell.Formula = FormulaStr is executed.

Just to check, I did try your code and it made no difference.

Thanks!