PDA

View Full Version : Excel Office Automation


ABC123

stecenko
09-09-2014, 10:05 PM
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
09-09-2014, 11:18 PM
http://msgboard.alphasoftware.com/alphaforum/showthread.php?65035-Outlook-Calendar-and-Tasks&highlight=createObject

Hope that helps

Stan Mathews
09-09-2014, 11:21 PM
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
09-10-2014, 01:02 PM
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
09-10-2014, 02:02 PM
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
09-10-2014, 02:03 PM
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
09-10-2014, 03:02 PM
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.