Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
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 .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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
On Aug 5, 1:50*am, Joe wrote:
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 Hi, you can try using GemBox.Spreadsheet .NET component for spreadsheet files. http://www.gemboxsoftware.com/GBSpreadsheet.htm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
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. Ive 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 .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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
BTW...I'm using SP2.
"Joe" wrote: 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. Ive 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 .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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
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 .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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
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 .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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
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 .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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
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 "Joe" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
It errors on oFrom.Copy After:=oTo. I tried running your example, however,
it couldnt 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 "Peter T" wrote: 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 "Joe" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
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 "Joe" wrote in message ... 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 "Peter T" wrote: 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 "Joe" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
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 "Peter T" wrote: 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 "Joe" wrote in message ... 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 "Peter T" wrote: 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 "Joe" wrote in message ... 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
Please forward to me here
pmbthornton gmail com Regards, Peter T "Joe" wrote in message ... 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 -- Automation Error
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automation error '-2147417848 (80010108)' excel 2007 | Excel Discussion (Misc queries) | |||
Ole Automation and Excel 2007 footer | Excel Worksheet Functions | |||
Compile error: Automation error in Excel 97 | Excel Programming | |||
Automation Error in Excel VBA | Excel Programming | |||
VB Excel Automation Error | Excel Programming |