Using JS to Create an Excel Chart

[KevinFSI]

I have an EXCEL workbook that I've created using JavaScript and ActiveX. So far everything has gone well. One of the worksheets is a chart. Things have gone well there too. I have one problem though. When setting the one of the bars on the chart to a pattern rather than a color, I get stuck. Here is how the line appears as VBA:

Selection.Fill.Patterned Pattern:=msoPatternShingle

I have tried several different ways to translate this to JS, but haven't had ANY luck. (Note xlApp is my excel object) Here are the various ways I've tried this.

xlApp.Selection.Fill.Patterned(47);
xlApp.Selection.Fill.Patterned = 47;
xlApp.Selection.Fill.Patterned(null);
xlApp.Selection.Fill.Patterned(msoPatternShingle);
xlApp.Selection.Fill.Patterned = msoPatternShingle;
xlApp.Selection.Fill.Patterned.msoPatternShingle = true;

I'm about to lose it! PLEASE HELP ME!

Kevin
slanek@ssd.fsi.com

"Life is what happens to you while you're busy making other plans."
- John Lennon

 

[dwarfthrower]

xlApp.Selection.Fill.Patterned = 'msoPatternShingle';

msoPatternShingle is a CONST in VBA, ie it can be used interchangeable with a certain number. JS doesn't have the same CONST declaration so you need quotes to stop JS trying to interpret it as some vairable you've forgotten to assign a value to.

 

[ggriffit]

Check out

http://www.greggriffiths.org/webdev/both/excel

which has a tutorial on this, there is also a link to a list of the constants used in Excel and their actual values, which will solve your problems at

http://www.greggriffiths.org/webdev/both/excel/conclusion.html

 

[KevinFSI]

Still a nogo. Check out this block of code.

xlWb.ActiveChart.SeriesCollection(4).Select();
xlApp.Selection.Border.Weight = 2;
xlApp.Selection.Border.LineStyle = -4105;
xlApp.Selection.Shadow = false;
xlApp.Selection.Fill.Patterned = 'msoPatternShingle';
xlApp.Selection.Fill.Visible = true;
xlApp.Selection.Fill.ForeColor.SchemeColor = 17;
xlApp.Selection.Fill.ForeColor.SchemeColor = 2;

The 'msoPatternShingle' line is the only one that throws an error. If I drag this entire block into the watch window in debugger, the 'mso...' line says "Object doesn't support this property or method."

Kevin
slanek@ssd.fsi.com

"Life is what happens to you while you're busy making other plans."
- John Lennon

 

[SashiRaveendran]

cant you just assign it a number rather than 'msoPatternShingle'

 

[KevinFSI]

You would think.

If you look at msoPatternShingle in the Object Browser in the VB editor in Excel, you'll see the value for msoPatternShingle is 47, which is why, in my original post, there are several references to the number 47.

Kevin
slanek@ssd.fsi.com

"Life is what happens to you while you're busy making other plans."
- John Lennon

 

[SashiRaveendran]

weird???
may be its the calling convention used?
dont suppose you are meant to use a set statement?

 

[Einstein47]

I know really nothing about this, but it seems that you have two parameters that you need to worry about. The first being that the fill is to be a pattern. And the second is the value of the pattern.

Again - I may be barking up the wrong tree - in the wrong neighborhood.

Einstein47
("As a cure for worrying, work is better than whiskey." - Thomas Edison)