Excel 2007 -- Automation Error

[Joe]

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
.Range("A50") = oMacroParams.StatusDateLabel
.Range("A51") = rngTestRange
End With

'moving to variable calendar range, so copy labels to charts
oSheet.Range("E6:R6").Copy
oCurrentChartSheet.Range("B51").PasteSpecial xlPasteValues

'copy from source to this chart's data
With oSheet
.Range(.Cells(rngTestRange.Row + 1, 5),
..Cells(rngTestRange.Row + 16, 20)).Copy
End With
'oSheet.Range("E" & rngTestRange.Row + 1 & ":T" &
rngTestRange.Row + 16).Copy
oCurrentChartSheet.Range("B52").PasteSpecial xlPasteValues

'update acwp, bcwp series
iStatusDateCol = GetStatusDateCol(oCurrentChartSheet)
Select Case sSourceChartName
Case "EarnedValue"
With oCurrentChartSheet.ChartObjects("Chart 1").Chart
.SeriesCollection(2).Values = "='" &
oCurrentChartSheet.Name & "'!R31C3:R31C" & iStatusDateCol & ""
.SeriesCollection(3).Values = "='" &
oCurrentChartSheet.Name & "'!R32C3:R32C" & iStatusDateCol & ""
End With

'Apply last chart column to the CPI, SPI & TCPI series
on CPI / SPI chart.
iStatusDateCol = iStatusDateCol + 16
With oCurrentChartSheet.ChartObjects("Chart 2").Chart
.SeriesCollection(1).Values = "='" &
oCurrentChartSheet.Name & "'!R29C19:R29C" & iStatusDateCol & ""
.SeriesCollection(2).Values = "='" &
oCurrentChartSheet.Name & "'!R30C19:R30C" & iStatusDateCol & ""
.SeriesCollection(3).Values = "='" &
oCurrentChartSheet.Name & "'!R31C19:R31C" & iStatusDateCol & ""
End With
End With

Case "Workforce"
With oCurrentChartSheet.ChartObjects("Chart 2").Chart
.SeriesCollection(2).Values = "='" &
oCurrentChartSheet.Name & "'!R32C3:R32C" & iStatusDateCol - 1 & ""
End With

End Select

Next x
End If
Loop Until rngTestRange.Row = lngLastRow

'delete extra sheets
oThisMgrChartBook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete

'save the chart notebook
Dim sChartNotebookName As String
sChartNotebookName = Left(oMgrNotebook.FullName,
Len(oMgrNotebook.FullName) - 4) & "_chart.xls"
oThisMgrChartBook.SaveAs Filename:=sChartNotebookName

'opening the chart skel made it active so re-active the mrg notebook
oMgrNotebook.Activate

'delete the chartgen sheet
oSheet.Delete
End Sub

 

[Peter T]

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

 

[Marko Zitic]

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'schart notebook
               'execution of the following line cause theAutomation 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
                    .Range("A50") = oMacroParams.StatusDateLabel
                    .Range("A51") = rngTestRange
                End With

               'moving to variable calendar range, so copy labels to charts
                oSheet.Range("E6:R6").Copy
                oCurrentChartSheet.Range("B51").PasteSpecial xlPasteValues

                'copy from source to this chart's data
                With oSheet
                    .Range(.Cells(rngTestRange.Row + 1, 5),
.Cells(rngTestRange.Row + 16, 20)).Copy
                End With
                'oSheet.Range("E" & rngTestRange.Row + 1 & ":T" &
rngTestRange.Row + 16).Copy
                oCurrentChartSheet.Range("B52").PasteSpecial xlPasteValues

                'update acwp, bcwp series
                iStatusDateCol = GetStatusDateCol(oCurrentChartSheet)
                Select Case sSourceChartName
                Case "EarnedValue"
                    With oCurrentChartSheet.ChartObjects("Chart 1").Chart
                        .SeriesCollection(2).Values = "='" &
oCurrentChartSheet.Name & "'!R31C3:R31C" & iStatusDateCol & ""
                        .SeriesCollection(3).Values = "='" &
oCurrentChartSheet.Name & "'!R32C3:R32C" & iStatusDateCol & ""
                    End With

                    'Apply last chart column to the CPI, SPI & TCPI series
on CPI / SPI chart.
                    iStatusDateCol = iStatusDateCol+ 16
                    With oCurrentChartSheet.ChartObjects("Chart 2").Chart
                        .SeriesCollection(1).Values = "='" &
