Workbook Not Saving

[fredlo2010]

Hello,

I have a little bit of an issue. I have a huge workbook 26 mb and its tuck in the saving part. If I save it while debugging and then interrupt the code it will save with no issues, but not by itself.

I have some code in the before save event of the workbook.

This is the code I have in the before save event. And its whats causing me issues because it becomes non-responsive in some of the sheets. I placed a small buffer with doEvents at the bottom to try to deal with this because its not doing anything.

Public Sub RemoveExternalLinks(ByRef theWorksheet As Worksheet)

    Dim rngCell As Range
    Dim intStartLink As Integer, intEndLink As Integer
    Dim rngFormulaCells As Range
    Dim lTotalCells As Long
    Dim i As Long
    
    Const cExternalLinkStart = "["
    Const cExternalLinkEnd = "]"
    
    On Error Resume Next
    
    DoEvents
    Set rngFormulaCells = theWorksheet.Cells.SpecialCells(xlFormulas)
    DoEvents
    
    i = 1
    lTotalCells = rngFormulaCells.Cells.Count
    
    For Each rngCell In rngFormulaCells
        
        ' Inform the user
        Application.StatusBar = "Removing links from " & theWorksheet.Name & " .......... " & Format$((i / lTotalCells), "0.00%")
        
        'Initialize the values
        intStartLink = 0
        intEndLink = 0
            
        intStartLink = InStr(rngCell.Formula, cExternalLinkStart)
        intEndLink = InStr(rngCell.Formula, cExternalLinkEnd)
        
        'If there is no ] character, then intEndLink stays at zero, and there is no link
        'If there is a link, paste in the value that's in the cell and move to the next one
        If intStartLink < intEndLink Then rngCell.Value = rngCell.Value
        
        i = i + 1
    Next rngCell


    ' Small Buffer for Hard to get Sheets
    If theWorksheet.Name = "MY HARD SHEET" Then
        For i = 1 To 10000
            Application.StatusBar = "Removing links from " & theWorksheet.Name & _
                                    " .......... 100% Almost there .......... " & _
                                    Format$((i / 10000), "0.00%")
            DoEvents
        Next i
    End If


End Sub

Now that I am working on this I wonder if there is a better way to remove links. Because if you remove a link from the links panel it tells you that the formulas will be replaced with values( which is what I want) If thats possible that would be a better solution to refactor this long loopy code.


I am wondering if anyone has seen this before or have any input to help.

Thanks a lot in advance.

[fredlo2010]

This code does the job of breaking links.

Option Explicit

Sub BreakLinks()
    
    Dim arrLinks As Variant
    Dim i As Long
    
    arrLinks = ActiveWorkbook.LinkSources(Type:=xlExcelLinks)
    
    For i = LBound(arrLinks) To UBound(arrLinks)
        ActiveWorkbook.BreakLink Name:=arrLinks(i), Type:=xlLinkTypeExcelLinks
    Next i
    
    
End Sub

[mancubus]

hi.

am i missing something?

this will convert formulas into values:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    With Worksheets("Sheet1").UsedRange
        .Value = .Value
    End With
End Sub

[fredlo2010]

Hi mancabus,

I do not want all formulas to be values; only the ones that have external links.

The code from post #2 seems to be doing the job.

Thanks for the help.

[mancubus]

you are welcome.
ok then...