I have a project that copies a series of worksheets from other files to a consolidation workbook. After the consolidation workbook is saved and closed, when it is reopened, the following message appears:
This workbook contains links to other data sources with Update / Don’t Update buttons.
This message is confusing to the users and if they attempt to do an Update, the source file may no longer be available. So this is what I did:
I determined that there are no links in formulas or in data verification. I realized that the source worksheets use various Shapes as buttons. They have a link to the macro. It appears in Excel 2010, when these worksheets are copied with the shapes with macros, the consolidation workbook creates a link to the source workbook to find the macro.
I was not able to find a way to stop this link from being created, so at the end of the vb code that copies that source file to the consolidation target, I put in a piece of code that does the following:
- Loops through each worksheet of the consolidation workbook
- Finds each shape in each of the worksheets
- Determines if the shape runs a macro
- If the shape runs a macro (the OnAction option is not null), remove the file reference and update the OnAction for the shape so it only references the local macro.
Here is the code:
Public Sub FixShapes() Dim ws As Worksheet Dim shp As Shape Dim iPosition As Integer Dim sNewOnAction As String For Each ws In Worksheets For Each shp In ws.Shapes If IsNull(shp.OnAction) Then Else If InStr(1, shp.OnAction, "!") > 0 Then iPosition = InStr(1, shp.OnAction, "!") + 1 sNewOnAction = Mid(shp.OnAction, iPosition) shp.OnAction = sNewOnAction Else End If End If Next shp Next ws End Sub