How to Pass Data from FoxPro to Another Application

• Microsoft FoxPro for Macintosh, versions 2.5b, 2.5c

SUMMARY

Apple Events and dynamic data exchange (DDE) are analogous forms of high- level interapplication communication (IAC) provided by System 7 on the Macintosh and by Windows on the IBM-compatible PC, respectively.

Microsoft FoxPro for Macintosh does not provide the same level of access to IAC that its Windows counterpart achieves through DDE. While FoxPro for Windows works with a fairly sophisticated set of DDE functions that you can use to set up FoxPro for Windows as a client or a server application, FoxPro for Macintosh works only with the required set of functions (RUN, OPEN, PRINT, QUIT) in addition to DO SCRIPT, allowing you to execute veritably any command in a script that you can execute interactively in the FoxPro Command window.

However, a problem arises when you want to pass the result of one of these commands from FoxPro for Macintosh to the client application requesting the data. While the RUNSCRIPT command provides a means for returning a value to FoxPro after an AppleScript script has finished performing an operation or a calculation, the means of passing data to an application from FoxPro is not as straightforward. Since no WITH clause is included with RUNSCRIPT, you cannot simply pass parameters to a script that could then pass the data contained in those parameters to another application. This article explains two methods of passing data to an application that is requesting data from FoxPro. The first method uses primitive file passing; the second uses the Clipboard, also known as the "scrap."

MORE INFORMATION

Method 1

As stated above, the DO SCRIPT command allows you to execute virtually any command that you can enter in the Command window. This functionality is fine for performing basic maintenance on a table or for printing a routine report or a set of labels where no result is needed in order to proceed. Often, however, a decision is made based upon information derived from a table. Using DO SCRIPT, you can perform a query to obtain information from a table. However, you cannot directly return the result of the query to the application requesting that the query be performed.

One method of circumventing this limitation is to use FoxPro's ability to create ASCII files and in some cases files of the type specific to the application that is requesting the data. The following code presents a simple example of how you can take advantage of FoxPro's ability to store and manage tabular data and Microsoft Excel's ability to analyze that data.

1. In the Script Editor, type the following code. Replace the semicolons with the AppleScript command-continuation character (OPTION+ENTER):

         copy "STATE" to theField
         copy "MA" to theValue
         copy "MASS" to destFile
         copy "Macintosh HD:Excel:" to destPath
         copy "Macintosh HD:FoxPro:tutorial:customer" to srcFile
   

         tell application "Microsoft FoxPro"
            Do Script "SET SAFETY OFF"
            copy "SELECT * FROM '" ;
               & srcFile & ;
               "' WHERE " ;
               & theField & ;
               "= '" & theValue & ;
               "' INTO CURSOR " ;
               & destFile to cmd
            Do Script cmd
   
            copy "COPY TO '" ;
               & destPath ;
               & destFile ;
               & ".XLS' TYPE XLS" to cmd
            Do Script cmd
            Do Script "SET SAFETY ON"
         end tell
   
         tell application "Microsoft Excel"
            copy destPath & destFile & ".XLS" to theDoc
            open theDoc
         end tell
   
   

2. In the FoxPro folder, save the script as an application called FilePass. Make sure you select the Never Show Startup Screen check box. Close the script.

3. Open Microsoft Excel version 4.0 for Macintosh and create a new macro sheet. In cells A1 through A3 of the macro sheet, type the following code:

         FilePass
         =EXEC("MACINTOSH HD:FOXPRO:FILEPASS",,TRUE,TRUE)
         =RETURN()
   

4. Give the macro a name by clicking cell A1 of the macro sheet and choosing Define Name from the Formula menu. In the resulting dialog box, type a name such as "FilePass" and select the Command radio button in the Macro section of the dialog box. Choose OK to save the name and close the Define Name dialog box.

5. From the Macro menu, choose Run to run the macro. The result is a sheet in Microsoft Excel containing the 16 records from CUSTOMER.DBF that have addresses located in Massachusetts.

The advantage of the previous approach is that there is practically no limit to the amount of data that can be passed from FoxPro to the requesting application. The disadvantage is that FoxPro must write to a disk file and that file must in turn be read, a much slower means of communication than using RAM. On the Macintosh, the Clipboard is an area in RAM that can be used to provide a faster, although limited, means of passing data and requests between applications.

Method 2

Many Macintosh applications, including FoxPro for Macintosh, maintain their own private scrap. FoxPro for Macintosh makes its private scrap available to the user through the system memory variable _CLIPTEXT. When you are switching from one application to another, the suspended application transfers the contents of its private scrap to the system scrap. When an application is resumed, it detects whether or not the contents of the system scrap have changed, and, if necessary, transfers the contents of the system scrap to its own private scrap. Activating one application, the destination, implies that another application, the source, is suspended.

In the context of an AppleScript script and the Apple Events from which it is derived, you must issue the "activate" command prior to accessing the Clipboard in order to ensure that the previous application has transferred the contents of its private scrap to the system scrap.

The following example shows that the system scrap, and hence the destination application's scrap, will not be updated unless the destination application is activated first. This example assumes that both FoxPro for Macintosh and the Scriptable Text Editor, supplied with the AppleScript extension, are available and that there is enough RAM available to run FoxPro for Macintosh, the Scriptable Text Editor, and a small script simultaneously.

1. Type the following FoxPro code into a program file. Call it CPTEST.PRG.

         _cliptext=' Jumped Over The Lazy Dog'
         RUNSCRIPT copyTest
   

2. Create an AppleScript containing the following code. Call it COPYTEST.

         tell application "Scriptable Text Editor"
            paste
         end tell
   
      NOTE: You must save and close the script before it can be used by FoxPro
      for Macintosh.
   
   

3. Launch the Scriptable Text Editor. In the document window that appears, type the following line of text:

         The Quick Brown Fox...
   

4. Select the text and choose Copy from the Edit menu. Place the insertion point after the ellipsis in the document window and return to FoxPro.

5. Run the FoxPro program. When the program terminates, the focus should be returned to the FoxPro Command window. Switch to the Scriptable Text Editor to view the contents of the document window. It should contain the text "The Quick Brown Fox...".

6. Now that FoxPro for Macintosh has been suspended, its private scrap has been transferred to the system scrap which was, in turn, transferred to the private scrap of the Scriptable Text Editor. To demonstrate this, choose Paste from the Scriptable Text Editor's Edit menu. "Jumped Over The Lazy Dog" now appears in the document window.

   tell application "Scriptable Text Editor"
      activate  -- force the generation of a suspend event in FP for Mac
      paste
   end tell

   -- Return focus to FoxPro
   tell application "Microsoft FoxPro"
      activate
   end tell

NOTE: If a substantial amount of data is passed through the Clipboard, the Macintosh operating system may place the data in a temporary file. There is no way to prevent this from happening other than to limit the amount of data passed to the Clipboard.

For an in-depth discussion of the Scrap Manager, see "Inside Macintosh: More Macintosh Toolbox." For more information about IAC, see "Inside Macintosh: Interapplication Communication." The Event Manager and its related Suspend and Resume events are discussed in "Inside Macintosh: Macintosh Toolbox Essentials."

REFERENCES

Microsoft Excel "Function Reference," version 4.0 Microsoft Excel "User's Guide 2," version 4.0 Microsoft FoxPro "Installation and Macintosh Features Guide" "AppleScript Developer's Kit," Apple Computer "Inside Macintosh: Interapplication Communication," Apple Computer "Inside Macintosh: More Macintosh Toolbox," Apple Computer