Problem with excel macro - getting automation error

[arlette4u_2003]

I have created a macro to copy some graphs from 1 workbook to another
and formats them. However, after running the macro, it gives an error
saying "Run-time error '-2147417848 (80010108)': Automation error. The
object invoked has disconnected from its clients." This crashes excel
completely.

 

[Jon Peltier]

Well, if you post the offending code, somebody may be able to suggest a fix.

- Jon

 

[Arlette]

The offending code is as below:

public sub create_chart()

Dim chart2 As Chart, chart4 As Chart, chart10 As Chart, chart15 As
Chart, chart12 As Chart
Dim chartobj As ChartObject


Windows(Frmselavs.selbuyer).Activate

chartobj = ActiveSheet.ChartObjects

Set chart2 = chartobj.Chart
Set chart4 = chartobj.Chart
Set chart10 = chartobj.Chart
Set chart15 = chartobj.Chart
Set chart12 = chartobj.Chart

chart2 = ActiveSheet.ChartObjects("Chart2")
chart4 = ActiveSheet.ChartObjects("Chart4")
chart10 = ActiveSheet.ChartObjects("Chart10")
chart15 = ActiveSheet.ChartObjects("Chart15")
chart12 = ActiveSheet.ChartObjects("Chart12")

Windows(filename).Activate
Columns("G:G").Activate
Selection.ColumnWidth = 3.43
Columns("N:N").Activate
Selection.ColumnWidth = 3.43


'ACTIONS FOR CHART 2
Windows(Frmselavs.selbuyer).Activate
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart2").Activate
ActiveChart.ChartArea.Copy
Windows(filename).Activate
Sheets(z).Select
Range("A6").Select
ActiveSheet.Paste
ActiveWindow.Visible = False


'ACTIONS FOR CHART 4
Windows(Frmselavs.selbuyer).Activate
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart4").Activate
ActiveChart.ChartArea.Copy
Windows(filename).Activate
Sheets(z).Select
Range("H6").Select
ActiveSheet.Paste
ActiveWindow.Visible = False


'ACTIONS FOR CHART 10
Windows(Frmselavs.selbuyer).Activate
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart10").Select
ActiveChart.ChartArea.Copy
Windows(filename).Activate
Sheets(z).Select
Range("O6").Select
ActiveSheet.Paste


'ACTIONS FOR CHART 15
Windows(Frmselavs.selbuyer).Activate
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart15").Select
ActiveChart.ChartArea.Copy
Windows(filename).Activate
Sheets(z).Select
Range("C25").Select
ActiveSheet.Paste


'ACTIONS FOR CHART 12
Windows(Frmselavs.selbuyer).Activate
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart12").Select
ActiveChart.ChartArea.Copy
Windows(filename).Activate
Sheets(z).Select
Range("J25").Select
ActiveSheet.Paste


'ALIGN ALL THE CHARTS IN THE SHEET

Dim iChart As Long
Dim nCharts As Long
Dim dTop As Double
Dim dLeft As Double
Dim dHeight As Double
Dim dWidth As Double
Dim nColumns As Long


dHeight = 217
dWidth = 289


nCharts = ActiveSheet.ChartObjects.count

For iChart = 1 To nCharts

With ActiveSheet.ChartObjects(iChart)

.Activate
.Height = dHeight
.Width = dWidth
.Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth

ActiveChart.Legend.Position = xlLegendPositionBottom

End With

Next

end sub

 

[Jon Peltier]

These lines should raise error 91, "Object variable or With block variable
not set":

chartobj = ActiveSheet.ChartObjects

Set chart2 = chartobj.Chart
Set chart4 = chartobj.Chart
Set chart10 = chartobj.Chart
Set chart15 = chartobj.Chart
Set chart12 = chartobj.Chart

There seems no reason for you to have declared 'chartobj'.

Why do you keep doing this:

Windows(Frmselavs.selbuyer).Activate

This whole block

Windows(Frmselavs.selbuyer).Activate
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart2").Activate
ActiveChart.ChartArea.Copy
Windows(filename).Activate
Sheets(z).Select
Range("A6").Select
ActiveSheet.Paste
ActiveWindow.Visible = False

can be replaced by something like this

Workbooks("Source.xls").Worksheets("Sheet1").ChartObjects("Chart
1").Copy
Workbooks("Target.xls").Worksheets("Sheet2").Paste

No need for all of that inefficient activating and selecting.

If you simplify the code, you may eliminate the problem.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______