Microsoft Office Automation with Visual FoxPro

2017-07-07 02:10:07

Adding shapes

Like Word and PowerPoint, Excel can add graphics to the worksheet. Excel (well, actually Office) has a robust set of Shape objects that s available to any Office application. Chapter 5, "Intermediate Word," covered how Word deals with the Shape object, and Chapter 10, "PowerPoint Basics," goes into immense detail on the Shape object for PowerPoint. We ll cover the basics of adding a Shape object here, and point you to the PowerPoint chapter for the details.

The available shapes, called AutoShapes, provide much more than just basic drawing shapes like rectangles and circles. There are many decorative shapes, such as arrows, stars, and banners. Flowchart symbols are also available, as are many kinds of callouts. Any of these shapes can be added to a worksheet using the AddShape method of the Shapes collection. AutoShapes are used to create diagrams and illustrations, or just spruce up your worksheet.

To interactively add a shape to your worksheet, select Insert|Picture|AutoShapes from the menu to activate the AutoShapes toolbar, shown in Figure 9. The buttons represent the main categories of shapes, and clicking on each one opens another menu of buttons that shows the available shapes for that main category of shapes.

Figure 9. The AutoShapes toolbar. This toolbar appears whenever you choose Insert|Picture|AutoShapes from the menu.

To add a shape programmatically, use the AddShape method of the Shapes collection. This is the syntax for adding an AutoShape:

oShape = oSheet.Shapes.AddShape( nType, nLeft, nTop, nWidth, nHeight )

The first parameter is a numeric constant that indicates one of the 140 shapes available. Table 2 lists some of the available constants. Note that their prefix is "mso," which denotes that they are available to all Office applications, not just PowerPoint. The next two parameters specify the upper-left corner of the rectangular box that contains the shape (in points, of course). The final two parameters determine the width and height of the object, in points. This rectangular box, which contains the shape, is called the bounding box.

The following lines of code add an arrow to the current spreadsheet. It is located half an inch from the top and left, and it s one inch long and one-half inch high. See Figure 10 for the results.

#DEFINE msoShapeRightArrow 33

oSheet.Shapes.AddShape(msoShapeRightArrow, 36, 36, 72, 36)

Each Shape object has a number of properties, some of which contain formatting objects. Table 3 shows the most important Shape properties.

Table 2. A sampling of AutoShape constants and their values.

Shape constant	Value
msoShape5PointStar	92
msoShapeArc	25
msoShapeBalloon	137
msoShapeCube	14
msoShapeDownArrow	36
msoShapeLeftArrow	34
msoShapeLineCallout1	109
msoShapeNoSymbol	19
msoShapeOval	9
msoShapeParallelogram	2
msoShapeRectangle	1
msoShapeRightArrow	33
msoShapeRoundedRectangle	5
msoShapeUpArrow	35

Table 3. Shape properties and objects.

Property	Type	Description
AutoShapeType	Numeric	One of the AutoShape constants, some of which are listed in Table 2.
Height	Numeric	The height of the shape, in pixels.
Width	Numeric	The width of the shape, in pixels
Left	Numeric	The position of the left side of the shape, from the left side of the worksheet, in pixels
Top	Numeric	The position of the top of the shape, from the top of the worksheet, in pixels.
TextFrame	Object	Reference to a TextFrame object. Among its properties and methods is the Character property, which points to a Character object. The Character object s Text property contains the text in the frame, and its Font property references a Font object to control the look of the text.
HorizontalFlip	Logical	True if the shape is flipped horizontally.
VerticalFlip	Logical	True if the shape is flipped vertically.
Fill	Object	Reference to a FillFormat object.
Line	Object	Reference to a LineFormat object.

Figure 10. Adding an AutoShape. The Shape collection s AddShape method adds one of more than 140 Office AutoShapes to your worksheet.

The TextFrame object contains properties to format text (if any) in the shape. The most important property is the Character property, which references a Character object. The Character object s Text property contains the text, and its Font property accesses a Font object to control the look of the text. The following code adds a text string and formats the font:

WITH oSheet.Shapes[1].TextFrame.Characters

.Text = "HERE"

WITH .Font

.Color = RGB(255, 0, 0) &&Red

.Name = "Times New Roman"

.Size = "12"

ENDWITH

Here s an interesting situation: for formatting cell borders, Chart objects, and other items in Excel, the Border object controls the border lines, and the Interior object controls the fill properties. However, Shapes are Office objects, and they use the LineFormat and FillFormat objects instead. Happily, they are similar. Sadly, they aren t the same.

Line objects store properties for color, style, transparency, pattern, weight, and arrowheads in the LineFormat object. Use the Shape object s Line property to access the LineFormat object. Table 4 shows many of the available properties for the LineFormat object.

The colors of the line are set by using the ForeColor and BackColor properties. These properties point to a ColorFormat object. A ColorFormat object has only two properties the one needed here is the RGB property (the other is the SchemeColor property; it s covered later). See "Formatting the Shape s border" in Chapter 10 for examples of how to use these properties.

The FillFormat object has many properties and is quite complex. To fill the shape with a color, use the FillFormat s ForeColor object. (Reminder: the FillFormat object is referenced by the Shape object s Fill property.)

oSheet.Shapes[1].Fill.ForeColor.RGB = RGB(192,192,192) && Light Gray

For more information on the FillFormat object, see the "Backgrounds" section in Chapter 11, where the FillFormat object is discussed in detail.

Table 4. LineFormat object properties.

Property	Type	Description
ForeColor	Object	The color of the line using a ColorFormat object.
BackColor	Object	The backcolor of a patterned line using a ColorFormat object. This is the secondary color of a patterned line, and is ignored if patterns are not used.
DashStyle	Numeric	The dash style of the line. Uses one of the following contants:
		msoLineDash	4	msoLineDashDot	5
		msoLineDashDotDot	6	msoLineLongDash	7
		msoLineLongDashDot	8	msoLineRoundDot	3
		msoLineSolid	1	msoLineSquareDot	2
Pattern	Numeric	The pattern applied to the line. The background of color is used as the background of the pattern. Use one of the many patterned constants, a few of which are listed here:
		msoPattern50Percent		7
		msoPatternLargeConfetti		33
		msoPatternLargeGrid		34
		msoPatternLightDownwardDiagonal		21
		msoPatternLightHorizontal		19
		msoPatternLightUpwardDiagonal		22
		msoPatternLightVertical		20
		msoPatternSmallGrid		23
		msoPatternWideDownwardDiagonal		25
		msoPatternZigZag		38
Style	Numeric	The style of the line, which can give the appearance of multiple lines.
		msoLineSingle	1	msoLineThickBetweenThin	5
		msoLineThickThin	4	msoLineThinThick	3
		msoLineThinThin	2
Transparency	Numeric	The degree of transparency of the line. The value ranges between 0.0 (opaque) and 1.0 (completely clear).
Weight	Numeric	The thickness of the line, in points.
Visible	Logical	Indicates whether the line is visible.

Категории