fredlo2010
09-28-2014, 05:05 PM
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.
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.