Copying Worksheets to Other Workbooks – Shapes causing unwanted links

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



Advertisements
This entry was posted in Excel Tips, VBA Code. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s