Excel Graph script

[the G]

I'm creating a jpg from a comma seperated input file.
The file is in the style

A,10,20
B,30,50
C1,21,40
C2,35,70
E,36,50

So has three values and is alphabetically listed with the first.

I'm creating the Excel graph with the following script

=================================================
DIM x(100)
DIM y(100)
DIM z(100)

vDate=Date()
LastMonth=DateAdd("m",-1,vDate)
vYear=Year(LastMonth)
vMonth1=Month(LastMonth)
vMonth=MonthName(vMonth1)
If vMonth1<10 then vMonth1="0"&vMonth1

Set oFS=CreateObject("Scripting.FileSystemObject")
inFILE="data.txt"
Set inputFILE=oFS.OpenTextFile(inFILE)
linecount=0

While Not inputFILE.AtEndOfStream
linecount=linecount+1
vline = inputFILE.Readline
If Len(vline)>0 then
splitline=split(vline,",")
x(linecount)=splitline(0)
y(linecount)=splitline(1)
z(linecount)=splitline(2)
End If
WEnd


inputFILE.Close
Set inputFILE=Nothing
Set oFS=Nothing

set loChartSpace = WScript.CreateObject("OWC10.ChartSpace")
set oConst = loChartSpace.Constants
set loGraph = loChartSpace.Charts.Add()
loGraph.Type = 3

loGraph.HasLegend = True
loGraph.PlotArea.Interior.Color = "azure"

loChartSpace.HasChartSpaceTitle = True
loChartSpace.ChartSpaceTitle.Caption = "Values - "& vMonth & ", "&vYear
loChartSpace.ChartSpaceTitle.Font.Bold = True

Set objAxis = loGraph.Axes(oConst.chAxisPositionBottom)

'Axis Properties
With objAxis
..HasTitle = True
..Scaling.Maximum=100
..Title.Caption = "Percent"
..Font.Size = 6
End With

Set objAxis = loGraph.Axes(oConst.chAxisPositionLeft)

'Axis Properties
With objAxis
..HasTitle = True
'.Scaling.Maximum=100
..Title.Caption = "Values"
..Font.Size = 8
End With


loGraph.SeriesCollection.Add()
loGraph.SeriesCollection(0).Caption = "In"
loGraph.SeriesCollection(0).Interior.Color = "#66ccff"
loGraph.SeriesCollection(0).SetData oConst.chDimCategories,
oConst.chDataLiteral, x
loGraph.SeriesCollection(0).SetData oConst.chDimValues,
oConst.chDataLiteral, y

loGraph.SeriesCollection.Add()
loGraph.SeriesCollection(1).Caption = "Out"
loGraph.SeriesCollection(1).Interior.Color = "#000099"
loGraph.SeriesCollection(1).SetData oConst.chDimValues,
oConst.chDataLiteral, z

lcFile = "D:\rank\graphs\"&vYear&vMonth1&".jpg"

loChartSpace.ExportPicture lcFile,"jpg",800,600
=========================================================

Most of which has been found on the internet (so if you see some of your own
code in there - thanks!!!!!!!)

The thing is I'm setting loGraph.Type = 3 which is sideways columns.
The problem I've got is that the values in the first column of the csv
(alphabetical) are displayed in reverse (top to bottom) on the graph.
So the graph show in the left column :
E
C2
C1
B
A

It seems to be OK if I choose loGraph.Type = 0 (columns going up) with the
bottom axis showing A,B,C1,C2,E

Is there any way I can flip the data simply so when I've got loGraph.Type =
3 the left axis is alphabetically downwards?

Many Thanks

 

[Tushar Mehta]

Keeping in mind that OWC is not the same as XL (totally different
animal)...

XL has the option of specifying that an axis has values in reverse.

.Axes(xlCategory).ReversePlotOrder = True

You may be able to use a similar (same?) property in OWC.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions