Help Excel Chart Axes

Targets Windows
1

Hello,

maybe someone can help me? I would like to set a min and max value for the axes of the diagram that will be displayed in excel.

However, in Xojo I get an OLE error message.

Var f As FolderItem = SpecialFolder.Desktop.Child(“Datei.xlsx”)
excel.workbooks.Open(f.NativePath)
excel.visible = true

excel.Worksheets(“Dosierung”).ChartObjects(1).Left = 10
excel.Worksheets(“Dosierung”).ChartObjects(1).top = 10
excel.Worksheets(“Dosierung”).ChartObjects(1).Activate
excel.Worksheets(“Dosierung”).ChartObjects(1).Axes(office.xlCategory).MinimumScale = 10

The code works except for the last command.

Thanks in advance

2

Hello Norbert,

Another way to have this work is to use the ActiveChart command since the previous line of code has Activate.

Excel.ActiveChart.Axes(Office.xlValue).MinimumScale = 270

Here is an example where I used a shortened version of the ChartObjects code:

Var Excel As New ExcelApplication
excel.visible = True //Show Excel
excel.workbooks.add // Add a workbook for our data

//Add data to excel workbook
excel.range("A1").value = "Jan 07"
excel.range("A2").value = "Jan 14"
excel.range("A3").value = "Jan 21"
excel.range("A4").value = "Jan 28"
excel.range("A5").value = "Feb 04"
excel.range("A6").value = "Feb 11"

excel.range("B1").value = "298.8"
excel.range("B2").value = "296.6"
excel.range("B3").value = "291"
excel.range("B4").value = "288.1"
excel.range("B5").value = "288.2"
excel.range("B6").value = "286.2"

excel.range("C1").value = "5.00"
excel.range("C2").value = "6.10"
excel.range("C3").value = "7.20"
excel.range("C4").value = "8.30"
excel.range("C5").value = "9.40"
excel.range("C6").value = "10.50"

excel.Charts.add //add the chart
Excel.Charts(1).Select_
Excel.Charts(1).Activate
'excel.charts.Select_ //select the chart

excel.ActiveChart.SetSourceData Excel.Sheets("Sheet1").Range("A1:C6") // Select the data Range
excel.ActiveChart.ChartType = office.xlXYScatterLinesNoMarkers   //Choose the Chart Type
excel.activechart.PlotBy = office.xlvalue //Plot the data by columns

excel.activechart.SeriesCollection(2).Select_ //Select Series 2
excel.Activechart.SeriesCollection(2).AxisGroup = 2  //Set series 2 to second axis
excel.activechart.axes(office.xlvalue, office.xlprimary).HasTitle = True
excel.activechart.axes(office.xlvalue, office.xlprimary).axistitle.characters.Text = "Gasoline Price"
excel.activechart.axes(office.xlcategory, office.xlprimary).HasTitle = True
excel.activechart.axes(office.xlcategory, office.xlprimary).axistitle.characters.Text = "Dates"
excel.activechart.axes(office.xlvalue, office.xlSecondary).HasTitle = True
excel.activechart.axes(office.xlvalue, office.xlSecondary).axistitle.characters.Text = "Door Price"
excel.activechart.SeriesCollection(1).name = "Gasoline"
excel.activechart.SeriesCollection(1).ChartType = office.xlXYScatterLines
excel.activechart.SeriesCollection(2).name = "Door"
excel.activechart.SeriesCollection(2).Select_
excel.activechart.SeriesCollection(2).ChartType = office.xlXYScatterSmoothNoMarkers

Excel.Charts(1).Axes(Office.xlValue).MinimumScale = 270 //This works on my machine

Exception err  //Report errors
  MessageDialog.Show err.message
3

Thank you Eugene,

I found a solution with your template.

var excel as new ExcelApplication
excel.workbooks.Open("D:\BA_20978_70_8.xlsx")

excel.Worksheets("Dosierungen KG (A)").ChartObjects(1).Select_
excel.Worksheets("Dosierungen KG (A)").ChartObjects(1).Activate

excel.activechart.SeriesCollection(1).Select_
excel.activechart.Axes(Office.xlValue).MinimumScale = 200
1 Like