VBA: Copying charts and simultaneously breaking links.

  • I have a spreadsheet project which produces various reports. I have a macro which copies the active report into a new book, simultaneously hard-coding any numbers from external sources whilst keeping formulae intact. The purpose of this is so I can email the reports without the (very large) underlying data files. This is the code I have so far:

    Code
    ActiveWorkbook.ActiveSheet.Copy    'NB: Problem when cells have more than 255 characters
    ActiveWorkbook.ActiveSheet.Unprotect
    If Not IsEmpty(ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)) Then
        For Each linkName In ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
            ActiveWorkbook.BreakLink Name:=linkName, Type:=xlLinkTypeExcelLinks
        Next
    End If
    ActiveSheet.Protect


    This works fine for the spreadsheet reports, and the charts where the series are hard linked to specific ranges. However, some of the charts use dynamic ranges and it is when exporting these the macro trips up. It converts the ranges in the series formulae to (non-existent!) equivalent ranges in the newly created workbook, as opposed to hard-coding the data values into the series formulae. Anyone any ideas how to accomplish this?


    Thanks in advance, Dzinja

  • Try setting up a macro that loops through all your charts, and for each chart through all of the seriescollections. For each seriescollection have lines like
    With seriescollection(i)
    x=.xvalues
    .xvalues=x
    x=.values
    .values=x
    end with


    where x is a variant. It should result in hard coded values for the charts. Warning: if there are too many data points in a series, the method will fail as the data values are stored as a long string and there is a limit to the number of characters it can hold (1024?).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!