Date values not appearing correclty in the x-axis

[Kush Kashyap]

Hi,

I am trying to display a graph from an ABAP program into an excel sheet via the following macro. The data is in the format:

DATE - PRICE - VENDOR

08.10.2010 - 50 - 200000

08.10.2010 - 75 - 200000

08.10.2010 - 25 - 200000

08.10.2010 - 75 - 200000

14.10.2010 - 100 - 100117

10.02.2011 - 3 - 100926

Here i am trying to create a graph with different series for each vendor. Along x-axis are the date values and the Price is along the Y-Axis. While doing so everything is fine except that the x-axis does not show the date values in the correct format, rather it is displaying them in the form of numbers like 0.5, 1 , 1.5 , 2 and so on. Instead what i want is to display the date values along the x-axis in the dd-mm-yyyy format.
Code:

sub draw_graph()
charts.add
activechart.charttype = xllinemarkersStacked
activechart.location where:=xllocATIONASOBJECT, NAME:="sheet1"'.
WITH ACTIVECHART
..PARENT.HEIGHT = 325
..PARENT.WIDTH = 500
..PARENT.TOP = 75
..PARENT.LEFT = 200
..HASTITLE = TRUE
..CHARTTITLE.CHARACTERS.TEXT = " Commodity Price Graph "
..hasLegend = false
..Axes(xlCategory, xlPrimary).HasTitle = True
..axes(xlCategory, xlprimary).axisTITLE.CHARACTERS.TEXT = "Date"
..AXES(XLVALUE, XLPRIMARY).HASTITLE = TRUE
..AXES(XLVALUE, XLPRIMARY).AXISTITLE.CHARACTERS.TEXT = "Price(INR)"
..Axes(xlValue).MaximumScale = AppLICATION.WORKSHEETFUNCTION.MAX(RANGE("B:B"))
..AXES(XLVALUE).MINIMUMSCALE = APPLICATION.WORKSHEETFUNCTION.MIN(RANGE("B:B"))
..SeriesCollection(1).ApplyDataLabels AutoText:=True
End With
End sub

Any help will be deeply appreciated. Thanks in advance.

Skydrive link for the sample: http://cid-9aa00329a92b0b2c.office.live.com/view.aspx/Graph Issue/Sample.xlsx

Skydrive link for the original ABAP code : http://cid-9aa00329a92b0b2c.office.live.com/view.aspx/Graph Issue/ABAP^_Code.docx

 

[Vacuum Sealed]

Hi Kush

you could try adding this:

Dim myDate As String
myDate = Format(Date, "dd-mmm-yyyy")

so replace this:
..axes(xlCategory, xlprimary).axisTITLE.CHARACTERS.TEXT = "Date"

with
..axes(xlCategory, xlprimary).axisTITLE.CHARACTERS.TEXT = myDate

HTH

Mick