I did as suggest and encountered the €œAutomation Error€� as well.
Interesting enough, if I remove the chart objects in ChartSkel.xls then the
marco will process without incident.

It would be nice if I could attached the ChartSkel.xls to this post.

Your assistance in zeroing in on the line of code that I believe is the
culprit is greatly appreciated.

"Peter T" wrote:

I can only glance at your code but on the face of it there's nothing wrong.

oChartSkel.Sheets(x).Copy After:=oThisMgrChartBook.Sheets(1)

the above is in effect the same as

cht1.Copy after:=ws2

IOW, no problem with method (I assume you tried it?)

In your own code add a bit more to isiloate the problem

dim oFrom as object, oTo as Object

'in the loop
Set oFrom = oChartSkel.Sheets(x)
Set oTo = oThisMgrChartBook.Sheets(1)
debug.? oFrom.Name, oTo.Name

oFrom.Copy After:=oTo

Regards,
Peter T




"Joe" wrote in message
...
Peter T,

Thank you again. The copy/paste approach I took is similar to yours;
however, I created a majority of the code using the macro recorder; your
code
appears to be more efficient.

ChartSkel.xls (source) can be looked at as my chart template. This
workbook
has two tabs with charts and a data table/grid: one tab is for
"Workforce"
charting and the other is for charting "Earned Value". For each Cost
Account
that I process the macro will create two tabs within the workbook. For
example, at the end of my process I rename Book1.xls (target) to the
Project/Manager Name and within this workbook I will have the cost account
charts: Sheet1 has been renamed to "1.1 Workforce", Sheet2 is now "1.1
EarnedValue", Sheet3 is now "1.2 Workforce" and so on.

The code always fails on this line: oChartSkel.Sheets(x).Copy
After:=oThisMgrChartBook.Sheets(1)

I hate to say it but I think this might be a bug within Excel 2007. Maybe
you could confirm this by using the above line of code in your test macro?

Do
For x = 1 To oChartSkel.Sheets.Count
oChartSkel.Sheets(x).Copy After:=oThisMgrChartBook.Sheets(1)
' processing code omitted
Next
Loop Until rngTestRange.Row = lngLastRow

Whe
oChartsSkel = ChartSkel.xls
oThisMgrChartBook = Book1.xls

At this point I am going forward with coding around this one line. Yes, I
would be willing to try your ComAddin, however, I will be away for a
couple
of weeks starting Monday. Thank you once again.

Regards,
Joe

"Peter T" wrote:

If I follow, in effect you wan to duplicate a chart into a second
workbook,
right?

As you've also figured there are two approaches,
remake the chart from scratch
or copy the chart and "resource" to similar data (itself perhaps copied
from
the source wb) in the new workbook.

Personally I probably prefer to recreate a new chart, however copying is
certainly much easier and typically should work fine, assuming of course
you
know the location of the original data.

oChartSkel.Sheets(x).Copy After:=oThisMgrChartBook.Sheets(1)

not sure why,

I made simple chart with two series,
B1:C2: series1 & 2 names
A2:A4 x values
B2:C4 Y values for the two series
the data is on Sheet1

the original chart is in "Book1", in a chartsheet named "Chart1"

Following will copy the source data to "Sheet1" in "Book2" and the chart
to
a chart-sheet in Book2, linked to the newly pasted data in Book2!Sheet1

Sub CopyChartAndData()
Dim p1 As Long, p2 As Long
Dim sF As String, sRep As String, sSht As String
Dim rng1 As Range, rng2 As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim cht1 As Chart
Dim cht2 As Chart
Dim sr As Series

Set ws1 = Workbooks("Book1").Worksheets("Sheet1")
Set rng1 = ws1.Range("A1:C4")

Set ws2 = Workbooks("Book2").Worksheets("Sheet1")
Set rng2 = ws2.Range("A1:C4")

rng1.Copy rng2

Set cht1 = Workbooks("Book1").Charts("Chart1")

cht1.Copy after:=ws2

Set cht2 = Workbooks("Book2").Sheets(ws2.Index + 1)

sSht = "'" & ws2.Name & "'"

For Each sr In cht2.SeriesCollection
sF = sr.Formula
p1 = InStr(9, sF, "[")
p2 = InStr(p1, sF, "!")
sRep = Mid$(sF, p1, p2 - p1)
sF = Replace(sF, sRep, sSht)
sr.Formula = sF
Next

End Sub


I suspect your problem with the copy sheet is just some simple error. But
did you isolate a particular aspect of code that errors in SP2 but works
in
other versions.

FWIW, I have quite an extensive ComAddin that removes all links in charts
to
cells (data goes to named arrays or arrays in formulas). It can also dump
source data to a new range cells and optionally relink the dumped data
(eg
copy chart to new wb, dump source data to new wb, resource to the dumped
data). It worked very well in earlier versions but there are one or two
things that have been problematic in Excel 2007 (waiting for a rainy day
to
update). Not sure if it would be of any use to you but let me know if
interested to try the beta.

Regards,
Peter T

"Joe" wrote in message
...
Peter T,

Thank you for the suggestion. In creating a smaller version of the
macro
I
uncovered the problem I am having with the command:
oChartSkel.Sheets(x).Copy After:=oThisMgrChartBook.Sheets(1)

oChartSkel is a spreadsheet (actual name is ChartSkel.xls) that contain
line
chart objects (one chart on one worksheet and two on the other). When
I
remove the line charts from the spreadsheet and re-run the macro it
completes
without incident. I've tried recreating the chart objects using Excel
2007
to no avail. So, in the meantime what used to take one line of code is
now
being replaced with 57 lines. :( (Long story short over the 57 lines
is
this: I select the range of cells to copy/paste over to my new
workbook.
Then I copy/paste over the Chart object and update the data-series. I
guess
it is a good thing there are only two worksheets with a total of three
charts). Do you know of a way to get around all of this chart
creation/manipulation?

Regards,
Joe

"Peter T" wrote:

Could you post a simplified routine to trigger the error that others
can
reproduce. Alternatively post a "test" routine with all the necessary
variables to call "GenerateCharts".

Also state if the problem occurs in SP1 or SP2 or both

Regards,
Peter T

"Joe" wrote in message
...
I am receiving the following error during macro execution. The error
occurs
only when the macro is ran through Excel 2007. Asking the users to
rollback
to Excel 2003 will not be an option soon. Thanks.

Regards,
Joe


ERROR MESSAGE:
Run-time error '-2147417847 (80010108)':

Automation error
The object invoked has disconnected from its clients.


REFERENCE LIBRARIES BEING USED AT RUNTIME:
Visual Basic For Application
Microsoft Excel 12.0 Object Library
Microsfot Office 12.0 Object Library
OLE Automation


LINE OF CODE CAUSING THE ERROR:
oChartSkel.Sheets(x).Copy After:=oThisMgrChartBook.Sheets(1)


MACRO CODE:
Sub GenerateCharts(Optional oMacroParams As Object = Nothing)
Dim oSheet As Worksheet, lngLastRow As Long, rngTestRange As Range,
oChartSkel As Workbook, oThisMgrChartBook As Workbook
Dim oCurrentChartSheet As Worksheet, oMgrNotebook As Workbook,
iStatusDateCol

'On Error Resume Next

Set oMgrNotebook = ThisWorkbook

If oMacroParams Is Nothing Then Exit Sub

'make sure chartgen sheet exists
If Not SheetExists(CHARTGEN_SHEETNAME) Then Exit Sub

'set a reference to chartgen sheet
Set oSheet = ThisWorkbook.Sheets(CHARTGEN_SHEETNAME)
If oSheet Is Nothing Then Exit Sub

'find the last row
lngLastRow = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

'if there is no data past the title block then bail out
If lngLastRow < 7 Then Exit Sub

'open chart skeleton workbook
Set oChartSkel = Workbooks.Open(Filename:=oMacroParams.ChartSkel,
ReadOnly:=True)
If oChartSkel Is Nothing Then Exit Sub

'create a new workbook to put the charts in
Set oThisMgrChartBook = Workbooks.Add

'loop through chartgen sheet and make charts for each account
Set rngTestRange = oSheet.Cells(6, 1)
Do
Set rngTestRange = rngTestRange.Offset(1, 0)
'check for start of new cost account
If Not IsEmpty(rngTestRange.Value) Then
sAccount = Left$(Mid(rngTestRange, 8, InStr(1,
rngTestRange,
"
") - 8), 31)
For x = 1 To oChartSkel.Sheets.Count

'copy chart sheets from skel to this cam's chart
notebook
'execution of the following line cause the Automation
Error
oChartSkel.Sheets(x).Copy
After:=oThisMgrChartBook.Sheets(1)


Set oCurrentChartSheet =
oThisMgrChartBook.ActiveSheet
sSourceChartName = ActiveSheet.Name

With oCurrentChartSheet
.Name = sAccount & "_" &
oChartSkel.Sheets(x).Name
.Range("A49") = oMacroParams.ProgramDescription