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:

Internet: 71141.3014@compuserve.com

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"
  DO SCRIPT "WAIT WINDOW ¬
    '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"
  Quit
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 */
AppleScript™
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 */
* IS_ASCRIPT.PRG

#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.
  SET LIBRARY TO FOXTOOLS ADDITIVE
  ON ERROR &saveerr
  IF m.haderr 
    WAIT WINDOW ;
      "Could not load FoxTools library"
    RETURN .F.
  ENDIF
ENDIF

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

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

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") +;
  "MACHETE:APPLESCRIPT:"
m.salesdbf = m.filepath + "MAC SALES.DBF"
m.xlsheet  = m.filepath + "XL SALES.XLS"
m.xlscript = m.filepath + "XL SCRIPT"

SET SAFETY OFF
CLOSE DATA

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") + ;
  "MACHETE:APPLESCRIPT:"
m.salesdbf = m.filepath + "MAC SALES.DBF"
m.xlscript = m.filepath + "Create Chart"
m.tmpstr = " "

CLOSE DATA

USE (m.salesdbf) ALIAS macsales AGAIN
 
SELECT DISTINCT region FROM macsales ;
  INTO ARRAY rgnarr
SELECT DISTINCT YEAR(date) AS Year FROM macsales;
  ORDER BY 1;
  INTO ARRAY yeararr
 
* create string for clipboard
FOR i = 1 TO ALEN(yeararr)
  m.tmpstr=m.tmpstr + tab + ;
    ALLTRIM(STR(yeararr(m.i)))
ENDFOR
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 ;
      YEAR(date)=yeararr(m.j)
    m.tmpstr=m.tmpstr + STR(m.tmpsum) + ;
      IIF(j=ALEN(yeararr),'',tab)
  ENDFOR
  m.tmpstr=m.tmpstr + crlf
ENDFOR

_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
    try
      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

How to build a successful civilisation i...
GodFinger 2 grants you godlike powers, leaving you to raise a civilization of followers. In the spirit of games like Black & White, the GodFinger games will see you building bigger and better villages, developing more advanced technology and... | Read more »
How to get all the crabs in Mr Crab 2
Mr. Crab 2 may look like a cutesy platformer for kids, but if you're the kind of person who likes to complete a game 100%, you'll soon realise that it's a tougher than a crustacean's shell. [Read more] | Read more »
How to be a star in Britney Spears: Amer...
If you've ever wanted to be a star, baby, then you've probably already checked out Britney Spears: American Dream and are happily making your way up the charts. But fame doesn't come easy, and everyone needs a helping hand sometimes. So we've got... | Read more »
AppSpy is hiring a part time Staff Write...
| Read more »
How to save lives in ER Surgery Simulato...
A serious earthquake has struck a nearby town in ER Surgery Simulator - Emergency Doctor, and it’s up to you to save the victims. [Read more] | Read more »
Tips and tricks to get a high score in G...
Ketchapp Games loves the endless runner genre. And its newest game, Gravity Switch, is no exception. Gravity Switch takes a fresh approach, though, as you move a block, suspended in zero gravity, safely through a maze of shifting pillars. If the... | Read more »
Tips and tricks to get a high score in S...
Smash Fu is a high-paced tile-tapping game that requires quick reflexes and some practice. You’ll have to smash bricks with the skill of a seasoned black belt to get a high score. To raise the stakes a bit, you’ll also have to avoid tapping any... | Read more »
How to keep the ball rolling in Dropple
If you're new to the minimalist puzzler Dropple, you may find yourself struggling to make it beyond the first couple of steps before your ball falls into the endless abyss below. [Read more] | Read more »
Game Craft releases new Legend of War ti...
Set for release at the end of this month, real time strategy title Legend of War seems sure to delight with a veritable feast of sweet features to get stuck into. Developed by Game Craft, the game is due for release through both the App Store and... | Read more »
How not to die in Traffic Rider
Traffic Rider, an Out Run-esque game in which your ride a motorcycle recklessly into trffic, might not seem particularly complicated. [Read more] | Read more »

Price Scanner via MacPrices.net

Apple refurbished iMacs available for up to $...
Apple has Certified Refurbished 2015 21″ & 27″ iMacs available for up to $350 off MSRP. Apple’s one-year warranty is standard, and shipping is free. The following models are available: - 21″ 3.... Read more
Textkraft Professional Becomes A Mobile Produ...
The new update 4.1 of Textkraft Professional for the iPad comes with many new and updated features that will be particularly of interest to self-publishers of e-books. Highlights include import and... Read more
SnipNotes 2.0 – Intelligent note-taking for i...
Indie software developer Felix Lisczyk has announced the release and immediate availability of SnipNotes 2.0, the next major version of his productivity app for iOS devices and Apple Watch.... Read more
Pitch Clock – The Entrepreneur’s Wingman Laun...
Grand Rapids, Michigan based Skunk Tank has announced the release and immediate availability of Pitch Clock – The Entrepreneur’s Wingman 1.1, the company’s new business app available exclusively on... Read more
13-inch 2.9GHz Retina MacBook Pro on sale for...
B&H Photo has the 13″ 2.9GHz Retina MacBook Pro (model #MF841LL/A) on sale for $1599 including free shipping plus NY tax only. Their price is $200 off MSRP. Amazon also has the 13″ 3.9GHz Retina... Read more
Apple price trackers, updated continuously
Scan our Apple Price Trackers for the latest information on sales, bundles, and availability on systems from Apple’s authorized internet/catalog resellers. We update the trackers continuously: - 15″... Read more
Clearance 12-inch Retina MacBooks available s...
B&H Photo has dropped prices on leftover 2015 12″ Retina MacBooks with models now available starting at $999. Shipping is free, and B&H charges NY tax only: - 12″ 1.1GHz Gray Retina MacBook... Read more
Check Apple prices on any device with the iTr...
MacPrices is proud to offer readers a free iOS app (iPhones, iPads, & iPod touch) and Android app (Google Play and Amazon App Store) called iTracx, which allows you to glance at today’s lowest... Read more
New 2016 13-inch 256GB MacBook Air on sale fo...
B&H Photo has the new 13″ 1.6GHz/256GB MacBook Air (model MMGG2LL/A) on sale for $1149 including free shipping plus NY sales tax only. Their price is $50 off MSRP. Amazon has the 13″ 1.6GHz/256GB... Read more
Apple refurbished iPad Air 2s available start...
Apple has Certified Refurbished iPad Air 2 available starting at $339. Apple’s one-year warranty is included with each model, and shipping is free: - 128GB Wi-Fi iPad Air 2: $499 - 64GB Wi-Fi iPad... Read more

Jobs Board

*Apple* Nissan Service Technicians - Apple A...
Apple Automotive is one of the fastest growing dealer...and it shows. Consider making the switch to the Apple Automotive Group today! At Apple Automotive , Read more
ISCS *Apple* ID Site Support Engineer - APP...
…position, we are looking for an individual who has experience supporting customers with Apple ID issues and enjoys this area of support. This person should be Read more
Automotive Sales Consultant - Apple Ford Linc...
…you. The best candidates are smart, technologically savvy and are customer focused. Apple Ford Lincoln Apple Valley is different, because: $30,000 annual salary Read more
*Apple* Support Technician II - Worldventure...
…global, fast growing member based travel company, is currently sourcing for an Apple Support Technician II to be based in our Plano headquarters. WorldVentures is Read more
Restaurant Manager (Neighborhood Captain) - A...
…in every aspect of daily operation. WHY YOU'LL LIKE IT: You'll be the Big Apple . You'll solve problems. You'll get to show your ability to handle the stress and Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.