Creating secondary Y axis in Excel from vbscript w/i a web page

[Brian Hman]

Hi,

I apologize if this isn't the appropriate newsgroup for this post.

I have a web page that has vbscript that opens excel on the client's system,
places data into, excel, and then creates a graph. I need to create a second
Y axis so I represent values with the $0.00 excel format. I haven't been able
to find any code to do this. Any help is appreciated.

Here's the code I have so far (if you run it--it opens excel, places the
data, and then creates a graph with only one y axis):

<script language="VBScript">
Dim app
Set app = CreateObject("Excel.Application")
app.Visible = true
Dim oBook
Set oBook = app.Workbooks.Add
Dim oSheets
Set oSheets = oBook.Sheets
Dim oSheet
Set oSheet = oSheets(1)
oSheet.Cells(1,1).Value ="REPORT_DTM"
oSheet.Cells(1,2).Value ="TEXT_RESULT"
oSheet.Cells(1,3).Value ="TEXT_TEST_RESULT"
oSheet.Cells(1,4).Value ="PAP_TIER_1"
oSheet.Cells(1,5).Value ="PAP_TIER_2"
oSheet.Cells(1,6).Value ="PID_CD"
oSheet.Cells(1,7).Value ="PRODUCT_DSC"
oSheet.Cells(2,1).Value ="09/01/04"
oSheet.Cells(2,2).Value ="1.27"
oSheet.Cells(2,3).Value ="3.79"
oSheet.Cells(2,4).Value ="0"
oSheet.Cells(2,5).Value ="0"
oSheet.Cells(2,6).Value ="BI-1A"
oSheet.Cells(2,7).Value ="UNEs and Resale Aggregate"
oSheet.Cells(3,1).Value ="10/01/04"
oSheet.Cells(3,2).Value ="1.34"
oSheet.Cells(3,3).Value ="3.63"
oSheet.Cells(3,4).Value ="0"
oSheet.Cells(3,5).Value ="0"
oSheet.Cells(3,6).Value ="BI-1A"
oSheet.Cells(3,7).Value ="UNEs and Resale Aggregate"
oSheet.Cells(4,1).Value ="11/01/04"
oSheet.Cells(4,2).Value ="1.28"
oSheet.Cells(4,3).Value ="3.25"
oSheet.Cells(4,4).Value ="0"
oSheet.Cells(4,5).Value ="0"
oSheet.Cells(4,6).Value ="BI-1A"
oSheet.Cells(4,7).Value ="UNEs and Resale Aggregate"
oSheet.Cells(5,1).Value ="12/01/04"
oSheet.Cells(5,2).Value ="1.46"
oSheet.Cells(5,3).Value ="3.72"
oSheet.Cells(5,4).Value ="0"
oSheet.Cells(5,5).Value ="0"
oSheet.Cells(5,6).Value ="BI-1A"
oSheet.Cells(5,7).Value ="UNEs and Resale Aggregate"
oSheet.Cells(6,1).Value ="01/01/05"
oSheet.Cells(6,2).Value ="14.63"
oSheet.Cells(6,3).Value ="3.50"
oSheet.Cells(6,4).Value ="0"
oSheet.Cells(6,5).Value ="0"
oSheet.Cells(6,6).Value ="BI-1A"
oSheet.Cells(6,7).Value ="UNEs and Resale Aggregate"
oSheet.Cells(7,1).Value ="02/01/05"
oSheet.Cells(7,2).Value ="1.57"
oSheet.Cells(7,3).Value ="3.21"
oSheet.Cells(7,4).Value ="0"
oSheet.Cells(7,5).Value ="0"
oSheet.Cells(7,6).Value ="BI-1A"
oSheet.Cells(7,7).Value ="UNEs and Resale Aggregate"
oSheet.Cells(8,1).Value ="03/01/05"
oSheet.Cells(8,2).Value ="1.34"
oSheet.Cells(8,3).Value ="3.28"
oSheet.Cells(8,4).Value ="0"
oSheet.Cells(8,5).Value ="0"
oSheet.Cells(8,6).Value ="BI-1A"
oSheet.Cells(8,7).Value ="UNEs and Resale Aggregate"
oSheet.Cells(9,1).Value ="04/01/05"
oSheet.Cells(9,2).Value ="1.32"
oSheet.Cells(9,3).Value ="3.07"
oSheet.Cells(9,4).Value ="0"
oSheet.Cells(9,5).Value ="0"
oSheet.Cells(9,6).Value ="BI-1A"
oSheet.Cells(9,7).Value ="UNEs and Resale Aggregate"
oSheet.Cells(10,1).Value ="05/01/05"
oSheet.Cells(10,2).Value ="1.19"
oSheet.Cells(10,3).Value ="3.07"
oSheet.Cells(10,4).Value ="0"
oSheet.Cells(10,5).Value ="0"
oSheet.Cells(10,6).Value ="BI-1A"
oSheet.Cells(10,7).Value ="UNEs and Resale Aggregate"
oSheet.Cells(11,1).Value ="06/01/05"
oSheet.Cells(11,2).Value ="1.28"
oSheet.Cells(11,3).Value ="3.30"
oSheet.Cells(11,4).Value ="0"
oSheet.Cells(11,5).Value ="0"
oSheet.Cells(11,6).Value ="BI-1A"
oSheet.Cells(11,7).Value ="UNEs and Resale Aggregate"
oSheet.Cells(12,1).Value ="07/01/05"
oSheet.Cells(12,2).Value ="1.27"
oSheet.Cells(12,3).Value ="3.28"
oSheet.Cells(12,4).Value ="0"
oSheet.Cells(12,5).Value ="0"
oSheet.Cells(12,6).Value ="BI-1A"
oSheet.Cells(12,7).Value ="UNEs and Resale Aggregate"
oSheet.Cells(13,1).Value ="08/01/05"
oSheet.Cells(13,2).Value ="0.92"
oSheet.Cells(13,3).Value ="3.10"
oSheet.Cells(13,4).Value ="0"
oSheet.Cells(13,5).Value ="0"
oSheet.Cells(13,6).Value ="BI-1A"
oSheet.Cells(13,7).Value ="UNEs and Resale Aggregate"
Dim exportRange
Set exportRange = oSheet.Range(oSheet.Cells(2,4), oSheet.Cells(13,4))
exportRange.NumberFormat = "$0.00"
Set exportRange = oSheet.Range(oSheet.Cells(2,5), oSheet.Cells(13,5))
exportRange.NumberFormat = "$0.00"
'On Error Resume Next
Dim SoureRange
Set SourceRange = oSheet.Range("A2:A13,B2:B13")
Dim oChart
Set oChart = oSheet.Parent.Charts.Add
oChart.ChartWizard SourceRange, 4, , 2, 1, 0, 2, ""

'oChart.SeriesCollection.NewSeries.Values =
oSheet.Range(oSheet.Cells(2,2),oSheet.Cells(13,2))
oChart.SeriesCollection(1).Name = "BI Wholesale"
oChart.SeriesCollection.NewSeries.Values =
oSheet.Range(oSheet.Cells(2,5),oSheet.Cells(13,5))
oChart.SeriesCollection(2).Name = "BI Tier 2"

</script>

 

[Peter Huang [MSFT]]

Hi

Thanks for your detailed sample.
So far I did not understanding your question very much.
Can you do it with Excel UI?
If so, I think you may try to record the macro to see what it will be done
via macro.
Or you can send me the xls file about what you are going to do via removing
"online" from my email address.
Thanks!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Brian Hman]

Hi Peter,

Telling me to use the macro recorder gave me the vbscript code I needed.

By simply adding oChart.SeriesCollection(2).AxisGroup = 2 to my code, I was
able to get the the series onto the secondary axis like I wanted.

Thanks for the hint.

Brian Hman

 

[Peter Huang [MSFT]]

Hi

You are welcomed!
I am glad that my suggestion helps you!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.