TweetFollow Us on Twitter

FoxPro and AppleScript
Volume Number:10
Issue Number:7
Column Tag:Applescript

Related Info: Apple Event Mgr

FoxPro and AppleScript

Drive other mac applications to create your own workflow solution

By Randy Brown, Sierra Systems

About the author

Randy is a consultant for Sierra Systems in Northern California. He specializes in custom FoxPro development on Macintosh, Windows and DOS. Prior to founding Sierra Systems, Randy consulted with Ernst & Young’s National Energy Practice in San Francisco.

Randy coauthored FoxPro MAChete: Hacking FoxPro for Macintosh, by Hayden Books, from which this article is excerpted. Last year, Randy completed his second book, FoxPro 2.5 OLE and DDE, a book by Pinnacle Publishing. Randy has written numerous articles on a variety of FoxPro technical topics for magazines like Dr. Dobbs Journal and FoxTalk. Randy is a speaker on the FoxPro conference circuit, which includes the Microsoft FoxPro Devcon and the Minnesota FoxPro User’s Conference. You can reach him at:


CompuServe: 71141,3014

America OnLine: RandyBrwn

Wouldn’t it be nice if you could have Microsoft FoxPro for Macintosh output a database query and have Microsoft Excel automatically create a chart from that data? Wouldn’t it be nice if this chart, along with an associated data table, could be inserted into an executive report in your favorite word processor such as WordPerfect? Wouldn’t it be nice if you could print this report, fax and/or E-Mail it to any destination of your choosing?

This article explores integrating Microsoft FoxPro/Mac with other applications using AppleScript™. If you are new to FoxPro and/or AppleScript, this article can help you gain a better understanding of these concepts. There are additional reference materials for mastering the elements of FoxPro and AppleScript.

Before we jump into lots of juicy code, I should warn you that running AppleScript with FoxPro is going to chew up lots of RAM. In fact, I would recommend a minimum of 8MB since FoxPro itself likes to have at least 5MB. And depending on the other application(s), you may need more.

In short, FoxPro for Macintosh supports the Required Suite and one critical additional command which it includes in a Miscellaneous Suite. What does this mean? Well, other applications can only make calls to FoxPro with these commands:

Required Suite:

Open - opens the specified object(s).

Print - print the specified object(s).

Quit - quit application.

Run - sent to application when it is double-clicked.

Miscellaneous Suite:

Do Script - execute a script.

I guess we can stop here, since there is not a whole lot of AppleScript stuff which FoxPro supports, right? Not quite. Using AppleScript is a two-sided equation, so learning about FoxPro’s support of AppleScript is only half the work. Examine Microsoft Excel for a minute. When I write a script (a script is the document containing the AppleScript code - similar to a FoxPro .PRG), that script can contain any commands and objects supported by the application. A script communicating with Excel can contain many AppleScript commands since Excel supports a fairly large set. In fact, Excel supports 5 separate suites of Apple Events (Required, Core, Table, Charting and Excel). So even though talking to FoxPro often involves small scripts, going in the other direction can be a different story.

Scripting to FoxPro

As you may know, FoxPro has a command, RUNSCRIPT, which is used to run a script from within a FoxPro program. I’ll go into this later, but you may not know that other applications can talk to FoxPro. Here is a very simple script you can try. If FoxPro is not running, the script will automatically launch it for you.

/* 1 */
tell application "Microsoft FoxPro"
    'This is my first script' TIMEOUT 2"
end tell

This simple script uses the DO SCRIPT command, the only non-Required suite AppleScript command FoxPro supports. The DO SCRIPT command lets you run any FoxPro command. While the FoxPro WAIT WINDOW command is just one of many possibilities, you would most likely pass a DO command to run a program (PRG or APP) to execute a sequence of commands. You wouldn’t want to string a bunch of FoxPro commands using separate DO SCRIPT AppleScript commands, because going back and forth between FoxPro and AppleScript is slow. These hits may not be all that significant, but you should try to avoid a lot of switching between applications.

This next script shows some of the commands which FoxPro supports from the Required Events suite (Run, Open, Print, Quit). The use of FoxPro’s INKEY() function here illustrates a neat trick you can use to make FoxPro pause for a specified time.

/* 2 */
- Sample AppleScript showing Required commands.
run application "Microsoft FoxPro"
tell application "Microsoft FoxPro"
  activate "Microsoft FoxPro"
  open "config.fpm"
  Do Script "=INKEY(2)"
  activate "Finder"
end tell

One of the more intriguing AppleScript ideas is accessing FoxPro as a server. The concept of using FoxPro in a client-server capacity based on AppleScript is pretty cool. Methods of requesting data can come in multiple flavors. For instance, you can write a script to request data by dropping a special request file on an AppleScript droplet. If you want to get a little fancier, take a look at a neat shareware utility called Folder Watcher (by Joe Zobkiw). Folder Watcher is actually a control panel/system extension combo which monitors the contents of any specified folder(s). Through the control panel, you can control a number of actions taken when the contents of a watched folder changes. Typically you have your machine display a dialog and/or play a sound. A more powerful option, however, is to have an AppleScript run when a folder’s contents change. Think of the possibilities. For example, you could set up a folder called “Requests” into which you drop files which FoxPro could then automatically process through a script.

Scripting from FoxPro

Here is the only command FoxPro has devoted to AppleScript. Unlike XCMDs, values passed back from scripts are not limited to strings. Scripts, for example, can also return numeric data types.

/* 3 */
RUNSCRIPT <script> | TO <memvar> |

Before you plan on issuing this command, you better make sure your Mac has AppleScript loaded, else an error will result. You could test for this by setting up an a FoxPro ON ERROR routine or you could search for the AppleScript extension file(s). Apple uses the following files for running AppleScript.

/* 4 */
Apple® Event Manager

The following program checks for the existence of the AppleScript file. This program relies on a function called FxSystem() contained in the FOXTOOLS.MLB library. If you plan on including this program in a distributed application, any Macs running it will need to have the Apple Shared Library Manager in order to load the FOXTOOLS library.

/* 5 */

#DEFINE C_ASFILE "AppleScript™"
PRIVATE hasascript,asfilename
PRIVATE saveerr,savelib,haderr

m.haderr = .F.
m.hasascript = .T.
m.asfilename = ""

m.savelib = SET('LIBRARY')
IF ATC('FOXTOOLS',m.savelib)=0
  m.saveerr = ON('ERROR')
  ON ERROR m.haderr = .T.
  ON ERROR &saveerr
  IF m.haderr 
      "Could not load FoxTools library"
    RETURN .F.

m.asfilename =fxsystem(1)+':'+C_ASFILE
m.hasascript = FILE(m.asfilename)

IF ATC('FOXTOOLS',m.savelib)=0

RETURN m.hasascript

FoxPro scripting can be fun, but having one command can be somewhat limiting. Take a close look at the RUNSCRIPT command above. Notice how information can be received but not passed. Part of the trick to successful scripting is finding a way to pass data from FoxPro to AppleScript. Since parameters are out of the question, you have two options. The first is to use a file whose format can be used by the other application (for example, FoxPro can output tables to Excel spreadsheets). The second option is to use the clipboard. FoxPro has a _CLIPTEXT system variable, which contains the text contents of the clipboard. If the other application can access the clipboard either through its own AppleScript application command or its own native language (or macros), you are in luck.

Excel Cross Tabs

This next example uses the first approach to pass data: FoxPro translates data to a recognizable file format. I’ll begin by discussing the overall integration of data between applications and then follow up with some tips and tricks seen in the code. In this example, a FoxPro table is queried and the output of this query is saved to an Excel spreadsheet. FoxPro then runs a script which processes this data in Excel. Here is how it works:

1. FoxPro runs a query from a table and saves the output to a spreadsheet.

2. FoxPro next runs a script to process this new file.

3. The script opens the worksheet saved from FoxPro and runs a macro.

4. The Excel macro creates a pretty cross-tabular table from the data.

Figure A. Results of FoxPro-Excel Cross-Tab script.

Instead of beginning with the script, I will first examine the FoxPro program since it is executed first. The Macsales table contains sales figures for various Macintosh computers by region. In our example, we want to obtain a breakdown of product by year for sales.

Figure B. FoxPro table used in Cross-Tab script.

The MAC SALES.PRG program is extremely basic. Again, the idea is to stress the AppleScript functionality, not what we can do with FoxPro. The COPY TO ... TYPE XLS command is used to create the Excel spreadsheet used later in the script.

/* 6 */
* Mac Sales program

m.filepath = SET("DEFAULT") +;
m.salesdbf = m.filepath + "MAC SALES.DBF"
m.xlsheet  = m.filepath + "XL SALES.XLS"
m.xlscript = m.filepath + "XL SCRIPT"


USE (m.salesdbf) ALIAS macsales AGAIN

SELECT product,unitprice*quantity AS sales,;
  YEAR(date) AS Year FROM macsales ;
  GROUP BY year,product ORDER BY year;
  INTO CURSOR xlcurs

COPY TO (m.xlsheet) TYPE XLS

RUNSCRIPT (m.xlscript) TO m.retval

There isn’t anything special in this program. A variable named m.retval is used to obtain any values returned by the script. I haven’t tested this variable in the sample procedure, but you could easily set up statements to do this. Now, look at the script that uses the results:

/* 7 */
- XL Script is file name 
tell application "Microsoft Excel"
  - setup AppleScript variables
  set x to path to startup disk
  copy (x as string) ¬
    & "Machete:AppleScript:" ¬
    to foxpath
  copy "XL Macros" to xlmacs
  - FoxPro query file
  copy "XL SALES.XLS" to xlsales 
  - open files if not already open
  if not (Window xlmacs exists) then
    open foxpath & xlmacs
  end if
  if not (Window xlsales exists) then
    open foxpath & xlsales
  end if
  - make sure xlsales document is on top
  set the selection of Document ¬
    xlsales to "R1C1"
  - run excel macro to create cross tab
  Evaluate "run('" & xlmacs & "'!Cross_Tab)"
  - close files, see if Macro file is hidden 1st
  close Window xlsales saving no
  if visible of Window xlmacs = false then
    set visible of Window xlmacs to true
  end if
  close Window xlmacs saving no
  -bring Excel to front
  activate "Microsoft Excel"
end tell

Most of the meat in this script is actually part of Excel’s dictionary which can be opened using Apple’s Script Editor. The AppleScript Evaluate (Excel) command is similar to the Do Script (FoxPro) command in that it performs a native Excel command (here, the command is the macro you run).

Many Excel developers like to keep hidden their macrosheets. You can do this by hiding the macrosheet and then quitting Excel. Excel will prompt you to save the macrosheet when terminating (select Save). The Excel AppleScript Close command has trouble closing hidden files. To do so, you need to first make them visible.

Often when you close files, the application prompts you to Save or Cancel new edits. The AppleScript Close command has an optional parameter called Saving which lets you bypass the Save dialog with a particular response.

Finally, the next table shows you the Excel Cross_Tab macro contained in the XL Macros macrosheet file. This entire macro was created using Excel’s macro recorder. Excel has a Cross Tab wizard which facilitates the creation of the CROSSTAB.CREATE command. As you begin working with scripts and spreadsheets, be aware that cell referencing in scripts is usually only by RC syntax. For instance, you can use “R1C1”, but may not be able to use “A1”.

Table A. Excel Macro to create Cross Tab from XL Macros file.

Notice the last line here - =RETURN(“OK”). Many of you who are familiar with Excel macros are probably wondering why we need to return anything. For some reason, the Evaluate command must receive a data type which AppleScript is familiar with, and a null value is not one of them.

I have already pointed out that you are often better off using the native language rather than AppleScript if at all possible. Excel is no exception. If you take a look at the Excel AppleScript dictionary, you will see many commands and objects. Unfortunately, the command set is somewhat limited when compared to that of Excel’s native macro language. Many advanced features such as cross tabs can only be performed within Excel itself.

Document Integration

