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

VMware Fusion 11.5.6 - Run Windows apps...
VMware Fusion and Fusion Pro - virtualization software for running Windows, Linux, and other systems on a Mac without rebooting. The latest version includes full support for Windows 10, macOS Mojave... Read more
Alfred 4.1 - Quick launcher for apps and...
Alfred is an award-winning productivity application for OS X. Alfred saves you time when you search for files online or on your Mac. Be more productive with hotkeys, keywords, and file actions at... Read more
Dashlane 6.2032.0 - Password manager and...
Dashlane is an award-winning service that revolutionizes the online experience by replacing the drudgery of everyday transactional processes with convenient, automated simplicity - in other words,... Read more
Skype 8.63.0.76 - Voice-over-internet ph...
Skype is a telecommunications app that provides HD video calls, instant messaging, calling to any phone number or landline, and Skype for Business for productive cooperation on the projects. This... Read more
Mellel 5.0.3 - The word processor for sc...
Mellel is the leading word processor for OS X and has been widely considered the industry standard for long form documents since its inception. Mellel focuses on writers and scholars for technical... Read more
A Better Finder Rename 11.20 - File, pho...
A Better Finder Rename is the most complete renaming solution available on the market today. That's why, since 1996, tens of thousands of hobbyists, professionals and businesses depend on A Better... Read more
TunnelBear 3.9.10 - Subscription-based p...
TunnelBear is a subscription-based virtual private network (VPN) service and companion app, enabling you to browse the internet privately and securely. Features Browse privately - Secure your data... Read more
Dropbox 103.4.383 - Cloud backup and syn...
Dropbox for Mac is a file hosting service that provides cloud storage, file synchronization, personal cloud, and client software. It is a modern workspace that allows you to get to all of your files... Read more
Daylite 2020.29.1 - Dynamic business org...
Daylite helps businesses organize themselves with tools such as shared calendars, contacts, tasks, projects, notes, and more. Enable easy collaboration with features such as task and project... Read more
HoudahSpot 5.1.5 - Advanced file-search...
HoudahSpot is a versatile desktop search tool. Use HoudahSpot to locate hard-to-find files and keep frequently used files within reach. HoudahSpot will immediately feel familiar. It works just the... Read more

Latest Forum Discussions

See All

Motorball is a car football game from No...
A few years back Noodlecake Studios announced that they would be dipping in the multiplayer gaming realm with two different games. The first of those, Golf Blitz, released a while back and has proven to be very popular. Now, the second has arrived... | Read more »
SINoALICE's latest update introduce...
SINoALICE's latest update has now arrived, adding several fan-favourite characters from popular RPG series NieR. Young Nier, Kaine, and Young Emil are available in-game as part of a limited-time crossover event set to run until August 20th. [Read... | Read more »
Rocat Jumpurr is an intense roguelite pl...
Rocat Jumpurr is a roguelite platformer from developer Mousetrap Games. You might already be familiar with it if you follow the Big Indie Pitch, where it won first place during this year's Pocket Gamer Connects London competition. Following its... | Read more »
PUBG Mobile's Play As One campaign...
Back in mid-July, we reported that PUGB Mobile had teamed up with Direct Relief to help raise money for the charity's COVID-19 response project. It focused on an in-game running challenge for players, which lead to the PUBG Mobile donating $2... | Read more »
Marvel Contest of Champions' latest...
Marvel Contest of Champions' latest motion comic has arrived, and it shows off new fighters Air-Walker and Dragon Man. Both characters are set to arrive in-game this month. [Read more] | Read more »
Clash Royale: The Road to Legendary Aren...
Supercell recently celebrated its 10th anniversary and their best title, Clash Royale, is as good as it's ever been. Even for lapsed players, returning to the game is as easy as can be. If you want to join us in picking the game back up, we've put... | Read more »
Global Spy is an intriguing 2D spy sim f...
Developer Yuyosoft Innovations' Global Spy launched last month for iOS and Android, though if you missed it at the time, we're here to tell you why it's well worth a go. This one's all about international espionage, tracking down elusive spies,... | Read more »
Distract Yourself With These Great Mobil...
There’s a lot going on right now, and I don’t really feel like trying to write some kind of pithy intro for it. All I’ll say is lots of people have been coming together and helping each other in small ways, and I’m choosing to focus on that as I... | Read more »
Hyena Squad is sci-fi turn-based strateg...
Wave Light Games has just revealed its latest release, Hyena Squad, a turn-based RPG set in a space station infested by gross aliens and the living dead. The announcement was first reported on by Touch Arcade. [Read more] | Read more »
Idle Guardians: Never Die is a pixel art...
SuperPlanet has been fairly prolific with game releases so far this year with both Evil Hunter Tycoon and Lucid Adventure releasing earlier this year. Now, they've released another idle RPG called Idle Guardians: Never Die, which you can download... | Read more »

