Link to home
Start Free TrialLog in
Avatar of Juan Velasquez
Juan VelasquezFlag for United States of America

asked on

How do I display years across the x-axis

Hello,
I am using the following code to create a graph in a excel worksheet from vb.net.  The code preceding the code listed below creates the table from which the graph is dereived.  I'm having trouble having the years appear on the x-axis .  
Dim oChart As Excel.Chart
                    Dim MyCharts As Excel.ChartObjects
                    Dim MyCharts1 As Excel.ChartObject
                    MyCharts = excelSheet.ChartObjects
                    'set chart location
                    MyCharts1 = MyCharts.Add(150, 290, 400, 250)

                    oChart = MyCharts1.Chart
                    'use the follwoing line if u want 
                    'to draw chart on the default location
                    'oChart.Location(Excel.XlChartLocation.xlLocationAsObject, excelSheet.Name)


                    Dim R As Integer = datTable.Rows.Count + 1
                    'MessageBox.Show(R.ToString)
                    With oChart
                        'set data range for chart
                        Dim chartRange As Excel.Range
                        chartRange = excelSheet.Range("A1", "N" & R)
                        'MessageBox.Show(excelSheet.Range("A2").Value.ToString)
                        .SetSourceData(chartRange)
                        'set how you want to draw chart i.e column wise or row wise

                        '.PlotBy = Excel.XlRowCol.xlColumns

                        .PlotBy = Excel.XlRowCol.xlRows

                        .SeriesCollection(1).Delete()
                        'set data lables for bars
                        .ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)


                        'set legend to be displayed or not
                        .HasLegend = True
                        'set legend location
                        .Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
                        'select chart type
                        .ChartType = Excel.XlChartType.xlLine
                        'chart title
                        .HasTitle = True
                        .ChartTitle.Text = "Test"
                        'set titles for Axis values and categories
                        Dim xlAxisCategory, xlAxisValue As Excel.Axes
                        xlAxisCategory = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
                        xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
                        xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = "Forecast Years"

                        xlAxisValue = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)


                        xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
                        xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = "Metrics"
                        
                        'oChart.SeriesCollection(1).Delete()
                        MessageBox.Show("Chart Completed")
                    End With
                    'MyCharts1.BringToFront()
                    'Set file format number to 56 if excel version <> 2007 
                    If Not filename.EndsWith(".xlsx") Then
                        excelWorkbook.SaveAs(filename, 56)

                    Else
                        excelWorkbook.SaveAs(filename)
                    End If

                    MsgBox("File generated successfully at " & filename)

                End If
            End If

Open in new window

test.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Juan Velasquez

ASKER

The solution helped.  Thanks