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...