Excel - Update Chart Source Data

[Mike]

All,

I am attemping to use VBA to update the source data of a chart. Here is my
code:

Sheets("Summary_Chart").Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.SetSourceData Source:=Sheets("Summary").Range("A1:A22,F1:F22"),
PlotBy:=xlColumns

The chart is on a worksheet named Summary_Chart and the data is on a
worksheet called Summary. There is only one chart on the Summary_Chart
sheet.

When I try to run this code I get the following error on the second line of
code above:
Run-time error '1004': Unable to get the ChartObjects property of the Chart
class.

Please help!

Thanks,

Mike

 

[Jim Cone]

Mike,

This might work, give it a try...

Sheets("Summary_Chart").Select
With ActiveSheet.ChartObjects(1).Chart
.SetSourceData Source:=ActiveSheet.Range("A1:A24,F1:F24"), PlotBy:=xlColumns
End With

Regards,
Jim Cone
San Francisco, CA

 

[Mike]

Jim,

I gave that a try and got the same error. Any other ideas?

Thanks,

Mike

 

[Jim Cone]

Mike,

Not really, however, I did notice after looking at the code again that it should have read...
(I had the wrong source data sheet)

Sub TestChartCode()
Sheets("Summary_Chart").Select
Sheets("Summary_Chart").ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Summary").Range("A1:A24,F1:F24"), PlotBy:=xlColumns
End Sub

I just set up a dummy chart and renamed a couple of sheets and
ran the above code in XL2002 and it worked without a hitch.

If the above doesn't work, what happens if you select the chart manually then run the code?

Regards,
Jim Cone
San Francisco, CA

 

[Jim Cone]

Mike,
I've gone as far as I can, maybe someone else can help.
Regards,
Jim Cone
San Francisco, CA

 

[Andy Pope]

Mike,

A chart sheet doesn't require the ChartObjects.Chart reference.
This modified code runs.

Sub TestChartCode()
Sheets("Summary_Chart").Select
Sheets("Summary_Chart").ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Summary").Range("A1:A24,F1:F24"), PlotBy:=xlColumns
End Sub

Cheers
Andy

 

[Mike]

Andy,

I tried running the code and it gave me the same result. After taking a
closer look it appears to be the same code?

Did you have different code you intended to post?

Thanks,

Mike

 

[Andy Pope]

Doh! I tripped up over cutting and pasting and testing the code.

I should have removed the ChartObjects and Chart references.

Sub TestChartCode()
Sheets("Summary_Chart").Select
Sheets("Summary_Chart").SetSourceData _
Source:=Sheets("Summary").Range("A1:A24,F1:F24"), PlotBy:=xlColumns
End Sub

Cheers
Andy

 

[Mike]

Andy,

Yes the updated code works. Thanks!

Unfortinunatley, I now have a new problem.I know this may be outside the
scope of this forum, but here goes.

When I copy this code from the Excel Macro to a SQL DTS ActiveX Task I get
the error:

Error Source : Microsoft Data Transformation Services (DTS)
Error Description : Error Code: 0
Error Source= Microsoft VBScript compiliation error
Error Description: Expected statement

Error on Line 15

Here is the code

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Main = DTSTaskExecResult_Success

Set MsExcObj = CreateObject("Excel.Application")
Set wbk = GetObject("\\server\share\charttest.xls")

MsExcObj.Visible = False

'***** Update Summary Chart
wbk.Worksheets("Summary_Chart").Select
wbk.Worksheets("Summary_Chart").SetSourceData _
Source:=Sheets("Summary").Range("A1:A24,F1:F24"), PlotBy:=xlColumns
(This is line 15)

wbk.Parent.Windows(wbk.Name).Visible = True
wbk.Save
MsExcObj.Quit

Set wbk = Nothing
Set MsExcObj = Nothing
End Function

Any help would be appriciated.

Thanks,

Mike

 

[Andy Pope]

Do you need to make the workbook visible before saving?
Especially as the Excel application itself is invisible.

In the small test I did making the workbook window visible did not make
the Excel app appear.

Cheers
Andy

 

[Mike]

Andy,

I'm sorry - when I put the (this is line 15) at the end of the line it
wrapped to the next line. Here is line 15:

Line 14: wbk.Worksheets("Summary_Chart").SetSourceData _
Line:15: Source:=Sheets("Summary").Range("A1:A24,F1:F24"),
PlotBy:=xlColumns

When I run the code I get the error:

Error Source : Microsoft Data Transformation Services (DTS)
Error Description : Error Code: 0
Error Source= Microsoft VBScript compiliation error
Error Description: Expected statement

Error on Line 15

Thanks,

Mike

 

[Andy Pope]

Curse those line wraps!!

Its possible the source needs the full workbook reference,
(hopefully the wbk object is the correct workbook)

wbk.Worksheets("Summary_Chart").SetSourceData _
Source:=wbk.Sheets("Summary").Range("A1:A24,F1:F24"), _
PlotBy:=xlColumns

Cheers
Andy