VBA: Copying charts and simultaneously breaking links.

dkabambe · Dec 12th 2003

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

Anonymous · Dec 12th 2003

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?).

VBA: Copying charts and simultaneously breaking links.

Participate now!

Share