TweetFollow Us on Twitter

Chart Generation Volume Number: 17 (2001)
Issue Number: 3
Column Tag: Programming Techniques

Chart Generation with AppleScript & Excel

By Rich Allen
Edited by Cal Simone

A friend of mine likes to say "everything is easy once you know how", Updating an Excel spreadsheet and generating an accompanying chart is easy using a little AppleScript, and Microsoft Excel, once you know how!

People like charts. Charts are an easy way of seeing a lot of data and understanding it with a quick glance. Using AppleScript to update an existing workbook, a simple VisualBasic macro for exporting a chart as a JPEG file and the iDo Script Scheduler included with OS 9 is all you need to create a regularly updated chart. This article will explain how to

  • Use AppleScript to parse a data file for required values
  • Transmit those values to Excel
  • Tell Excel to export a chart as a JPEG file for use as part of a web page
  • How to setup iDo so the chart is updated regularly

A note on conventions used in the AppleScript source listing; those words that have an underline are application keywords. These terms have a specific meaning to either the enclosing tell block or to AppleScript itself. See the preferences panel of your script editor for implementing this option.

First let's look at using AppleScript to open a file of raw data and parse out the values that we really want. For our raw data file, we will use a tab-delimited text file as shown in Listing 1.

Listing 1: Sample raw data

Date   Hour   PhoneNum   Type   Members   Peg   Busy   Usage
12/20/00   09   5551234   DNH   3   7   0   17
12/20/00   09   5552345   DNH   4   9   0   34
12/20/00   09   5553456   DNH   2   1   0   3
12/20/00   09   5554488   DNH   4   5   0   6

For this project, we require the values under the headings labeled "Date", "Hour", "Peg" (number of telephone calls), and "Usage" (connect time of all calls) for telephone number 5554488. We will assume that this raw file is updated once each hour, although we will want to verify this, and that our chart will track peg values versa usage value count on an hourly basis. The Excel workbook will be pre-built with two worksheets; one containing the chart (chart) and another with the values that feed that chart (data). See Figures 1 and 2 for examples of these worksheets.


Figure 1. Sample chart output.


Figure 2. Property declaration.

OK, let's start writing a script! The script will use three properties, values that will be used throughout the script. These properties designate the path to the folder containing our files, the name of the raw data file, and the telephone number to find. See Listing 2.

Listing 2: Sample of worksheet data

property folderPath : "MacHD:Desktop Folder:MacTech:"
property fileName : "rawdata"
property phoneNumber : "5554488"

It is always a good idea to have a try block around scripting statements to catch any error that may arise. The main portion of the script, Listing 3, is completely enclosed in a single try block. Any error encountered will be handled by the on error section. For example, if the raw data file can not be found when AppleScript tries to open it, an error will occur, passing control to the on error routine.

The script starts by setting fileRef to an empty string. By doing this, if an error does occur, the error handler will be able to determine if the data file is open, if so, closing it. If the error routine is invoked and fileRef is the empty string, then the error was generated while attempting to open the file thus there will be no need to execute the close access statement.

Next the script will attempt to open the raw data file, concatenating the folderPath and fileName properties into a single string as input for the open for access statement and assigning the resulting file reference number to the variable fileRef. From this point on, we can access data from the file by referring to the fileRef variable. We know that the data file is tab-delimited (a tab character seperates each value in the file), so next, the script is set to use the tab character as its text item delimiter. (Normally AppleScript's text item delimiter is set to an empty character (""). Since we already know that each value of interest per line is separated by the tab character, by setting AppleScript's text item delimiters to a tab we can easily refer to any value by its position.)

The sample data file contains eight items per line; in this case the date, the hour, phone number, phone number type, number of members associated with the phone number, a peg count, number of busies and the usage. The variable num will be set to the empty string and will later be set to the telephone number from each line that is read from the data file and then compared to the property phoneNumber to determine a positive match.

At this point the script is ready to start reading each line of the data file until it finds a match of the phone number read and the desired number. Reading a line of the data file is done with the standard read statement; reading from the current file position until the next encountered return character. If the two telephone numbers do not match, the next line is read and phone numbers compared. If the desired phone number is not found before the end of the file (eof) is reached then an error will be created by AppleScript and handled by the on error portion of the run handler, otherwise the close statement will be executed to close the data file.

Listing 3: The run handler

