Home Menu

Menu


ExcelBanter » Excel Newsgroups » Charts and Charting in Excel > Problem with excel macro - getting automation error
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 1
Default Problem with excel macro - getting automation error
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.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 6,582
Default Problem with excel macro - getting automation error
Well, if you post the offending code, somebody may be able to suggest a fix.

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


wrote in message
oups.com...
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.



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 14
Default Problem with excel macro - getting automation error
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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 6,582
Default Problem with excel macro - getting automation error
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").Chart Objects("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
_______



"Arlette" wrote in message
ups.com...
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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 c++ automation problem. Ken Excel Worksheet Functions 0 January 9th 07 12:13 AM
Automation Error xlEnt Excel Discussion (Misc queries) 2 May 15th 06 11:37 PM
Automation Error Bob Barnes Charts and Charting in Excel 3 November 7th 05 08:37 PM
Unspecified Automation Error _Yoyo Excel Discussion (Misc queries) 1 May 23rd 05 08:23 PM
Unspecified Automation Error _Yoyo Excel Discussion (Misc queries) 0 May 20th 05 02:06 PM


All times are GMT +1. The time now is 06:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement