By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Change Datasource of embedded Excel Unbound Object Frame

P: n/a
I have searched the web all day for a lead on this.

I have found how to control the Graph object mut not the embedded excel
unbound object frame.

I am trying to chart the data below on one chart that comes from a
query. I have managed to do this in excel and embedding the worksheet.

Program 2005 2006 2007 2008 2009 2010

Series 1 130 146 185 217 262 299
Series 2 528 634 794 1025 1054 1155
Series 3 1051 1231 1508 1752 1953 2108
Series 4 200 210 254 292 360 434
Series 5 906 918 918 1186 1250 1488
Series 6 1153 1370 1597 1797 1929 2328
Series 7 424 475 783 952 1097 1163
Series 8 100 80 80 94 110 176
Series 9 274 274 307 371 415 437
Series 10 26 26 26 26 26 38
Series 11 689 801 890 999 1091 1208

I then assign the datasource cells on the worksheet by looping through
this recordset thus

Dim rst As Object
Dim x, y

Set rst = Me.Recordset.Clone

With rst

y = 3

On Error Resume Next

While Not .EOF

rst.MoveNext

XLChart.worksheets(1).Cells(y, 2).Value = ![Program]

XLChart.worksheets(1).Cells(y, 3).Value = nnz(![SumOfR0])
XLChart.worksheets(1).Cells(y, 4).Value = nnz(![SumOfR1])
XLChart.worksheets(1).Cells(y, 5).Value = nnz(![SumOfR2])
XLChart.worksheets(1).Cells(y, 6).Value = nnz(![SumOfR3])
XLChart.worksheets(1).Cells(y, 7).Value = nnz(![SumOfR4])
XLChart.worksheets(1).Cells(y, 8).Value = nnz(![SumOfR5])

y = y + 1
Wend

..Close

End With

My problem is that if the query is filtered and the recordcount is less
than 11 the legend on the chart still shows 11 series but if there were
5 records - I would have 5 lines and 6 #Ref because excel is still
looking for 11 datasources.

So I need to be able to dynamically change the unbound objects charts
datasource.

So far I have tried things like

XLChart.SetSourceData Source:=Sheets("worksheets(1)").Range ("B3:H5"),
PlotBy:=xlRows

I tried to get the basics right - so I tried something simple.
Change the title.

XLChart.worksheets(1).ChartObjects("Chart 1").ChartArea.Select

XLChart.worksheets(1).ChartObjects("Chart
1").ChartArea.Select.HasTitle = True
XLChart.worksheets(1).ChartObjects("Chart
1").ChartArea.Select.ChartTitle.Characters.Text = "My Title"

This fails as well.

Is it a missing reference or something?
wrong syntax - probably - this should be simple.

Any and all help is appreciated.

Gary

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.