Chart control.Methods,Properties...Printing to Word , Excel..

[Brezhnev]

Hi all!
I am taking a proposition of Mike Lewis and opening new thread that (i hope) would put some light on work with MS Chart control.
I took Mikes class as a start. You can find free download on his web-site.
But as we all know ¡V there is always place for improvement
I added couple of new properties and methods.

Some of them :
ChartToWord()

LOCAL loWord,lcDoc

loWord=CREATEOBJECT("Word.Application&quot
lcDoc=THISFORM.cntSelectFile.txtFile.VALUE && users entry

loWord.DisplayAlerts = .F.
loWord.Documents.Open(lcDoc)

THISFORM.PrintChart.EditCopy() && copy chart to clipboard

loWord.Selection.PasteSpecial(.F., .F., 0, .F., 3)
loWord.Documents.Save(.T.) && save

loWord.Documents.Close(0)
loWord.quit()

loWord =.NULL.
RELEASE loWord

ChartToExcel()

*-- work in progress ( difficulties with syntax)
LOCAL loExcel,loExcelDoc,loExcelSheet

loExcel=CREATEOBJECT("Excel.Application&quot

&& Select sheet
loExcelDoc=loExcel.Application.WorkBooks.Add() && new excel sheet
loExcelSheet=loExcel.Application.Worksheets("Sheet1&quot.Activate && Select sheet

THISFORM.PrintChart.EditCopy() && copy chart to clipboard
loExcelDoc.Selection.PasteSpecial(.F., .F., 0, .F., 3) &&?????
loExcelDoc.PrintOut
loExcelDoc.Close(0)

*-- Mike ? Would you help , please ? ļ

SetChartParameters()

*-- Titles for axis
*-- x
IF !EMPTY(THIS.cXAxisTitle)
WITH THIS.PrintChart.Plot.Axis(0)
.AxisTitle.Text = ALLT(THIS.cXAxisTitle)
.AxisTitle.vtFont.Name = "Tahoma"
.AxisTitle.VtFont.Effect = 512
.AxisTitle.VtFont.Size = 12
.AxisTitle.VtFont.Style = 1
ENDWITH
ENDIF

*-- y
IF !EMPTY(THIS.cYAxisTitle)
WITH THIS.PrintChart.Plot.Axis(1)
.AxisTitle.Text = THIS.cYAxisTitle
.AxisTitle.vtFont.Name = "Tahoma"
.AxisTitle.VtFont.Effect = 512
.AxisTitle.VtFont.Size = 14
.AxisTitle.VtFont.Style = 1
ENDWITH
ENDIF

*-- Grid divisions
WITH THIS.PrintChart.Plot.Axis(1).ValueScale

IF THIS.nMajor<>0 OR THIS.nMinor<>0
.Auto = .F.
ENDIF

IF THIS.nMajor<>0
.MajorDivision = THIS.nMajor
ENDIF

IF THIS.nMinor<>0
.MinorDivision = THIS.nMinor
ENDIF


ENDWITH

*-- Pen

WITH THIS.PrintChart.Plot.Axis(1).AxisGrid
.MajorPen.Width = IIF(THIS.nMajorPen<>0, THIS.nMajorPen ,2) && does¡¦t work !
.MinorPen.Width = IIF(THIS.nMinorPen<>0, THIS.nMinorPen ,1)
ENDWITH

If you have anything to add -- please do ... on my side ..each time time something will start to work -- i'll post it here.

thanks to everybody for participation

 

[Brezhnev]

ok ...here is some code that will allow you to add text and a chart and a table to word document (some of the code borrowed from Knowledge Base):

*----------------
#DEFINE wdToggle 9999998
#DEFINE wdTableRight -999996
#DEFINE wdAlignParagraphLeft 0
#DEFINE wdAlignParagraphCenter 1
#DEFINE wdAlignParagraphJustify 3
#DEFINE wdHorizontalPositionMargin 0
#DEFINE wdCollapseEnd 0
#DEFINE wdCollapseStart 1
#DEFINE wdParagraph 4

LOCAL loWord,loDoc,loRange,loTable

loWord = CREATEOBJECT(&quot;Word.Application&quot
loWord.Visible = .F.

*-- new document
loDoc = loWord.Documents.Add()

loRange = loDoc.Range()
loRange.Collapse(wdCollapseStart)

*-- Add text

WITH loRange
.ParagraphFormat.Alignment = wdAlignParagraphCenter
.Font.Size = 14
.Font.Name = &quot;Arial Black&quot;
.InsertAfter(&quot;Pay Item Register Analysis&quot
.MoveEnd(wdParagraph,1)
.Bold = .T.
.Collapse(wdCollapseEnd)
.InsertParagraphAfter()
.MoveEnd(wdParagraph,1)
.Bold = .F.
.Collapse(wdCollapseEnd)
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Font.Size = 12
.Font.Name = &quot;Times New Roman&quot;
.InsertParagraphAfter()
.InsertParagraphAfter()
.Collapse(wdCollapseEnd)
.ParagraphFormat.Alignment = wdAlignParagraphJustify
.InsertAfter(&quot;This is Pay Item Register Analysis. &quot;+;
&quot;Analysis were done for years 2001 and 2002. Enjoy!&quot
.Collapse(wdCollapseEnd)
ENDWITH

SELECT csrTest && your .cAlias
lnRecordsCount=_TALLY

loWord.ActiveDocument.Tables.Add(loRange, lnRecordsCount+2, 3, 1, 0)
*!* Arguments are Range, #rows, #cols, [DefaultTableBehavior,] [AutoFitBehavior])

loTable = loWord.ActiveDocument.Tables(1) && Assign a table object

LOCAL lnCounter
lnCounter=2

WITH loTable
.Columns.SetWidth(72,0) && 72 points/inch
.Rows.WrapAroundText = .T.
.Rows.RelativeHorizontalPosition = 0 && wdHorizontalPositionMargin
.Rows.HorizontalPosition = -999996 && wdTableRight
.Autoformat(2,.T.,.T.,.T.,.T.,.T.,.T.,.T.,.T.,.T.)
*!* (Format,ApplyBorders,ApplyShading,ApplyFont,ApplyColor,ApplyHeadingRows,
*!* ApplyLastRow,ApplyFirstColumn,ApplyLastColumn,AutoFit)


.Cell(1,2).Range.InsertAfter(&quot;2001&quot
.Cell(1,3).Range.InsertAfter(&quot;2002&quot

SELECT csrTest
SCAN

.Cell(lnCounter,1).Range.InsertAfter(csrTest.PayItemCode)

.Cell(lnCounter,2).Range.InsertAfter(TRANS(csrTest.Amount1,&quot;9,999,999.99&quot)
.Cell(lnCounter,3).Range.InsertAfter(TRANS(csrTest.Amount2,&quot;9,999,999.99&quot)

lnCounter=lnCounter+1

ENDSCAN

.Rows(lnCounter).Cells.Merge
.Cell(lnCounter,1).Range.InsertAfter(&quot;Yearly amounts by Pay Item &quot;+;
&quot;- in $ Millions&quot
.Cell(lnCounter,1).Range.MoveEnd(wdParagraph,1)
.Cell(lnCounter,1).Range.Bold = .T.
.Cell(lnCounter,1).FitText = .T.

.Rows(1).Shading.Texture = 200

ENDWITH

loRange = loTable.Range
loRange.Collapse(wdCollapseEnd) && Move insertion point beyond table

WITH loRange
.InsertAfter(&quot;The table goes before this sentence. &quot
.InsertAfter(REPLICATE(&quot;This is a long paragraph that &quot;+;
&quot;needs to wrap around a table that will fit in the &quot;+;
&quot;paragraph, aligned at the right margin. &quot;,5))
.InsertParagraphAfter()
.InsertParagraphAfter()

ENDWITH

loWord.Visible = .T. && Inspect the results
**loWord.Documents.Save(.T.)
loWord.Quit(.T.) && save and quit

*----------------

Can somebody please point me to the code for Excel automation in FoxPro. Thanks.

 

[Mike Lewis]

Brezhnev,

Your Word codes looks more or less OK to me.

With your Excel code, the PasteSpecial parameters are different from word. Try this:

loExcelDoc.Selection.PasteSpecial(&quot;Picture (Enhanced Metafile)&quot;,.F.,.F.)

I haven't tested that. I got the syntax by recording a macro in Excel and checking the generated VB code. This is always a good way of figuring out how to do Automation with Office. (Another good way is to use Intellisense in VFP 7.0 and above).

Your SetChartParameters() code also looks OK at a quick glance. Is it working correctly?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Brezhnev]

Mike,

Thanks a lot for a tip...i did't think about Intellisense because i am doing this changes to existent project in VFP6... But since i have VFP7 i can use it ....

Your line of the code does't work for me ..but i am still testing ( error -- short or long should be passed as a parameter)...

my code tested and it's all working , but &quot;Pen&quot;. Whatever I do it's still all the same width.

 

[Mike Lewis]

Brezhnev,

Sorry I don't know why the PasteSpecial gave that error. If I get time, I'll experiment a bit more with it. It's certainly easier with Intellisense, but this Office Automation stuff is often a matter of trial and error. At least, it is for me.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk