Add Chart Data from other Workbooks into one series with VBA - Excel 2007

Question

Hi All,

I need to create a Chart which will grab data from external sources when a macro is run. Setting up the UserForm and all that is fine, all done, however I cant find out how to add another piece of the series.

Is it even possible to have a series that sources data from several different locations? Basically I need it to go in chronological order (Horizontal Axis are all dates), but each date comes from a different workbook entirely... I have tried to get Excel to do this with its basic functions, I just cant get it to put it into the graph! If possible, I would like to to not have to copy all the reference data to the workbook with the graphs, as it is already going to have 16+ Charts that will be on their own sheet.

Does anyone know how to make Excel accept many different references into one series? And how it would be done with VBA? Im not too bad with VBA, just havent had to deal with Charting before... Any help would be greatly appreciated!!

Thanks,

Rob.

Answer 1

I don't think you can have a single axis of a single series sourced from multiple ranges/addresses, but you can write VBA to set the values of a chart series to specific values rather than to a contiguous range. Here's an example from the help:

Charts("Chart1").SeriesCollection(1).Values = Array(1, 3, 5, 7, 11, 13, 17, 19)
Charts("Chart1").SeriesCollection(1).XValues = Array(5.0, 6.3, 12.6, 28, 50)

You would have to write code to read the values out of the ranges in your different workbooks, but this should work.

Answer 2

You cannot combine data from different worksheets into the values of a series, or into its Xvalues. (You can use data from one sheet for X and another for Y).

I recommend setting up a chart data worksheet, then copy the data from each of your data sheets and pasting it in this sheet.

The problem with using arrays for the data is that the length of the array is limited to around 250 characters. Including commas and assuming only a 2 digit number with no decimal digits, this means you get only 80 values. If you have floating point values, you will be lucky to get more than about 15 values.