Price Scanner via MacPrices.net

Apple restocks refurbished 2020 13″ MacBook A...
Apple has restocked Certified Refurbished 2020 13″ MacBook Airs starting at only $849 and up to $200 off the cost of new Airs. Each MacBook features a new outer case, comes with a standard Apple one-... Read more
Apple restocks clearance 2019 13″ 2.4GHz MacB...
Apple has restocked Certified Refurbished 2019 13″ 2.4GHz 4-Core Touch Bar MacBook Pros starting at $1359 and up to $560 off original MSRP. Apple’s one-year warranty is included, shipping is free,... Read more
Apple restocks refurbished iPhone XR models s...
Apple has restocked Certified Refurbished, unlocked, iPhone XR models in the refurbished section of their online store starting at $539. Each iPhone comes with Apple’s standard one-year warranty,... Read more
Price drops! $100-$200 off clearance 27″ 5K i...
B&H Photo has dropped prices on clearance, previous-generation 27″ 5K iMacs by up to $200 off Apple’s original MSRP: – 27″ 3.0GHz 6-Core 5K iMac: $1699 $100 off original MSRP – 27″ 3.1GHz 6-Core... Read more
Woot offers Apple Watch and iPhone models fro...
Amazon-owned Woot has refurbished Apple Watch and iPhone models available from $99-$749 through August 6th. According to Woot, the items may show some wear, but they have all been fully tested and... Read more
Apple’s Phil Schiller Steps Down As SVP OF Wo...
NEWS: 08.05.20 – Former Apple senior Vice President of worldwide marketing, Phil Schiller, is stepping down from his long time role at the company in order to focus on spending more time with family... Read more
Expercom offers $320 discount on the 6-core 1...
Apple reseller Expercom has the Silver 16″ 6-core MacBook Pro on sale for a limited time for $2079 shipped. Their price is $320 off Apple’s MSRP for this model, and it’s the cheapest price currently... Read more
Apple announces Education pricing for new 202...
Purchase a new 2020 iMac or iMac Pro at Apple using Apple’s Education discount, and take up to $400 off MSRP. All teachers, students, and staff of any educational institution with a .edu email... Read more
Apple reseller Expercom offers $256 discount...
Expercom has Apple’s new 2020 10-core iMac Pro available for order and on sale for $4743 shipped. Their price is $256 off Apple’s MSRP for this new model, and it’s the cheapest price we’ve seen so... Read more
Apple releases refreshed 2020 27″ iMacs with...
Apple today released updated versions of their 27″ iMacs featuring 10th generation Intel processors, SSDs across the board, a better 5K display, and improvements to the camera, speakers, and mic.... Read more

Jobs Board

Executive Team Leader GM Sales (Assistant Man...
…(Assistant Manager General Merchandise and Operations) - Apple Valley, CaliforniaApply NowJob ID:R0000082364job family:Store Managementschedule:Full Read more
Cub Foods - *Apple* Valley - Now Hiring Par...
Cub Foods - Apple Valley - Now Hiring Part Time! United States of America, Minnesota, Apple Valley New Retail Post Date 2 days ago Requisition # 122305 Sign Up Read more
Part-time Geek Squad *Apple* Consultation P...
**770829BR** **Job Title:** Part-time Geek Squad Apple Consultation Professional-Store 384(Ithaca) **Job Category:** Store Associates **Store Number or Department:** Read more
Product Manager, *Apple* Commercial Sales -...
Product Manager, Apple Commercial Sales Austin, TX, US Requisition Number:77652 As an Apple Product Manager for the Commercial Sales team at Insight, you Read more
Cub Foods - *Apple* Valley - Now Hiring Par...
Cub Foods - Apple Valley - Now Hiring Part Time! United States of America, Minnesota, Apple Valley New Retail Post Date 1 day ago Requisition # 122305 Sign Up Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.