| Home |
| Search |
| Today's Posts |
|
|
|
#1
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 |
| 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 | |||
Hybrid Mode
