CODEWITHSUNDEEP
vbaexcelcharts
hemagso
hemagso

Reputation: 41

Programmatically creating multilevel axis chart with VBA on Excel

I have some code that retrieves data from a SAS Table on a server. I'm trying to chart this data using VBA, without copying it to an Worksheet first (Feeding the recordset directly into the charts)

I've had success so far with this. The problem is, my charts need to have multilevel categorical X axis, and i'm having trouble automating this only with VBA

What I'm trying to get is something like this

https://i.sstatic.net/wNgUp.jpg (I can't post the image due to lack of reputation)

But so far all my attempts lead to this

https://i.sstatic.net/66Wcj.jpg

I know I can do this if I first make a Pivot Table from my recordset and then build a chart on top of it, but as stated before I'm trying to do this without copying the data into a Sheet.

Also tried messing with TickLabel options (Multilevel and Depth), but so far to no avail.

Upvotes: 0

Views: 526

Answers (1)

Jon Peltier
Jon Peltier

Reputation: 6063

I've tried everything I could think of, and I have not been able to create a multiple level categorical axis without having the axis data in a worksheet. I've tried various 1D and 2D arrays, with array elements that include various LF, CR, and CRLF characters. I've tried using the worksheet range, then converting XValues to an array, and of course, this broke the axis without providing a usable array.

Bottom line: Excel charts were designed to plot worksheet data, and you can't always get around this requirement. Most of the time I think you shouldn't waste time trying to get around it.

Upvotes: 1

Related Questions