try
   set oldDelimiter to AppleScript's text item delimiters
   set AppleScript's text item delimiters to tab
   set fileRef to ""
   set fileRef to open for access (folderPath & fileName)
   set num to ""
   repeat until num is phoneNumber
      set dLine to read fileRef before return
      set num to text item 3 of dLine
      if (phoneNumber is num) then
         UpdateWorkbook(dLine)
      end if
   end repeat
   close access fileRef
   set AppleScript's text item delimiters to oldDelimiter
on error errMsg number errNum
   if fileRef is not "" then
      close access fileRef
   end if
   set AppleScript's text item delimiters to oldDelimiter
end try

Once a match has been found, the UpdateWorkbook handler (Listing 4) is called. (A handler is basically equivalent to a subroutine.) The script starts with a tell statement that will direct any statements following towards Excel. For any statement within the tell block that are not directly understood by Excel, those statements will be handled by AppleScript itself (e.g. if).

The first statement to Excel will open the required workbook by combining two of the properties set initially and adding the ".xls" suffix to match the exact file name and path of the required workbook. Since the workbook contains more then one worksheet (chart and data), the script selects the data worksheet to assure the values from the raw data file will be inserted into the proper cells.

For this workbook and chart we are tracking 24 hours of data. The data worksheet contains a header in the first row with the following 24 rows containing the last 24 hours worth of data. Refer back to Figure 2 for an example.

We will want to verify that the raw data we have read is new before we update the worksheet. By comparing the hour from the line of raw data with the last hour on the data worksheet (cell B25), we can be sure that the raw data has been changed.

Since we want our chart to show peg versa usage information on an hourly basis, we will want to drop the oldest hour's data from our chart and add the newest data. By copying the last 23 hours data values (rows 3 through 25) and then pasting those values into the first 23 hours data (rows 2 through 24), the script has effectively dropped the oldest hour. (Note that the copyobject keyword, used here, comes from Excel's Custom Suite - unfortunately Excel does not have the standard copy verb as many other applications do.) Rows 24 and 25 are now the same. To add the newest data, the script will simply set the new values in row 25, overwriting the extraneous values.

Now that all the values have been updated, it's time to export a copy of the chart for use on the web page. Excel has an export function using VisualBasic that can accomplish this (the macro is contained within the workbook). We will not delve into Excel VisualBasic macros in this article but will show the macro we're using here in Listing 5. AppleScript uses the evaluate statement to communicate with Excel to run the macro. Be sure to note the syntax here, evaluate requires the file name of the workbook concatenated with the name of the macro with the exclamation point character separating them.

The last job for the UpdateWorkbook handler is to save the changes made to the workbook and close the workbook file.

Listing 4: The UpdateWorkbook handler

on UpdateWorkbook(dataLine)
   tell application "Microsoft Excel"
      open (folderPath & phoneNumber & ".xls")
      select sheet "data"
      if (text item 2 of dataLine != value of cell "$B$25") then
         select range ("$A$3:$D$25")
         CopyObject selection
         select range ("$A$2:$D$24")
         paste
         set value of cell "$A$25" to text item 1 of dataLine
         set value of cell "$B$25" to text item 2 of dataLine
         set value of cell "$C$25" to text item 6 of dataLine
         set value of cell "$D$25" to text item 8 of dataLine
         evaluate (phoneNumber & ".xls!ExportChart()")
         close ActiveWorkbook saving yes
      end if
   end tell
end UpdateWorkbook

Listing 5: VisualBasic macro to export chart as JPG

Sub ExportChart()
Charts("Chart").Export _
    FileName:="pb0:Desktop Folder:MacTech:5554488.jpg"
End Sub

Each time the script is run it will update the workbook and export a new chart for use on a web page. This script does not provide a method of performing this task on a regular basis so how do we get it to run on a schedule? One solution is to use the iDo Script Scheduler that is included with the Mac OS. If it is not already in your control panels folder, you will find it on the Mac OS 9 CD in the CD Extras folder in the AppleScript Extras folder.

