7.7 Output | Microsoft Office Automation with Visual FoxPro

Output

Once you have a good-looking spreadsheet, inevitably, your users will want it printed.

Page Setup

Excel has a robust set of features used to print out a page, as evidenced by Figure 10, the Page Setup dialog. Excel s Page Setup dialog corresponds to its PageSetup object. Each worksheet (and each chart) has its own PageSetup object.

Figure 10. Setting up the pages. This is one of four tabs of properties used when printing.

The PageSetup has 35 properties and one method that s robust! Quite a number of them are useful for everyday tasks. Table 5 shows most of the properties they are grouped by the tab on which they re found on the Page Setup dialog (a helpful order if you re using the macro recorder to help you).

Table 5. The PageSetup object s properties. Reminder: the properties listed as Logical or Numeric can be set with either logical or numeric values, but they should be tested against numeric values only.

Property	Type	Description
Orientation	Numeric	The orientation of printing on the paper. xlPortrait 1 xlLandscape 2
Zoom	Numeric or Logical	If numeric, applies the percentage (between 10 and 400) to scale the worksheet. If set to False (.F. or 0), FitToPagesTall and/or FitToPagesWide are used to scale the worksheet.
FitToPagesTall	Numeric or Logical	Scales the vertical print area to fit this many pages. If False (.F. or 0), no vertical scaling occurs. Ignored if the Zoom property is numeric and not zero.
FitToPagesWide	Numeric or Logical	Scales the horizontal print area to fit this many pages. If False (.F. or 0), no horizontal scaling occurs. Ignored if the Zoom property is numeric and not zero.
PaperSize	Numeric	One of about 45 preset paper sizes. Some of the most common are: xlPaperLetter 1 xlPaperLegal 5 xlPaperA4 9 xlEnvelope9 19
FirstPageNumber	Numeric	The page number used to start numbering pages. The default is 1.
TopMargin	Numeric	The distance from the top of the page to the top of the first line of text, in points.
HeaderMargin	Numeric	The distance from the top of the page to the top of the first line of text in the header, in points.
LeftMargin	Numeric	The distance from the left edge of the page to the left edge of the text, in points.
RightMargin	Numeric	The distance from the right edge of the page to the right edge of the text, in points.
BottomMargin	Numeric	The distance from the bottom edge of the page to the bottom line of text, in points.
FooterMargin	Numeric	The distance from the bottom edge of the page to the bottom line of the footer text, in points.
CenterHorizontally	Logical or Numeric	True (.T. or -1) to center the text horizontally on the page; False (.F. or 0) to start printing at the left margin.
CenterVertically	Logical or Numeric	True (.T. or -1) to center the text vertically on the page; False (.F. or 0) to start printing at the top margin.
LeftHeader	Character	These properties set the left, center, or right portions of the header or footer, as indicated. They can be set to a character string or codes, or a combination of the two. A complete set of codes is in the Excel VBA Help file under the topic "Formatting Codes for Headers and Footers." Prints the current date &D Prints the name of the file &F Prints the name of the active worksheet &A Prints the current page number &P Prints the ampersand character && Prints the total number of pages in the document &N
CenterHeader	Character
RightHeader	Character
LeftFooter	Character
CenterFooter	Character
RightFooter	Character
PrintArea	Character	The range of cells to print for example, "A1:Z40". If blank, it will print them all.
PrintGridlines	Logical or Numeric	True (.T. or -1) to print the cells gridlines, False (.F. or 0) to omit them.
BlackAndWhite	Logical or Numeric	True (.T. or -1) to print colors in high-contrast shades of gray for the best possible printing on a black and white printer; False (.F. or 0) to print in color, or to let the colors print in whatever shades of gray the printer is programmed for.
Draft	Logical or Numeric	True (.T. or -1) to print the data without graphics; False (.F. or 0) prints everything.
Order	Numeric	The order in which pages are printed and numbered when a worksheet is too big to fit on one page. xlDownThenOver 1 xlOverThenDown 2

These properties are pretty straightforward. Here s some sample code that ensures that the spreadsheet will print on one sheet, with half-inch margins, in landscape orientation, with page numbers in the upper-left header.

#DEFINE xlLandscape 2

#DEFINE autoIn2Pts 72

oSheet.PageSetup.Orientation = xlLandscape

oSheet.PageSetup.FitToPagesTall = 1

oSheet.PageSetup.FitToPagesWide = 1

oSheet.PageSetup.TopMargin = .5 * autoIn2Pts

oSheet.PageSetup.BottomMargin = .5 * autoIn2Pts

oSheet.PageSetup.LeftMargin = .5 * autoIn2Pts

oSheet.PageSetup.RightMargin = .5 * autoIn2Pts

oSheet.PageSetup.LeftHeader = "Page &P"

Print Preview

In some of our Automation applications, we display the final document in Print Preview mode. This lets the user analyze the data, then decide whether it s important enough to print, or if the data needs more review. Excel s printing is based on the worksheets, not the workbooks. You ll invoke the PrintPreview method from the Sheet object. This also means that you will see one sheet in the PrintPreview; you cannot scroll between sheets in a workbook (while Excel users are quite used to this, it comes as a shock to VFP developers, who can scroll through the whole report, and Word users, who can scroll through the whole document).

