SetSourceData Source question...

[danomaniac]

Hi gang.

I'm creating an app in VB6 that opens Excel, populates it with some data, then creates a chart from the data. I'm having trouble setting the SourceData Source for my chart object. The number of data entries varies so I can't set my source as an absolute reference.

I've used this and it works...

ActiveChart.SetSourceData Source:=Sheets("Results&quot.Range("A1425&quot, PlotBy _
:=xlColumns

But I need to set the range using a variable. I've managed to figure out how to create a variable sized named range depending on how many lines will be populated, but I can't figure out how to make this work...

ActiveChart.SetSourceData Source:=Sheets("Results&quot.Range("Range1&quot, PlotBy _
:=xlColumns

Any help would be appreciated. Thanks.

 

[xlbo]

To use a dynamic range, I THINK (please correct me if I'm wrong) that you have to manually add a dynamic name for each data series in the chart. To do it dynamically in code, try:
ActiveChart.SetSourceData Source:=Sheets("Results&quot.Range("A14" & range("D65536&quot.end(xlup).row), PlotBy _
:=xlColumns

you may need to use something like Current Region if you have more data below the chart data
HTH Rgds
~Geoff~

 

[danomaniac]

Thanks for the reply Geoff, I ended up getting it to work. My problem was the previous line was something like Sheets("Results&quot.Select - I took that line out and it worked with the ...Sheets("Results&quot.Range("Range1&quot, PlotBy ... line. Thanks