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

Minecraft 1.20.2 - Popular sandbox build...
Minecraft allows players to build constructions out of textured cubes in a 3D procedurally generated world. Other activities in the game include exploration, gathering resources, crafting, and combat... Read more
HoudahSpot 6.4.1 - 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 is a productivity tool. It is the hub where all the... Read more
coconutBattery 3.9.14 - Displays info ab...
With coconutBattery you're always aware of your current battery health. It shows you live information about your battery such as how often it was charged and how is the current maximum capacity in... Read more
Keynote 13.2 - Apple's presentation...
Easily create gorgeous presentations with the all-new Keynote, featuring powerful yet easy-to-use tools and dazzling effects that will make you a very hard act to follow. The Theme Chooser lets you... Read more
Apple Pages 13.2 - Apple's word pro...
Apple Pages is a powerful word processor that gives you everything you need to create documents that look beautiful. And read beautifully. It lets you work seamlessly between Mac and iOS devices, and... Read more
Numbers 13.2 - Apple's spreadsheet...
With Apple Numbers, sophisticated spreadsheets are just the start. The whole sheet is your canvas. Just add dramatic interactive charts, tables, and images that paint a revealing picture of your data... Read more
Ableton Live 11.3.11 - Record music usin...
Ableton Live lets you create and record music on your Mac. Use digital instruments, pre-recorded sounds, and sampled loops to arrange, produce, and perform your music like never before. Ableton Live... Read more
Affinity Photo 2.2.0 - Digital editing f...
Affinity Photo - redefines the boundaries for professional photo editing software for the Mac. With a meticulous focus on workflow it offers sophisticated tools for enhancing, editing and retouching... Read more
SpamSieve 3.0 - Robust spam filter for m...
SpamSieve is a robust spam filter for major email clients that uses powerful Bayesian spam filtering. SpamSieve understands what your spam looks like in order to block it all, but also learns what... Read more
WhatsApp 2.2338.12 - Desktop client for...
WhatsApp is the desktop client for WhatsApp Messenger, a cross-platform mobile messaging app which allows you to exchange messages without having to pay for SMS. WhatsApp Messenger is available for... Read more

Latest Forum Discussions

See All

Square Enix commemorates one of its grea...
One of the most criminally underused properties in the Square Enix roster is undoubtedly Parasite Eve, a fantastic fusion of Resident Evil and Final Fantasy that deserved far more than two PlayStation One Games and a PSP follow-up. Now, however,... | Read more »
Resident Evil Village for iPhone 15 Pro...
During its TGS 2023 stream, Capcom showcased the Following upcoming ports revealed during the Apple iPhone 15 event. Capcom also announced pricing for the mobile (and macOS in the case of the former) ports of Resident Evil 4 Remake and Resident Evil... | Read more »
The iPhone 15 Episode – The TouchArcade...
After a 3 week hiatus The TouchArcade Show returns with another action-packed episode! Well, maybe not so much “action-packed" as it is “packed with talk about the iPhone 15 Pro". Eli, being in a time zone 3 hours ahead of me, as well as being smart... | Read more »
TouchArcade Game of the Week: ‘DERE Veng...
Developer Appsir Games have been putting out genre-defying titles on mobile (and other platforms) for a number of years now, and this week marks the release of their magnum opus DERE Vengeance which has been many years in the making. In fact, if the... | Read more »
SwitchArcade Round-Up: Reviews Featuring...
Hello gentle readers, and welcome to the SwitchArcade Round-Up for September 22nd, 2023. I’ve had a good night’s sleep, and though my body aches down to the last bit of sinew and meat, I’m at least thinking straight again. We’ve got a lot to look at... | Read more »
TGS 2023: Level-5 Celebrates 25 Years Wi...
Back when I first started covering the Tokyo Game Show for TouchArcade, prolific RPG producer Level-5 could always be counted on for a fairly big booth with a blend of mobile and console games on offer. At recent shows, the company’s presence has... | Read more »
TGS 2023: ‘Final Fantasy’ & ‘Dragon...
Square Enix usually has one of the bigger, more attention-grabbing booths at the Tokyo Game Show, and this year was no different in that sense. The line-ups to play pretty much anything there were among the lengthiest of the show, and there were... | Read more »
Valve Says To Not Expect a Faster Steam...
With the big 20% off discount for the Steam Deck available to celebrate Steam’s 20th anniversary, Valve had a good presence at TGS 2023 with interviews and more. | Read more »
‘Honkai Impact 3rd Part 2’ Revealed at T...
At TGS 2023, HoYoverse had a big presence with new trailers for the usual suspects, but I didn’t expect a big announcement for Honkai Impact 3rd (Free). | Read more »
‘Junkworld’ Is Out Now As This Week’s Ne...
Epic post-apocalyptic tower-defense experience Junkworld () from Ironhide Games is out now on Apple Arcade worldwide. We’ve been covering it for a while now, and even through its soft launches before, but it has returned as an Apple Arcade... | Read more »

Price Scanner via MacPrices.net

New low price: 13″ M2 MacBook Pro for $1049,...
Amazon has the Space Gray 13″ MacBook Pro with an Apple M2 CPU and 256GB of storage in stock and on sale today for $250 off MSRP. Their price is the lowest we’ve seen for this configuration from any... Read more
Apple AirPods 2 with USB-C now in stock and o...
Amazon has Apple’s 2023 AirPods Pro with USB-C now in stock and on sale for $199.99 including free shipping. Their price is $50 off MSRP, and it’s currently the lowest price available for new AirPods... Read more
New low prices: Apple’s 15″ M2 MacBook Airs w...
Amazon has 15″ MacBook Airs with M2 CPUs and 512GB of storage in stock and on sale for $1249 shipped. That’s $250 off Apple’s MSRP, and it’s the lowest price available for these M2-powered MacBook... Read more
New low price: Clearance 16″ Apple MacBook Pr...
B&H Photo has clearance 16″ M1 Max MacBook Pros, 10-core CPU/32-core GPU/1TB SSD/Space Gray or Silver, in stock today for $2399 including free 1-2 day delivery to most US addresses. Their price... Read more
Switch to Red Pocket Mobile and get a new iPh...
Red Pocket Mobile has new Apple iPhone 15 and 15 Pro models on sale for $300 off MSRP when you switch and open up a new line of service. Red Pocket Mobile is a nationwide service using all the major... Read more
Apple continues to offer a $350 discount on 2...
Apple has Studio Display models available in their Certified Refurbished store for up to $350 off MSRP. Each display comes with Apple’s one-year warranty, with new glass and a case, and ships free.... Read more
Apple’s 16-inch MacBook Pros with M2 Pro CPUs...
Amazon is offering a $250 discount on new Apple 16-inch M2 Pro MacBook Pros for a limited time. Their prices are currently the lowest available for these models from any Apple retailer: – 16″ MacBook... Read more
Closeout Sale: Apple Watch Ultra with Green A...
Adorama haș the Apple Watch Ultra with a Green Alpine Loop on clearance sale for $699 including free shipping. Their price is $100 off original MSRP, and it’s the lowest price we’ve seen for an Apple... Read more
Use this promo code at Verizon to take $150 o...
Verizon is offering a $150 discount on cellular-capable Apple Watch Series 9 and Ultra 2 models for a limited time. Use code WATCH150 at checkout to take advantage of this offer. The fine print: “Up... Read more
New low price: Apple’s 10th generation iPads...
B&H Photo has the 10th generation 64GB WiFi iPad (Blue and Silver colors) in stock and on sale for $379 for a limited time. B&H’s price is $70 off Apple’s MSRP, and it’s the lowest price... Read more

Jobs Board

Housekeeper, *Apple* Valley Villa - Cassia...
Apple Valley Villa, part of a 4-star senior living community, is hiring entry-level Full-Time Housekeepers to join our team! We will train you for this position and Read more
Housekeeper, *Apple* Valley Village - Cassi...
Apple Valley Village Health Care Center, a 4-star rated senior care campus, is hiring a Part-Time Housekeeper to join our team! We will train you for this position! Read more
Optometrist- *Apple* Valley, CA- Target Opt...
Optometrist- Apple Valley, CA- Target Optical Date: Sep 23, 2023 Brand: Target Optical Location: Apple Valley, CA, US, 92308 **Requisition ID:** 796045 At Target Read more
Senior *Apple* iOS CNO Developer (Onsite) -...
…Offense and Defense Experts (CODEX) is in need of smart, motivated and self-driven Apple iOS CNO Developers to join our team to solve real-time cyber challenges. Read more
*Apple* Systems Administrator - JAMF - Activ...
…**Public Trust/Other Required:** None **Job Family:** Systems Administration **Skills:** Apple Platforms,Computer Servers,Jamf Pro **Experience:** 3 + years of Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.