Excel automation, change Category (X) Axis Labels, VB6

[Zico Damatsu]

Hello,
I put some data to excel sheet from Access DB through VB6. I can create charts but I can set the labels for the X axes. For example if I set data for charts as :
| A | B |
1 | 3 | 10 |
2 | 4 | 25 |
3 | 5 | 55 |

Excel Automaticaly set LAbels for X-axes as: 3,4,5, and Y Values as 10,25,55. Because Title and value columns are next to each other.
But in my case I want Y values as Column B1:B3 and X labels as E1:E3 which is not next to column B.
| A | B | C | D | E |
1 | X | 10 | X | X | 3 |
2 | X | 25 | X | X | 4 |
3 | X | 55 | X | X | 5 |
Manually this can be done by changing Category (X) Axis Labels on Excel Chart's Source Data options.

Is there any way to do that by programming.

I use this sample code .
----------------------------------------------
Dim new_chart As Chart
Set new_chart = Charts.Add()

With new_chart
.ChartType = xlLineMarkers
.HasLegend = False
.SetSourceData Source:=active_sheet.Range("B1:B3), PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:=active_sheet.Name
End With

active_sheet.Shapes(active_sheet.Shapes.Count).Top = 10
active_sheet.Shapes(active_sheet.Shapes.Count).Left = _
100
active_sheet.Shapes(active_sheet.Shapes.Count).Width = _
600
active_sheet.Shapes(active_sheet.Shapes.Count).Height = _
400


ActiveChart.ChartArea.Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Monthly BSP Index"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, _
xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text _
= "Value"
End With



Submitted via EggHeadCafe - Software Developer Portal of Choice
Building Websites with DotNetNuke
http://www.eggheadcafe.com/tutorial...8-3b2ec766fe52/building-websites-with-do.aspx

 

[Gerald Haberl]

With new_chart
.........
.SeriesCollection(1).XValues=active_sheet.Range("E1:E3")
End With

Gerald