Excel text box & lines

[wbenhart]

I am trying to interact with excel and need some help getting some code to work correctly. I can create the text box, but I am unable to enter any text to it. Also I can create a line, but can not create the arrow on the end. Does anyone have any idea how the code works?


Example:

DEFINE VARIABLE chExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkBook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkSheet AS COM-HANDLE NO-UNDO.
CREATE 'Excel.Application' chExcel NO-ERROR.

ASSIGN
chWorkBook = chExcel:WorkBooks:ADD(1)
chExcel:VISIBLE = TRUE
chWorkSheet = chExcel:Sheets:Add(,chWorksheet,1,).
chWorkSheet:Shapes:AddTextbox(1, 374, 167, 64, 25):SELECT.
chWorkSheet:Shapes:AddLine(330, 173.25, 373.5, 179.25):SELECT.

RELEASE OBJECT chWorkSheet.
RELEASE OBJECT chExcel.
RELEASE OBJECT chWorkBook.

/*
ActiveSheet:Shapes:AddTextbox(msoTextOrientationHorizontal, 374.25, 167.25, 64.5, 25.5):Select
Selection:Characters:Text = "hello"
Selection:Characters(Start:=1, Length:=5.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.Shapes.AddLine(330#, 173.25, 373.5, 179.25).Select
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
Selection.ShapeRange.Line.EndArrowheadLength = msoArrowheadLengthMedium
Selection.ShapeRange.Line.EndArrowheadWidth = msoArrowheadWidthMedium
*/

Thanks for any help....

Wayne Benhart

 

[Simon Sweetman]

Best bet with working out these sort of things is to record a macro that does what you want and then edit it. If you need values for constants (like msoArrowheadLengthMedium) just assign a cell value to the constant in your macro .

DEFINE VARIABLE chExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkBook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkSheet AS COM-HANDLE NO-UNDO.
CREATE 'Excel.Application' chExcel NO-ERROR.
 
ASSIGN
chWorkBook = chExcel:WorkBooks:ADD(1)
chExcel:VISIBLE = TRUE
chWorkSheet = chExcel:Sheets:Add(,chWorksheet,1,).
chWorkSheet:Shapes:AddTextbox(1, 374, 167, 64, 25):SELECT.
chExcel:Selection:Characters:Text = "This is a test".
chWorkSheet:Shapes:AddLine(330, 173.25, 373.5, 179.25):SELECT.
chExcel:Selection:ShapeRange:Line:BeginArrowheadLength = 2. /* msoArrowheadLengthMedium */
chExcel:Selection:ShapeRange:Line:BeginArrowheadWidth = 2. /* msoArrowheadWidthMedium */
chExcel:Selection:ShapeRange:Line:BeginArrowheadStyle = 3. /* msoArrowheadOpen */
 
RELEASE OBJECT chWorkSheet.
RELEASE OBJECT chExcel.
RELEASE OBJECT chWorkBook.