oCurrentChartSheet.Name & "'!R29C19:R29C" & iStatusDateCol & ""
                        .SeriesCollection(2).Values = "='" &
oCurrentChartSheet.Name & "'!R30C19:R30C" & iStatusDateCol & ""
                        .SeriesCollection(3).Values = "='" &
oCurrentChartSheet.Name & "'!R31C19:R31C" & iStatusDateCol & ""
                    End With
                End With

                Case "Workforce"
                    With oCurrentChartSheet.ChartObjects("Chart 2").Chart
                        .SeriesCollection(2).Values = "='" &
oCurrentChartSheet.Name & "'!R32C3:R32C" & iStatusDateCol - 1 & ""
                    End With

                End Select

            Next x
        End If
    Loop Until rngTestRange.Row = lngLastRow

    'delete extra sheets
    oThisMgrChartBook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete

    'save the chart notebook
    Dim sChartNotebookName As String
    sChartNotebookName = Left(oMgrNotebook.FullName,
Len(oMgrNotebook.FullName) - 4) & "_chart.xls"
    oThisMgrChartBook.SaveAs Filename:=sChartNotebookName

    'opening the chart skel made it active so re-active the mrg notebook
    oMgrNotebook.Activate

    'delete the chartgen sheet
    oSheet.Delete
End Sub

Hi,

you can try using GemBox.Spreadsheet .NET component for spreadsheet
files.
http://www.gemboxsoftware.com/GBSpreadsheet.htm

 

[Joe]

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

 

[Joe]

BTW...I'm using SP2.

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]

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]

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

Where:
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

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

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]

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

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

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

Where:
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

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

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

:

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

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
.Range("A50") = oMacroParams.StatusDateLabel
.Range("A51") = rngTestRange
End With

'moving to variable calendar range, so copy labels to
charts
oSheet.Range("E6:R6").Copy
oCurrentChartSheet.Range("B51").PasteSpecial
xlPasteValues

'copy from source to this chart's data
With oSheet
.Range(.Cells(rngTestRange.Row + 1, 5),
.Cells(rngTestRange.Row + 16, 20)).Copy
End With
'oSheet.Range("E" & rngTestRange.Row + 1 & ":T" &
rngTestRange.Row + 16).Copy
oCurrentChartSheet.Range("B52").PasteSpecial
xlPasteValues

