Creting an Excel Graph with the following script

T

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 column.

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(chAxisPositionLeft) :
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?

It has been suggested that .Axes(xlCategory).ReversePlotOrder = True might
work - but I can't get it, nor any combination of it to sort the column!

Many Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top