This last script shows off Apple’s true intent with AppleScript. Apple envisioned AppleScript as the glue needed to combine the strengths of several applications. You wouldn’t want to create a nicely formatted document in FoxPro. Nor would you want to use WordPerfect to produce a complex graph. This script combines the strengths of FoxPro (database querying), Excel (charting) and WordPerfect (word processing) to automate the generation of a sales report. While this script stops here, you could easily add additional AppleScript commands which use AOCE to send off this report to a number of E-Mail addresses.

Once again, start out with the FoxPro program. This program exploits the clipboard as the method for transferring data from FoxPro to our script. Since you are passing this data to an Excel spreadsheet, you need to format the data in a manner Excel can handle. Excel columns are separated by Tabs and rows by Carriage Return/Line Feeds.

The table used for the query is the same one used before with the Cross-Tab example (MAC SALES.DBF). This time we are querying data based on region not product.

/* 8 */
* FoxPro Regions program

#DEFINE tab CHR(9)
#DEFINE crlf CHR(13)+CHR(10)

m.filepath = SET("DEFAULT") + ;
m.salesdbf = m.filepath + "MAC SALES.DBF"
m.xlscript = m.filepath + "Create Chart"
m.tmpstr = " "


USE (m.salesdbf) ALIAS macsales AGAIN
SELECT DISTINCT region FROM macsales ;
  INTO ARRAY rgnarr
  INTO ARRAY yeararr
* create string for clipboard
FOR i = 1 TO ALEN(yeararr)
  m.tmpstr=m.tmpstr + tab + ;
m.tmpstr=m.tmpstr + crlf

FOR i = 1 TO ALEN(rgnarr)
  m.tmpstr=m.tmpstr + rgnarr(m.i) +tab
  FOR j = 1 TO ALEN(yeararr)
    SUM unitprice*quantity TO m.tmpsum ;
      FOR region = rgnarr(m.i) AND ;
    m.tmpstr=m.tmpstr + STR(m.tmpsum) + ;
  m.tmpstr=m.tmpstr + crlf

_CLIPTEXT = m.tmpstr
RUNSCRIPT (m.xlscript) TO retval

In this program, the FoxPro global _CLIPTEXT is used to transfer the data string created to the clipboard. This string consists of regions along the y-axis and years along the x-axis.

Now comes the fun part. This script below is not all that long, with most of it devoted to Excel. As you begin to read through the code, you will notice a number of tricks that may help you get around limitations with the two products. Here is what the script does:

1. FoxPro runs a query and stuffs the results into the Clipboard.

2. Excel takes the contents of the clipboard and creates a new worksheet.

3. Excel massages the data a bit, and then creates a 3D chart.

4. The script starts WordPerfect and opens a stationery document.

5. Finally, the script puts the Excel chart in the WordPerfect document.

Figure E. Excel generated chart from FoxPro query.

/* 9 */
-AppleScript script 
tell application "Microsoft Excel"
  - Set up variables
  set charttitle to "Annual Sales by Region"
  set charttype to three D column
  set chartlegend to true
  - Now do the work
  activate "Microsoft Excel"
  make Document
  Evaluate "Paste()" -paste contents of clipboard
  Evaluate "APPLY.STYLE(\"Comma [0]\")"
  (* this repeat loop converts Excel numbers into labels for the series 
headings *)
  repeat with snum from 2 to 99 - use large number
    copy value of Cell snum to scontents
    if scontents is equal to "" then
      exit repeat
    end if
      get scontents as integer - check for number
      set the value of Cell snum to "'" & scontents
      on error - loop around
    end try
  end repeat
  make new Chart
  set type of first Chart to charttype
  set variant of first Chart to 1
  set has legend of first Chart to chartlegend
  set title of first Chart to charttitle
  copy first Chart to finalchart
  close first Document saving no
  close first Document saving no
  quit -not needed if you have enough memory
end tell

(* Need to launch WP first if not already running, else the chart gets 
lost.  Also, make sure you have stationery file “Machete” loaded. *)
run application "WordPerfect"
tell application "WordPerfect"
  ignoring application responses
    activate "WordPerfect"
    - this is a stationery file
    open (a reference to file "Machete") 
    make new paragraph with data finalchart
    move paragraphs 2 thru 14 to front
  end ignoring
