theta
08-28-2012, 09:11 AM
I received an automation error when trying to unlink a chart in Excel 2007.
A sheet/charts were copied to a new workbook and the links are to be broken. Part of this involves the following code :
Sub Unlink2007Charts()
Dim chrtObj As ChartObject
Dim sc As SeriesCollection, sr As Series, _
result As Range, temp As String, i As Integer, _
ar() As String, j As Integer
ActiveWorkbook.Unprotect
ActiveWorkbook.Sheets("Sheet1").Unprotect
For Each chrtObj In ActiveWorkbook.Sheets("Sheet1").ChartObjects
Set sc = chrtObj.Chart.SeriesCollection
' For each of the series
For i = 1 To sc.Count
sc(i).Values = sc(i).Values
sc(i).XValues = sc(i).XValues 'F9 to debug
Next
Next
End Sub
Although a chart may generate with an invalid XValues range (becuase the cells are empty), when I try to unlink using the above code it produces a debug on the XValues = XValues line.
Trying to run commands in the immediate window produces the runtime error.
?ubound(sc(i).Values) 'WORKS
10
?ubound(sc(i).XValues) 'PRODUCES ERROR
I tried to test an error catch by wrapping is in ISERROR() and it still produces the runtime error.
How can I catch / account for this error? Even trying to find ISERROR or UBOUND results in the debug...
A sheet/charts were copied to a new workbook and the links are to be broken. Part of this involves the following code :
Sub Unlink2007Charts()
Dim chrtObj As ChartObject
Dim sc As SeriesCollection, sr As Series, _
result As Range, temp As String, i As Integer, _
ar() As String, j As Integer
ActiveWorkbook.Unprotect
ActiveWorkbook.Sheets("Sheet1").Unprotect
For Each chrtObj In ActiveWorkbook.Sheets("Sheet1").ChartObjects
Set sc = chrtObj.Chart.SeriesCollection
' For each of the series
For i = 1 To sc.Count
sc(i).Values = sc(i).Values
sc(i).XValues = sc(i).XValues 'F9 to debug
Next
Next
End Sub
Although a chart may generate with an invalid XValues range (becuase the cells are empty), when I try to unlink using the above code it produces a debug on the XValues = XValues line.
Trying to run commands in the immediate window produces the runtime error.
?ubound(sc(i).Values) 'WORKS
10
?ubound(sc(i).XValues) 'PRODUCES ERROR
I tried to test an error catch by wrapping is in ISERROR() and it still produces the runtime error.
How can I catch / account for this error? Even trying to find ISERROR or UBOUND results in the debug...