#1
|
|||
|
|||
Excel 2010 Chart SetSourceData 1004 error
Hello,
I have a problem with an Excel application that was written in my company using Office 2003. It has always worked properly in Office 2003 but when I run it in Office 2010 I get an error message after executing the SetSourceData to apply a Range taken from a group of cells in the WorkSheet. The error message is the following: Run-time error '1004': Method 'SetSourceData' of object'_Chart' failed. I have tested the following code in an Excel file in Office 2010 and 2003 and the error only comes up in Office 2010. Sub callSetSourceData() Dim range As Range Dim chartt As ChartObject Set chartt = ActiveSheet.ChartObjects("Chart 1") On Error GoTo DelSeries: For i = 0 To 10000 chart.Chart.SetSourceData Source:=ActiveSheet.Range("A1:FA6") Next i Exit Sub DelSeries: For Each series In chart.Chart.SeriesCollection series.Delete Next series Resume Next End Sub Any ideas on how to solve this problem or why the behaviour of VBA/Chart has changed from one Office version to another? Many thanks for your help |
#2
|
|||
|
|||
Hi
Sure enough your posted code "as is" won't even work under excel 2003, that's because: chart.Chart.SetSourceData Source:=ActiveSheet.Range("A1:FA6") has to be: chartt.Chart.SetSourceData Source:=ActiveSheet.Range("A1:FA6") Otherwise you walk on thin ice: You're using the chart's name given by excel. Excel itself names chart according to the number of charts within a Sheet, for the first chart the name will be "Chart 1". Now regardless if the first chart still exists, the next chart created will be called "Chart 2". - and so on... So just check the name of the chart to be modified, if it's indeed "Chart 1" if not so you will have to rename the chart, or correct your code. See if the error renames, in case tell me which line of the code is marked by the debugger. But maybe above already helped! |
#3
|
|||
|
|||
Hi whatsup,
Thanks for taking some time to look at this problem. I have to apologize for posting the wrong code in my original question. Please find an updated version of the code that you can insert in a new module in a new Excel Workbook and test it. Code:
Sub setDataChart() Call createAColValues ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SetSourceData Source:=Range("A1:FA6"), PlotBy:=xlColumns ActiveSheet.ChartObjects(1).Activate With ActiveChart.Parent .Height = 325 .Width = 900 .Top = 120 .Left = 10 End With Call updateValues Call sendData End Sub Sub sendData() Dim cht As ChartObject Set cht = ActiveSheet.ChartObjects(1) 'On Error GoTo delSeries: For i = 0 To 1000 cht.Chart.SetSourceData Source:=ActiveSheet.Range("A1:FA6"), PlotBy:=xlColumns Next i End Sub Sub createAColValues() Range("A1").Select ActiveCell.FormulaR1C1 = "1" Range("A2").Select ActiveCell.FormulaR1C1 = "2" Range("A1:A2").Select Selection.AutoFill Destination:=Range("A1:A6"), Type:=xlFillDefault Range("A1:A6").Select End Sub Sub updateValues() Range("B1").Select ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,10)" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B6"), Type:=xlFillDefault Range("B1:B6").Select Selection.AutoFill Destination:=Range("B1:FA6"), Type:=xlFillDefault Range("B1:FA6").Select End Sub Many thanks. |
#4
|
|||
|
|||
Pocerus, what's the loop for?
I don't see the point to set the source of a chart again and again to the same Range! Can you explain what you are after? |
#5
|
||||
|
||||
Quite.
Is this what you're looking to do? (all subs rolled into 1): Code:
Sub setDataChart() Range("A1").Value = 1 Range("A1:A6").DataSeries With ActiveSheet.Shapes.AddChart .Chart.ChartType = xlXYScatterSmoothNoMarkers .Height = 325 .Width = 900 .Top = 120 .Left = 10 Range("B1:FA6").Formula = "=RANDBETWEEN(0,10)" For i = 2 To 157 .Chart.SetSourceData Source:=Union(Range("A1:A6"), Range("A1:FA6").Columns(i)) Application.ScreenUpdating = True MsgBox "OK to continue…" Next i End With End Sub |
Tags |
seriescollection, setsourcedata, vba |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Run Time Error '1004' | galkej | Excel | 0 | 02-03-2014 06:39 AM |
Run-time error '1004': Document not saved. The document may be open... | doctor_who12 | Excel Programming | 1 | 01-22-2014 04:47 PM |
paste fails error 1004 in VBA Excel 2010 | mikec | Excel Programming | 17 | 05-08-2013 03:11 PM |
Run time error 1004 | yonasan | Excel Programming | 3 | 06-12-2012 11:08 PM |
runtime error 1004 | gbaker | Excel Programming | 11 | 06-06-2012 05:23 AM |