Excel Office Automation

[stecenko]

Hi All,

The following code when run as an xBasix script works just fine except for the last line: the second series is not changed to a dotted line.

However, if I copy/paste the code to a Visual Foxpro prg and change

loEx=ole.getObject("", "Excel.Application")

to

loEx=createObject("Excel.Application")

it works just fine and the second series has a dashed line.

Why?

In both cases, I'm sending commands to Excel, who does all the work. It should not make any difference if the commands are sent by Alpha or VFP. But it does.

Does any one have an explanation or, even better, a work around?

I noticed that Stan, in his samples, often selects a range of cells. But I tried doing it that way, but with no joy.

I really, really want to have the lines in different styles. They look great when seen on the screen, but not so great when printed.

dim loEx as p
loEx=ole.getObject("", "Excel.Application")
loEx.visible=.T.
loEx.sheetsInNewWorkBook=1
loWorkbook=loEx.Workbooks.add()
loSheet=loWorkbook.worksheets(1)
loSheet.name="Testing Chart"

for dlnI=2 to 9
	loCol=loSheet.columns(dlnI)
	loCol.columnWidth=15
next dlnI

loSheet.columns(3).columnWidth=15
loSheet.range("B5")="Dec"
loSheet.range("C5")="Jan"
loSheet.range("c5")="Feb"
loSheet.range("d5")="March"
loSheet.range("e5")="April"
loSheet.range("f5")="May"
loSheet.range("g5")="June"
loSheet.range("h5")="july"
loSheet.range("I5")="Aug"

loSheet.range("A6")="Orders"
loSheet.range("B6")=200
loSheet.range("c6")=220
loSheet.range("d6")=230
loSheet.range("e6")=240
loSheet.range("f6")=100
loSheet.range("g6")=120
loSheet.range("h6")=150
loSheet.range("i6")=190

loSheet.range("A7")="Quotes"
loSheet.range("B7")=250
loSheet.range("c7")=220
loSheet.range("d7")=50
loSheet.range("e7")=270

	loChart=loSheet.ChartObjects.add(10, 10, 900,500)
	loChart.Activate()
	cSourceRange = "='"+loSheet.name+"'!$A$5:$I$7"
	nChartType=4		' line 
	nChartFormat=2		' line with no data markers
	nPlotBy=1			' rows
	nCategoryLabels=1	
	nSeriesLabels=1
	lHasLegend=.t.
	cTitle="Long Title"+chr(10)+"(Domestic 8 year 52 Week Average)"
	cCategoryTitle="Dates"
	cExtraTitle="(Domestic 8 year 52 Week Average)"
	cValueTitle="$ Value"
	loEx.ActiveChart.ChartWizard(cSourceRange, nChartType, nChartFormat, nPlotBy, nCategoryLabels, nSeriesLabels, lHasLegend, cTitle, cCategoryTitle, cValueTitle, cExtraTitle ) 

	loEx.ActiveChart.ChartArea.Format.Line.visible=.f.

	loEx.ActiveChart.SeriesCollection(2).Format.Line.DashStyle = 7

[mvaughn]

Re: Excel Office Automation

http://msgboard.alphasoftware.com/al...t=createObject

Hope that helps

[Stan Mathews]

Re: Excel Office Automation

The implementation of lines and borders seems to me to be imperfect. When I try to format Excel cells with borders (outline and diagonal) I am not able to make it work. I am able to write a macro into a spreadsheet and run it which does the necessary formatting. Haven't been able to determine a reason why the difference.

I'll take a look at your code tomorrow. It would help if you attach a sample spreadsheet upon which your code would work.

[stecenko]

Re: Excel Office Automation

Hi Stan,

I didn't want to use a macro, but can live with one if I must.

I was just surprised that the code worked in VFP, but not in Alpha. I thought the commands were just 'sent' to the ole object, but if there is an imperfect implentation then that must mean that Alpha is not quite sending the command properly. Would this be considered a 'bug' in Alpha?

The code I included can be run from an Interactive window; it creates a spreadsheet.


Michael,

I saw that post. The problem there was incorrect syntax. But my code has correct syntax (or at least VFP is ok with it). If I set debug(1), Alpha doesn't complain about the syntax.

[stecenko]

Re: Excel Office Automation

OK, it works. I looked again at Stan's old post (cklist_new.xls) on adding stuff to a spreadsheet.

Instead of this...
loEx.ActiveChart.SeriesCollection(2).Format.Line.DashStyle = 7
do this...
loEx.ActiveChart.SeriesCollection(2).select()
loEx.Selection.Format.Line.DashStyle = 7

This two-step approach also worked for date formatting.
loSheet.range(dlcCol+"6").select()
loEx.Selection.NumberFormat="[$-1009]d-mmm-yy;@"

[mvaughn]

Re: Excel Office Automation

Richard, if you can bring up your data in a grid, you can export that data as an Excel spreadsheet by using an Action Button.
Don't know why I didn't think of that earlier.

[stecenko]

Re: Excel Office Automation

Yes, but you can do anything fancy with it.

With an export, you get one row per record. But I need 8 rows per record and a chart.

Office automation is great for this. It is just taking me a while to figure out the 'design features' of Alpha's implementation of automation.