The Total Cost Model has had its yearly look over. There have been some minor cosmetic changes, such as copyright dates, but the main focus was to stop the “Update External Links” message from appearing. Although such data links can be very useful, we don’t want any external links connected to this workbook. Microsoft explains External Links on this page.
The Total Cost Model, and, the BidSmart Estimator both suffered from the following warning message being shown when the workbook was opened.
This message just adds more apprehension to the user who has no knowledge of this feature of Excel, or those who have been frightened by Microsoft’s warnings about running macros or opening files downloaded from the internet. If you are the author of the workbook or you know its origin this should not be an issue. However, when you download a macro-enabled workbook from a website such as this, you should always be cautious.
This is why I added the new Sub routine Break_External_Links() to the workbook. The sub is called when the workbook opens and will “break” any external links that exist. The code for this procedure follows (Use the scrollbar at the bottom to scroll to the right) :
Public Sub Break_External_Links()
' A sub to break all external links
' -->add to the end of a successful import and repair subs
Dim ExternalLinks As Variant
Dim WrkBk As Excel.Workbook
Dim i As Long
Dim bolProtected As Boolean
On Error GoTo errHandler
Set WrkBk = ActiveWorkbook
'check for and unprotect the workbook if it is protected
If ActiveWorkbook.ProtectWindows Or ActiveWorkbook.ProtectStructure Then
bolProtected = True
Call UnProtect_Me(ResetRibbon:=False)
End If
If Not IsEmpty(WrkBk.LinkSources(Type:=xlLinkTypeExcelLinks)) Then
ExternalLinks = WrkBk.LinkSources(Type:=xlLinkTypeExcelLinks)
Else
GoTo cleanup
End If
For i = 1 To UBound(ExternalLinks)
WrkBk.BreakLink Name:=ExternalLinks(i), Type:=xlLinkTypeExcelLinks
Next i
MsgBox "All External Data Links have been removed.", vbOKOnly, "External links removed"
GoTo cleanup
cleanup:
'Reprotect if needed...
If bolProtected = True Then
Call Protect_Me(ResetRibbon:=False)
End If
Exit Sub
errHandler:
If Err.Number > 0 Then
If MsgBox("An Error occurred while attempting to Break External Data Links in Break_External_Links()", "Error description: " & Err.Description & vbCrLf & vbCrLf _
& "Click Cancel to ABORT, otherwise...Resuming next...", vbOKCancel, "Error number: " & Err.Number) = vbCancel Then
Resume cleanup
Else
Resume Next
End If
Else
Resume Next
End If
You will find a call to this procedure in the Workbook_Open() event:
If Not IsEmpty(ThisWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)) Then
Call Break_External_Links
End If
That’s really all that was needed this time. To download a new copy of the Total Cost Model visit its homepage.
The BidSmart Estimator will include this fix and hundreds of others, and many new features in the upcoming version 2.5 release.