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

The best remote desktop apps for iOS
We've been sifting through the App Store to find the best ways to do computer tasks on a tablet. That gave us a thought - what if we could just do computer tasks from our tablets? Here's a list of the best remote desktop apps to help you use your... | Read more »
Warhammer 40,000: Freeblade guide - How...
Warhammer 40,000: Freebladejust launched in the App Store and it lets you live your childhood dream of blowing up and slashing a bunch of enemies as a massive, hulking Space Marine. It's not easy being a Space Marine though - and particularly if... | Read more »
Gopogo guide - How to bounce like the be...
Nitrome just launched a new game and, as to be expected, it's a lot of addictive fun. It's called Gopogo, and it challenges you to hoparound a bunch of platforms, avoiding enemies and picking up shiny stuff. It's not easy though - just like the... | Read more »
Sago Mini Superhero (Education)
Sago Mini Superhero 1.0 Device: iOS Universal Category: Education Price: $2.99, Version: 1.0 (iTunes) Description: KAPOW! Jack the rabbit bursts into the sky as the Sago Mini Superhero! Fly with Jack as he lifts impossible weights,... | Read more »
Star Wars: Galaxy of Heroes guide - How...
Star Wars: Galaxy of Heroes is all about collecting heroes, powering them up, and using them together to defeat your foes. It's pretty straightforward stuff for the most part, but increasing your characters' stats can be a bit confusing because it... | Read more »
The best cooking apps (just in time for...
It’s that time of year again, where you’ll be gathering around the dinner table with your family and a huge feast in front of you. [Read more] | Read more »
Square Rave guide - How to grab those te...
Square Rave is an awesome little music-oriented puzzle game that smacks of games like Lumines, but with its own unique sense of gameplay. To help wrap your head around the game, keep the following tips and tricks in mind. [Read more] | Read more »
Snowboard Party 2 (Games)
Snowboard Party 2 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Crowned the best snowboarding game available on the market, Snowboard Party is back to fulfill all your adrenaline needs in... | Read more »
One Button Travel (Games)
One Button Travel 1.0 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0 (iTunes) Description: “To cut a long story short, If you like interactive fiction, just go buy this one.” - “Oozes the polish that... | Read more »
Light Apprentice Volume 1 (Games)
Light Apprentice Volume 1 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: Light Apprentice Volume 1 includes Chapters 1 to 4, all gathered in a new exclusive game. When life in the world of... | Read more »

Price Scanner via

iMobie Releases its Ace iOS Cleaner PhoneClea...
iMobie Inc. has announced the new update of PhoneClean 4, its iOS cleaner designed to reclaim wasted space on iPhone/iPad for use and keep the device fast. Alongside, iMobie hosts a 3-day giveaway of... Read more
U.S. Cellular Offering iPad Pro
U.S. Cellular today announced that it is offering the new iPad Pro with Wi-Fi + Cellular, featuring a 12.9-inch Retina display with 5.6 million pixels — the most ever in an iOS device. U.S. Cellular... Read more
Newegg Canada Unveils Black Friday Deals for...
Newegg Canada is offering more than 1,000 deep discounts to Canadian customers this Black Friday, available now through Cyber Monday, with new deals posted throughout the week. “Black Friday is... Read more
Black Friday: Macs on sale for up to $500 off...
BLACK FRIDAY B&H Photo has all new Macs on sale for up to $500 off MSRP as part of their early Black Friday sale including free shipping plus NY sales tax only: - 15″ 2.2GHz Retina MacBook Pro: $... Read more
Black Friday: Up to $125 off iPad Air 2s at B...
BLACK FRIDAY Walmart has the 16GB iPad Air 2 WiFi on sale for $100 off MSRP on their online store. Choose free shipping or free local store pickup (if available): - 16GB iPad Air 2 WiFi: $399, save $... Read more
Black Friday: iPad mini 4s on sale for $100 o...
BLACK FRIDAY Best Buy has iPad mini 4s on sale for $100 off MSRP on their online store for Black Friday. Choose free shipping or free local store pickup (if available): - 16GB iPad mini 4 WiFi: $299.... Read more
Black Friday: Apple Watch for up to $100 off...
BLACK FRIDAY Apple resellers are offering discounts and bundles with the purchase of an Apple Watch this Black Friday. Below is a roundup of the deals being offered by authorized Watch resellers:... Read more
Black Friday: Target offers 6th Generation iP...
BLACK FRIDAY Save $40 to $60 on a 6th generation iPod touch at Target with free shipping or free local store pickup (if available). Sale prices for online orders only, in-store prices may vary: -... Read more
Black Friday: Walmart and Target offer iPod n...
BLACK FRIDAY Walmart has the 16GB iPod nano (various colors) on sale for $119.20 on their online store for a limited time. That’s $30 off MSRP. Choose free shipping or free local store pickup (if... Read more
Black Friday: Target and Walmart offer new Ap...
BLACK FRIDAY Take up to $50 off the price of a new Apple TV at Target and Walmart this Black Friday. Choose free shipping or free local store pickup (if available): 32GB Apple TV: Target: $112.49,... Read more

Jobs Board

Storefront Operations Coordinator, *Apple* -...
# Storefront Operations Coordinator, Apple -Latin America Job Number: 43587750 Miami, Florida, United States Posted: Oct. 16, 2015 Weekly Hours: 40.00 **Job Summary** The Read more
*Apple* Enterprise / Government Professional...
# Apple Enterprise / Gove ment Professional Services Engineer Job Number: 42292976 Reston, Virginia, United States Posted: Aug. 18, 2015 Weekly Hours: 40.00 **Job Read more
iOS Wallet & *Apple* Pay Engineer - App...
# iOS Wallet & Apple Pay Engineer Job Number: 40586801 Santa Clara Valley, Califo ia, United States Posted: Nov. 16, 2015 Weekly Hours: 40.00 **Job Summary** The iOS Read more
Software Engineer, *Apple* Watch - Clock Fa...
# Software Engineer, Apple Watch - Clock Face Team Job Number: 44368761 Santa Clara Valley, Califo ia, United States Posted: Nov. 14, 2015 Weekly Hours: 40.00 **Job Read more
Administrative Assistant, *Apple* Online St...
# Administrative Assistant, Apple Online Store Job Number: 43992352 Santa Clara Valley, Califo ia, United States Posted: Nov. 9, 2015 Weekly Hours: 40.00 **Job Summary** Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.