#1   Report Post  
the G
 
Posts: n/a
Default Excel Graph script

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



  #2   Report Post  
Tushar Mehta
 
Posts: n/a
Default

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

In article ,
says...
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do u set the PLOT AREA to nearest mm in Excel Scatter Graph . fh Charts and Charting in Excel 0 June 3rd 05 01:07 PM
Need to get slope of peaks on excel graph Capillod Excel Discussion (Misc queries) 3 May 13th 05 03:58 PM
Need to get slope of peaks on excel graph Capillod Charts and Charting in Excel 3 May 13th 05 03:16 PM
why is the y-axis label in the Excel graph always messed up? Song Charts and Charting in Excel 3 April 2nd 05 04:51 AM
I'm trying to get a custom graph in Excel LoriLogan Charts and Charting in Excel 1 March 7th 05 08:30 PM


All times are GMT +1. The time now is 11:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"