end tell

This script uses the Excel Evaluate command several times. The line Evaluate "APPLY.STYLE(\"Comma [0]\")" applies a number format (Comma [0]) to the selected cells. Notice the backslashes. Since AppleScript also uses double quote delimiters, the command confuses the Script Editor since there is also a set of double quotes for the Excel command. AppleScript allows you to include double quotes in your strings if you precede them with a “\” character.

The repeat loop is actually a kludge to get Excel to convert numbers (e.g., 1992, 1993) into label strings so that they will not be used as data in the chart. This happens if you try to copy and paste numeric data preceded with the single quote delimiter.

Figure F. WordPerfect final document incorporating Excel chart from FoxPro query.

Before sending AppleScript commands to WordPerfect, you need to launch it. Normally, the Tell Application command can be used. With this script, however, WordPerfect doesn’t properly process the command which adds the chart (Make New Paragraph...) if WordPerfect is not already running.

To accomplish this launch, a stationery file provides a stub. If you wanted to get really fancy, you could have FoxPro create a text file from a memo field and have this added as the text portion of the document. WordPerfect 3.0 supports Apple’s new AOCE technology which in essence integrates E-Mail into the application. One of the other reasons that you will want to consider using a stationery file is that an AOCE mailer can be attached and saved with preset E-Mail addresses. Through AppleScript, you could take this script one step further by having the generated document mailed to a number of E-Mail accounts.

Wrap Up

FoxPro’s RUNSCRIPT command lets you connect with other Macintosh applications, through AppleScript, in unlimited ways. While FoxPro itself has very limited support for AppleScript (i.e., primarily via the DO SCRIPT command), you should by now have a better understanding from this article of its true power.

Happy scripting.


Community Search:
MacTech Search:

Software Updates via MacUpdate

Rodeo Stampede guide to running a better...
In Rodeo Stampede, honing your skills so you can jump from animal to animal and outrun the herd as long as possible is only half the fun. Once you've tamed a few animals, you can bring them home with you. [Read more] | Read more »
VoxSyn (Music)
VoxSyn 1.0 Device: iOS Universal Category: Music Price: $6.99, Version: 1.0 (iTunes) Description: VoxSyn turns your voice into the most flexible vocal sound generator ever. Instantly following even subtle modulations of pitch and... | Read more »
Catch Battleplans on Google Play from Ju...
Real-time strategy title Battleplans is due for release on Google Play on June 30th, following its release for iOS systems last month. With its simple interface and pretty graphics, the crowd-pleaser brings a formerly overlooked genre out for the... | Read more »
iDoyle: The interactive Adventures of Sh...
iDoyle: The interactive Adventures of Sherlock Holmes - A Scandal in Bohemia 1.0 Device: iOS Universal Category: Books Price: $1.99, Version: 1.0 (iTunes) Description: Special Release Price $1.99 (Normally $3.99) | Read more »
Five popular free apps to help you slim...
Thanks to retail and advertising, we're used to thinking one season ahead. Here we are just a week into the summer and we're conditioned to start thinking about the fall. [Read more] | Read more »
How to ride longer and tame more animals...
It's hard to accurately describe Rodeo Stampede to people who haven't seen it yet. It's like if someone took Crossy Roadand Disco Zoo and put them in a blender, yet with a unique game mechanic that's still simple and fun for anyone. [Read more] | Read more »
Teeny Titans - A Teen Titans Go! Figure...
Teeny Titans - A Teen Titans Go! Figure Battling Game 1.0.0 Device: iOS Universal Category: Games Price: $3.99, Version: 1.0.0 (iTunes) Description: Teeny Titans, GO! Join Robin for a figure battling RPG of epic proportions! TEENY... | Read more »
NinjAwesome: Tips and tricks to be a mor...
Sorry about that headline, but I'm going to go ahead and assume that GameResort would not have named its game NinjAwesome without expecting some of that. It is, in fact, pretty awesome the way it combines an endless runner and old school arcade... | Read more »
Into Mirror (Games)
Into Mirror 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: "Is all that we see or seem, but a dream within a dream?"- Edgar Allan Poe New game by Lemon Jam Studio, the team behind Pursuit... | Read more »
Perchang (Games)
Perchang 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Welcome to Perchang! A test of skill and puzzle solving across 60 stunning levels. | Read more »