'update acwp, bcwp series
iStatusDateCol = GetStatusDateCol(oCurrentChartSheet)
Select Case sSourceChartName
Case "EarnedValue"
With oCurrentChartSheet.ChartObjects("Chart
1").Chart
.SeriesCollection(2).Values = "='" &
oCurrentChartSheet.Name & "'!R31C3:R31C" & iStatusDateCol & ""
.SeriesCollection(3).Values = "='" &
oCurrentChartSheet.Name & "'!R32C3:R32C" & iStatusDateCol & ""
End With

'Apply last chart column to the CPI, SPI & TCPI
series
on CPI / SPI chart.
iStatusDateCol = iStatusDateCol + 16
With oCurrentChartSheet.ChartObjects("Chart
2").Chart
.SeriesCollection(1).Values = "='" &
oCurrentChartSheet.Name & "'!R29C19:R29C" & iStatusDateCol & ""
.SeriesCollection(2).Values = "='" &
oCurrentChartSheet.Name & "'!R30C19:R30C" & iStatusDateCol & ""
.SeriesCollection(3).Values = "='" &
oCurrentChartSheet.Name & "'!R31C19:R31C" & iStatusDateCol & ""
End With
End With

Case "Workforce"
With oCurrentChartSheet.ChartObjects("Chart
2").Chart
.SeriesCollection(2).Values = "='" &
oCurrentChartSheet.Name & "'!R32C3:R32C" & iStatusDateCol - 1 & ""
End With

End Select

Next x
End If
Loop Until rngTestRange.Row = lngLastRow

'delete extra sheets
oThisMgrChartBook.Sheets(Array("Sheet1", "Sheet2",
"Sheet3")).Delete

'save the chart notebook
Dim sChartNotebookName As String
sChartNotebookName = Left(oMgrNotebook.FullName,
Len(oMgrNotebook.FullName) - 4) & "_chart.xls"
oThisMgrChartBook.SaveAs Filename:=sChartNotebookName

'opening the chart skel made it active so re-active the mrg
notebook
oMgrNotebook.Activate

'delete the chartgen sheet
oSheet.Delete
End Sub

 

[Joe]

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.

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

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

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

Where:
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

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

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

:

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

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

 

[Peter T]

Set oFrom = oChartSkel.Sheets(x)
Which of the above lines gave you the error?
Did the demo I posted earlier work?

Regards,
Peter T

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.

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

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

Where:
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

:

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

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

:

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

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

 

[Joe]

It errors on oFrom.Copy After:=oTo. I tried running your example, however,
it couldn’t find Chart1 (or Chart 1) in Book1 even though I was looking at
the chart while processing.

What I found interesting about this example was when I saved Book1 as
Book1.xls and ran the marco I encountered a new error on line “Set ws1 =
Workbooks("Book1").Worksheets("Sheet1")â€

Run-time error ‘-2147352565 (8002000b)’:

The specified dimension is not valid for the current chart type.

Regards,
Joe

Which of the above lines gave you the error?
Did the demo I posted earlier work?

Regards,
Peter T

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.

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




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

Where:
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

:

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

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

:

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

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

 

[Peter T]

I encountered a new error on line "Set ws1 =

Workbooks("Book1").Worksheets("Sheet1")"

You'd need to change "Book1" and "Sheet1" to suit. Eg, change "Book1" to
"Book1.xls" if you had saved the workbook.

Run-time error '-2147352565 (8002000b)':

There are one or two scenarios that trigger this error with charts in 2007,
some of which are new to SP2.

If you want to send a stripped down version of your file (zipped), just
enough to replicate the problem, I'll have a look tomorrow. Please include
full details of what to do if not obvious.

Regards,
Peter T

It errors on oFrom.Copy After:=oTo. I tried running your example,
however,
it couldn't find Chart1 (or Chart 1) in Book1 even though I was looking at
the chart while processing.

What I found interesting about this example was when I saved Book1 as
Book1.xls and ran the marco I encountered a new error on line "Set ws1 =
Workbooks("Book1").Worksheets("Sheet1")"

Run-time error '-2147352565 (8002000b)':

The specified dimension is not valid for the current chart type.

Regards,
Joe

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

Which of the above lines gave you the error?
Did the demo I posted earlier work?

Regards,
Peter T

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.

:

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




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

Where:
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

:

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

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

:

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

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

 

[Joe]

Peter T,

Thanks for the offer. I have a stripped down version in a zip file. Please
advise as to how I can xfer this to you.

Regards,
Joe

I encountered a new error on line "Set ws1 =
Workbooks("Book1").Worksheets("Sheet1")"

You'd need to change "Book1" and "Sheet1" to suit. Eg, change "Book1" to
"Book1.xls" if you had saved the workbook.

Run-time error '-2147352565 (8002000b)':

There are one or two scenarios that trigger this error with charts in 2007,
some of which are new to SP2.

If you want to send a stripped down version of your file (zipped), just
enough to replicate the problem, I'll have a look tomorrow. Please include
full details of what to do if not obvious.

Regards,
Peter T

It errors on oFrom.Copy After:=oTo. I tried running your example,
however,
it couldn't find Chart1 (or Chart 1) in Book1 even though I was looking at
the chart while processing.

What I found interesting about this example was when I saved Book1 as
Book1.xls and ran the marco I encountered a new error on line "Set ws1 =
Workbooks("Book1").Worksheets("Sheet1")"

Run-time error '-2147352565 (8002000b)':

The specified dimension is not valid for the current chart type.

Regards,
Joe

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

Which of the above lines gave you the error?
Did the demo I posted earlier work?

Regards,
Peter T


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.

:

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




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

Where:
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

:

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

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]

Please forward to me here

pmbthornton gmail com

Regards,
Peter T

Peter T,

Thanks for the offer. I have a stripped down version in a zip file.
Please
advise as to how I can xfer this to you.

Regards,
Joe

<snip>

 

[Peter T]

Follow up:
Joe sent me his file, it turned out there was a "lost" chart on the sheet
with all of its Series having invalid references. In Excel 2007 copying a
sheet with such a chart led to failure to reconcile the invalid references
and the automation error Joe described in this thread. Deleting the chart
resolved the copy sheet problem.
The chart would have become "lost" as a result of deleting all the rows that
contained it, making its height zero and effectively not visible.
It wasn't obvious!

Peter T