Open the iDo Script Scheduler control panel and click on the New button. Give this event a name, set the trigger to "repeating", and select the start time and date. All that is left is to click the Choose button, select the script (which we've saved as a compiled script), and save the newly created event. Your chart will now be updated every hour at the specified time. See Figure 3 for an example of the iDo event.


Figure 3. iDo event.

Although this is a short script with only limited error checking, it does demonstrate the ease of integrating AppleScript with Microsoft Excel to create charts suitable for use on any web page!

My friend also has another thing he likes to say, "Now that I know how, it's easy!"


Rich Allen currently works for a local telephone company in Alaska in the traffic-engineering department. He has held a variety of telecommunications positions since 1984 and has designed a number of Macintosh based data systems. His email address is g3pb@alaska.net.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

Tor Browser 11.5.8 - Anonymize Web brows...
Using Tor Browser you can protect yourself against tracking, surveillance, and censorship. Tor was originally designed, implemented, and deployed as a third-generation onion-routing project of the U.... Read more
Alarm Clock Pro 15.0 - $19.95 (91% off)
Alarm Clock Pro isn't just an ordinary alarm clock. Use it to wake you up in the morning, send and compose e-mails, remind you of appointments, randomize the iTunes selection, control an internet... Read more
Google Chrome 107.0.5304.121 - Modern an...
Google Chrome is a Web browser by Google, created to be a modern platform for Web pages and applications. It utilizes very fast loading of Web pages and has a V8 engine, which is a custom built... Read more
calibre 6.9.0 - Complete e-book library...
Calibre is a complete e-book library manager. Organize your collection, convert your books to multiple formats, and sync with all of your devices. Let Calibre be your multi-tasking digital librarian... Read more
Safari Technology Preview 16.4 - The new...
Safari Technology Preview contains the most recent additions and improvements to WebKit and the latest advances in Safari web technologies. And once installed, you will receive notifications of... Read more
FileZilla 3.62.2 - Fast and reliable FTP...
FileZilla (ported from Windows) is a fast and reliable FTP client and server with lots of useful features and an intuitive interface. The FileZilla Client not only supports FTP, but also FTP over TLS... Read more
djay Pro 4.0.13 - Transform your Mac int...
djay Pro provides a complete toolkit for performing DJs. Its unique modern interface is built around a sophisticated integration with iTunes and Spotify, giving you instant access to millions of... Read more
Opera 93.0.4585.21 - High-performance We...
Opera is a fast and secure browser trusted by millions of users. With the intuitive interface, Speed Dial and visual bookmarks for organizing favorite sites, news feature with fresh, relevant content... Read more
AppCleaner 3.6.6 - Uninstall your apps e...
AppCleaner allows you to uninstall your apps easily. It searches the files created by the applications and you can delete them quickly. Supports macOS Ventura. Fixed an issue causing failed updates... Read more
QuickBooks 21.0.7.1248 - Financial manag...
QuickBooks helps you manage your business easily and efficiently. Organize your finances all in one place, track money going in and out of your business, and spot areas where you can save. Built for... Read more

Latest Forum Discussions

See All

‘Top Hunter Roddy & Cathy’ Review –...
The NEOGEO is generally characterized by, with only a few notable exceptions, fighting games and Metal Slug. Within a couple of years of its launch, the vast majority of the output on the console seemed to be mining (quite successfully) a few... | Read more »
SwitchArcade Round-Up: Reviews Featuring...
Hello gentle readers, and welcome to the SwitchArcade Round-Up for November 28th, 2022. In today’s article, we’ve got a pair of reviews to check out. Full reviews of Pokemon Scarlet and Violet and The Oregon Trail are waiting for you to read. There’... | Read more »
‘OPUS: Echo of Starsong’ Interview: Port...
With OPUS: Echo of Starsong ($8.99) having finally launched on iOS after hitting PC and consoles, I had a chance to talk to Scott Chen who is the co-founder and executive producer of Sigono. In our chat, I touched on topics like game subscription... | Read more »
Best iPhone Game Updates: ‘Rush Rally 3’...
Hello everyone, and welcome to the week! It’s time once again for our look back at the noteworthy updates of the last seven days. As November breaths its last, the holiday season is right around the corner. That means we should start seeing more... | Read more »
‘Total Football’ is an Arcade-Style Socc...
GALA SPORTS recently launched its brand new soccer title, Total Football, and, true to its name, it is a pure arcade-style soccer game in the same vein as FIFA Mobile and PES Mobile. It also features official licensing from FIFPro and Manchester... | Read more »
Genshin Impact will recieve two new char...
HoYoverse has announced that Genshin Impacts version 3.3 will be arriving on December 7th. Titled All Senses Clear, All Existence Void, the update will bring two powerful new characters and a brand new card-based minigame. [Read more] | Read more »
‘Wreckfest’ Mobile Compared With Console...
HandyGames’ mobile version of Bugbear’s demolition derby-style racer Wreckfest ($9.99) released on iOS and Android recently, and we featured it as our Game of the Week. | Read more »
Black Friday Deals Here – The TouchArcad...
After taking a couple of weeks off we return on this glorious Black Friday with another episode of The TouchArcade Show. We get into a big discussion about virtual assistants like Alexa, Siri, and Google, and their place in the greater smarthome... | Read more »
TouchArcade Game of the Week: ‘Station 1...
I’m a big fan of Glitch Games and their unique brand of point-and-click adventure/escape room/puzzle games, and while they’re a tiny outfit and there’d typically be a couple years gap in-between their new releases, they were always worth the wait.... | Read more »
SwitchArcade Round-Up: ‘Super Lone Survi...
Hello gentle readers, and welcome to the SwitchArcade Round-Up for November 25th, 2022. Today we look at the remaining releases for the week, and I’ll be honest with you: it’s not a great assortment. Still, there are at least a couple of things... | Read more »

Price Scanner via MacPrices.net

Cyber Monday: 24″ Apple M1 iMacs for $150 off...
Amazon has Apple’s 24″ M1 iMacs on Black Friday sale for $150 off MSRP. Their prices are currently the lowest available for new iMacs among the Apple retailers we track: – 24″ M1 iMacs (8-Core CPU/7-... Read more
Cyber Monday Sale: 25% off Apple MagSafe acce...
Apple retailers are offering MagSafe accessories for up to 25% off MSRP for Cyber Monday. Here are the best deals available, currently from Verizon and Amazon: (1) Verizon has Apple MagSafe Chargers... Read more
Cyber Monday Sale: Apple AirPods for up to $1...
Looking for Apple AirPods, AirPods Pro, or AirPods Max this Cyber Monday? Look no further than our Apple AirPods Price Tracker. We track prices from 20+ Apple retailers and update the tracker... Read more
Final day for Apple’s Black Friday/Cyber Mond...
CYBER MONDAY Apple’s four day Black Friday/Cyber Monday 2022 event is now live and will run from November 25, 2022 to November 28, 2022 (ends today!). Receive a free $100-$250 Apple Gift Card with... Read more
Cyber Monday: Apple 13″ M2 MacBook Airs for $...
Apple retailers have posted their Cyber Monday prices on 13″ MacBook Airs. Take up to $200 off MSRP on M2-powered Airs with these sales with prices starting at only $1049. Free shipping is available... Read more
The best Cyber Monday iPhone sale? This $500...
If you switch to Xfinity Mobile and open a new line of service, they will take $500 off the price of a new iPhone, no trade-in required. This is the best no trade-in Cyber Monday Apple iPhone 14 deal... Read more
Cyber Monday Sale: Apple 16″ MacBook Pros for...
Amazon is offering $500 off MSRP discounts on Apple 16″ MacBook Pros with M1 Pro CPUs as part of their Cyber Monday sale. Their prices are the lowest available for these models from any Apple... Read more
Cyber Monday Sale: Apple 14″ MacBook Pros for...
Amazon is offering $300-$500 off MSRP discounts on Apple 14-inch MacBook Pros with M1 Pro CPUs as part of their Cyber Monday sale. Their prices are the lowest available for these models from any... Read more
Cyber Monday Sale: Apple Watch Ultra for $60...
Amazon has Apple Watch Ultra models (Alpine Loop, Trail Loop, and Opean Bans) on sale for $60 off MSRP as part of their Cyber Monday sale, each including free shipping, reducing the price for an... Read more
Cyber Monday MacBook Sale: 13″ M1 Apple MacBo...
Amazon has Apple 13″ M1 MacBook Airs back on sale for $200 off MSRP, starting at only $799, for Cyber Monday 2022. Their prices are the lowest available for new MacBooks this Cyber Monday. Stock may... Read more

Jobs Board

*Apple* Electronic Repair Technician - PlanI...
…a highly motivated individual to join our Production Department as an Apple Electronic Repair Technician. The computer repair technician will diagnose, assemble, Read more
Product Manager II - *Apple* - DISH (United...
…you will be doing We seek an ambitious, data-driven thinker to assist the Apple Product Development team as our new Retail Wireless division continues to grow and Read more
Staff Engineer 5G Protocol, *Apple* - DISH...
…metrics. Essential Functions and Responsibilities for a Staff Engineer 5G protocol( Apple ) Knowledge of 5G and 4G/LTE protocols and system architectures Experience Read more
Cashier - *Apple* Blossom Mall - JCPenney (...
Cashier - Apple Blossom Mall Location:Winchester, VA, United States (https://jobs.jcp.com/jobs/location/191170/winchester-va-united-states) - Apple Blossom Mall Read more
Omnichannel Associate - *Apple* Blossom Mal...
Omnichannel Associate - Apple Blossom Mall Location:Winchester, VA, United States (https://jobs.jcp.com/jobs/location/191170/winchester-va-united-states) - Apple Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.