To invoke the Print Preview mode, use the sheet s PrintPreview method:

oSheet.PrintPreview()

However, this puts FoxPro in a wait state; the user needs to click on either the flashing window title or the flashing button on the menu bar. Once the user has done so, the PrintPreview window is displayed. But FoxPro cannot continue until the PrintPreview window is closed. Be sure that the Excel Application object s window is in a Normal state before issuing the PrintPreview command, and even then, the user may not see any of the flashing items and assume that the app has hung.

An alternative syntax, using the PrintOut method and the PrintPreview parameter, still suffers from the same problem.

oSheet.PrintOut(,,,.T.)

There is a way to display the PrintPreview screen, but it requires the user to close the PrintPreview window before your FoxPro app can continue. It uses some API calls to manipulate the windows. The FindWindowA function obtains a window handle to the application based on its class and its window name. Table 6 shows the classes of the Office applications (valid for 97 and 2000), as well as the various versions of FoxPro. The SetWindowPos function sets the window to be the topmost window (or not) based on the window handle. You must remember to set the window back to NoTopMost, to ensure that other apps can be brought forward.

Table 6. Class names used in FindWindowA. Passed to the API along with the window caption, it returns a valid window handle, which you can use to manipulate the window with other APIs.

Application	Class
Excel 97 and 2000	XLMAIN
Outlook 97 and 2000	rctrl_renwnd32
PowerPoint 97	PP97FrameClass
PowerPoint 2000	PP9FrameClass
Word 97 and 2000	OpusApp
VFP 3.0 and 5.0	Fox4000001
VFP 6.0	VFP66400000

#DEFINE swp_nosize 1

#DEFINE swp_nomove 2

#DEFINE hwnd_topmost -1

#DEFINE hwnd_notopmost -2

* FindWindowA returns the window handle from the window's caption

DECLARE LONG FindWindowA IN WIN32API STRING class, STRING title

* SetWindowPos moves the window to the top, using the window handle

DECLARE SetWindowPos IN WIN32API LONG HWND, LONG hwndafter, ;

LONG x, LONG Y, LONG cx, LONG cy, LONG flags

* Capture the window handles for both Excel and VFP.

hWndXL = FindWindowA("XLMAIN", oExcel.Caption)

hWndVFP = FindWindowA("VFP66400000", _VFP.Caption)

* Bring VFP to the top, and instruct the user to

* close PrintPreview

= SetWindowPos(hWndVFP, hwnd_topmost, 0,0,0,0, swp_nosize + swp_nomove)

= MessageBox("Close this MessageBox, and then Excel will come forward." + ;

CHR(13) + "Close Print Preview when ready to return to FoxPro.")

* Now bring the Excel window to the top

= SetWindowPos(hWndVFP, hwnd_notopmost, 0,0,0,0, swp_nosize + swp_nomove)

= SetWindowPos(hWndXL, hwnd_topmost, 0,0,0,0, swp_nosize + swp_nomove)

* Issue the PrintPreview method. Make sure your code

* can tolerate a wait state, and that the user knows

* to close the PrintPreview window to continue

oSheet.PrintPreview()

* Now put the Excel window to NoTopMost, so VFP can come forward

= SetWindowPos(hWndXL, hwnd_notopmost, 0,0,0,0, swp_nosize + swp_nomove)

* Bring the VFP window forward, then set it NoTopMost so other

* applications can be brought forward.

= SetWindowPos(hWndVFP, hwnd_topmost, 0,0,0,0, swp_nosize + swp_nomove)

= SetWindowPos(hWndVFP, hwnd_notopmost, 0,0,0,0, swp_nosize + swp_nomove)

This may work for many applications, especially if you put up a message box explaining what to do just before you call this routine.

Printing

A little less problematic than PrintPreview is printing the spreadsheet. The bulk of the properties that control printing are set in the PageSetup object. Once you re ready to print, the PrintOut method is called. While it has some parameters to control the start and end page numbers, the number of copies, and the device to which the output is sent (printer name, preview, or filename), it does its job just fine without any parameters. It assumes you want it all printed to the default printer.

oSheet.Printout()

The Excel VBA Help file does a fine job of explaining the parameters; see the "PrintOut Method" topic.

Saving the data in different formats

Excel 2000 has constants defined for 41 different formats (though all might not be available, depending on several factors, like the language [such as U.S. English], or whether the user has installed them, and so on). Quite a number of these formats are variations on the spreadsheet theme, including eight versions of Excel, and seven WK* versions. Also available are formats for CSV, SYLK, DBF, DIF, text, and HTML, among others.

The SaveAs method takes many parameters, though the most useful are the first two. The first parameter is the new filename. The filename must be fully qualified; Excel has no knowledge of VFP s SET DEFAULT setting. The second parameter is a numeric value that represents the format. Of course, we need the table of constants, shown in Table 7. We ll show a few you can find the rest in the Object Browser.

You ll notice the xlWorkbookNormal value. Use this to save a copy of the file in the current version s format.

Table 7. File format constants for Excel. Excel supports saving in many different formats. Here are a few of the 41 in Excel 2000.

Constant	Value	Constant	Value
xlCSV	6	xlSYLK	2
xlDBF3	8	xlTextWindows	20
xlDIF	9	xlWK4	38
xlExcel9795	43	xlWorkbookNormal	-4143