Price Scanner via

15-inch Retina MacBook Pros on sale for $200-...
B&H Photo has 15″ Retina MacBook Pros on sale for up to $210 off MSRP. Shipping is free, and B&H charges NY tax only: - 15″ 2.2GHz Retina MacBook Pro: $1799.99 $200 off MSRP - 15″ 2.5GHz... Read more
Mac minis on sale for up to $100 off MSRP
B&H Photo has Mac minis on sale for up to $100 off MSRP including free shipping plus NY sales tax only: - 1.4GHz Mac mini: $449 $50 off MSRP - 2.6GHz Mac mini: $649 $50 off MSRP - 2.8GHz Mac mini... Read more
Clearance 2015 13-inch MacBook Airs available...
B&H Photo has clearance 2015 13″ MacBook Airs available for $300 off original MSRP. Shipping is free, and B&H charges NY sales tax only: - 13″ 1.6GHz/4GB/128GB MacBook Air (MJVE2LL/A): $799.... Read more
Apple refurbished Mac minis available for up...
Apple has Certified Refurbished Mac minis available starting at $419. Apple’s one-year warranty is included with each mini, and shipping is free: - 1.4GHz Mac mini: $419 $80 off MSRP - 2.6GHz Mac... Read more
ABBYY TextGrabber: 1,000,000 Installs in 5 Da...
ABBYY, an international OCR technologies provider, has announced that their image-to-text application TextGrabber, got installed 1,000,000 times in just five days while being featured by the App... Read more
New SkinIt Waterproof Case For iPhone 6
With its impact and waterproof design, the Skinit Waterproof case provides security and protection to guarantee your phone will get you through even the most demanding outdoor conditions. The impact-... Read more
iMacs on sale for up to $150 off MSRP
B&H Photo has 21″ and 27″ iMacs on sale for up to $150 off MSRP including free shipping plus NY sales tax only: - 27″ 3.3GHz iMac 5K: $2181.11 $118 off MSRP - 27″ 3.2GHz/1TB Fusion iMac 5K: $1949... Read more
12-inch 1.1GHz Retina MacBooks on sale for $5...
B&H Photo has 2016 12″ 1.1GHz/256GB Retina MacBooks on sale for up to $50 off MSRP. Shipping is free, and B&H charges NY tax only: - 12″ 1.1GHz Space Gray Retina MacBook: $1249 $50 off MSRP... Read more
WWDC Announcements Revisited Still Underwhelm...
I was disappointed that no new MacBook hardware was announced at this year’s all-software World Wide Developer’s Conference. Not even a hint about what’s in the development pipeline. Of course, we... Read more
Twelve South Compass 2 iPad Stand Now Availab...
Twelve South has updated its most popular iPad stand, Compass 2, with the introduction of two new colors — Gold and Rose Gold. These new color options n perfectly complement the new Rose Gold iPad... Read more

Jobs Board

*Apple* New Products Testers Needed - Apple...
…we therefore look forward to put out products to quality test for durability. Apple leads the digital music revolution with its iPods and iTunes online store, Read more
*Apple* Solutions Consultant - APPLE (United...
Job Summary As an Apple Solutions Consultant, you'll be the link between our future customers and our products. You'll showcase your entrepreneurial spirit as you Read more
*Apple* Retail - Multiple Positions - Apple,...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
*Apple* Retail - Multiple Positions, Towson...
Job Description:SalesSpecialist - Retail Customer Service and SalesTransform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
*Apple* Professional Learning Specialist - A...
Job Summary The Apple Professional Learning Specialist is a full-time position for one year with Apple in the Phoenix, AZ area. This position requires a high Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.