Chart axis change with .Chartype changes

[alangea]

I have done some coding in VBA to export data from a Query to Excel, have Excel draw a chart and then export it to jpg to import in a report.
I am facing a problem with my X axis in the chart. It changes when changing the charttype to xlXYScatterLinesNoMarkers

Here is the code:

Sub open_excel_file(filename As String)
Dim MinX As Double
Dim MaxX As Double
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook


Dim xlWS As Excel.Worksheet
Dim SQL As String
Dim personid As Double
Dim mindato As Date
Dim maxdato As Date
Set xlApp = New Excel.Application


'Find værdier på akser
'KG akse
personid = Me.Id.Value
SQL = "SELECT Person_kg.Dato, Person_kg.Weight, Person_data.Max_kg, Person_data.Maal FROM Person_data INNER JOIN Person_kg ON Person_data.Id = Person_kg.Person_id WHERE Person_data.Id = " & personid & " ORDER BY Person_kg.Dato DESC"
MinY = min_in_columns(SQL, 1, 3) - 1
MaxY = max_in_columns(SQL, 1, 3) + 1
mindato = min_date_columns(SQL, 0, 0)
maxdato = max_date_columns(SQL, 0, 0)

'Max and Min for axis is now calculated
'Open Excel file
' Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
Set xlWB = .Workbooks.Open(filename, , False)
End With
Set xlWS = xlWB.Worksheets("Graph")

'Format colum A in Excel sheet to dates
xlWS.Columns("A:A").NumberFormat = "DD-MM-YY"
xlWB.Charts.Add 'chart is open and shows dates correct
With xlWB.ActiveChart
.ChartType = xlXYScatterLinesNoMarkers 'chart changes and shows dates incorrect.
.HasTitle = False 'True
' .ChartTitle.Characters.Text = "Name"
.Axes(xlCategory).CategoryType = xlDate
.Axes(xlCategory).BaseUnit = xlDays
.Axes(xlValue).MinimumScale = MinY
.Axes(xlCategory).MinimumScale = mindato
.Axes(xlCategory).MaximumScale = maxdato
.AutoScaling = True
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dato"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Vægt [Kg]"
.Axes(xlCategory).HasMajorGridlines = True
.Axes(xlCategory).HasMinorGridlines = False
.Axes(xlValue).HasMajorGridlines = True
.Axes(xlValue).HasMinorGridlines = False

End With
filename2 = Replace(filename, ".xls", ".jpg")
xlWB.ActiveChart.Export filename2, FilterName:="JPG"
'Closing Excel
ActiveWorkbook.Saved = True
xlWB.Close
xlApp.Quit
Set xlWB = Nothing
DoEvents
Set xlApp = Nothing
DoEvents
Dim strClsExl As String
strClsExl = "TASKKILL /F /IM Excel.exe"
Shell strClsExl, vbHide


End Sub


Here pictures of the charts.

Correct dates:


After changing the x axis has changes, steps now varies:


Anybody has a solution to keep the correct format of the date x axis?

[June7]

Have you considered building the graph in the Access report?

I analyze graph issues best by reviewing and testing directly if you want to provide file.

[alangea]

Hi again.
I changed the Chartype to xlLine.
That did the job and now the chart is correct