TweetFollow Us on Twitter

Introduction to Scripting Microsoft Excel

Volume Number: 23 (2007)
Issue Number: 02
Column Tag: AppleScript Essentials

Introduction to Scripting Microsoft Excel

by Benjamin S. Waldie

With Office 2008 on the horizon, Microsoft has recently begun to push AppleScript as an alternative automation technology to Visual Basic macros in the Office applications. Moving forward, Visual Basic macros will not be supported in the release of Office 2008. Current AppleScript users are ahead of the curve. The Office applications have been AppleScriptable for quite some time, and AppleScript actually provides several advantages over Visual Basic. For one, AppleScripts can interact with multiple applications, including non-Microsoft applications, allowing even complex multi-application workflows to be automated.

Last month, we began discussing how to get started with scripting Microsoft Word. We explored various techniques for interacting with Word documents, as well as the content within those documents, all using AppleScript. This month, we're going to begin discussing another Office application, Microsoft Excel. Like Word, Excel contains a quite extensive AppleScript dictionary, allowing almost any task that can be performed manually to be automated using AppleScript.

Please note, all example code within this month's column was written and tested with Excel 2004 (version 11.x). If you are using another version of Excel, please be aware that the terminology may need to be adjusted in order to function properly. Let's get started.

Working with Workbooks

In Excel, the top-level class (beneath the application class) with which you will probably want to interact is a workbook. A workbook will contain one or more sheets, and those sheets will typically contain ranges of data. This data can be text, numbers, dates, and so forth. We will discuss each of these primary classes of Excel objects in this month's column, but we will begin with the workbook class.

Making a Workbook

Creating a workbook in Excel is similar to the process of creating a document in Word, or in many scriptable applications, for that matter. To do so, use the make command, as demonstrated below.

tell application "Microsoft Excel"
   make new workbook
end tell
--> workbook "Sheet1" of application "Microsoft Excel"

The make command's result will be a reference to the newly created workbook, which may be placed into an AppleScript variable, if desired, for future reference in your script.

Closing a Workbook

Closing a workbook is also very similar to closing a document in other scriptable applications. Use the close command, referencing the workbook you wish to close. Optionally, you may choose to specify whether the workbook should be saved during the close process, using the optional saving parameter. For example:

tell application "Microsoft Excel"
   close workbook 1 saving no
end tell

Opening a Workbook

To open a workbook, use the open command, followed by a reference to the workbook file you want to open. For example:

set theWorkbookFile to choose file with prompt "Please select an Excel workbook file:"
tell application "Microsoft Excel"
   open theWorkbookFile
end tell

One issue with the open command is that, unfortunately, it does not return a result. Therefore, if you want to perform further processing on the newly opened document, you will need to build a reference to it in another manner. One way to do this is to retrieve the workbook file's name, and then construct a reference to the workbook using that name, once it has been opened. This is demonstrated in the example code below.

set theWorkbookFile to choose file with prompt "Please select an Excel workbook file:"
set theWorkbookName to name of (info for theWorkbookFile)
tell application "Microsoft Excel"
   open theWorkbookFile
   set theWorkbook to workbook theWorkbookName
end tell
--> workbook "My Workbook.xls" of application "Microsoft Excel"

Another way that this can be achieved is by referencing the active workbook property of Excel's application class, once the workbook has been opened. This property references the currently active workbook, which should be the newly opened document.

set theWorkbookFile to choose file with prompt "Please select an Excel workbook file:"
set theWorkbookName to name of (info for theWorkbookFile)
tell application "Microsoft Excel"
   open theWorkbookFile
   set theWorkbook to active workbook
end tell
--> active workbook of application "Microsoft Excel"

When referencing the active workbook property, one thing to keep in mind is that, if another workbook is brought to the front, then your script may reference the incorrect workbook. Because of this, it is recommended to reference workbooks by name.

Saving a Workbook

To save an opened workbook to its existing path, use the save command, referencing the workbook to be saved. For example, the following code will save the currently active workbook to its current path.

tell application "Microsoft Excel"
   save active workbook
end tell

Excel also has a save workbook as command, which may be used to save a workbook into a new location, or in a different file format. The following example code makes use of this command, as well as some of its optional parameters, in order to save the currently active workbook to the desktop in comma separated format.

set theOutputPath to (path to desktop folder as string) & "My Saved Workbook.csv"
tell application "Microsoft Excel"
   tell active workbook
      save workbook as filename theOutputPath file format CSV file format
   end tell
end tell

Take some time to explore some of the other optional parameters for the save workbook as command, as well as some of the other available file formats, which can be found in Excel's AppleScript dictionary.

Working with Sheets

As previously mentioned, a workbook itself does not contain data in Excel. Rather, a workbook contains sheets, which contain the data. Most often, you will find yourself writing a script that will interact with a worksheet, a specific type of sheet in Excel. That's what we will be discussing here. Another type of sheet, which we will not discuss at this time, is a chart sheet.

Making a Worksheet

Like a workbook, a worksheet is created by using the make command. When using this command, be sure to specify a location for the new worksheet to be created, such as beginning, end, before worksheet 1, and so forth. For example, this code will create a new worksheet at the end of the existing worksheets within the currently active workbook.

tell application "Microsoft Excel"
   tell active workbook
      make new worksheet at end
   end tell
end tell
--> sheet "Sheet2" of active workbook of application "Microsoft Excel"

Selecting a Worksheet

At times, you may want to navigate to a specific worksheet in an Excel workbook. To do this, use the activate object command, and target the worksheet that you want to be displayed. For example:

tell application "Microsoft Excel"
   tell active workbook
      activate object worksheet "Sheet1"
   end tell
end tell

Working with Data

In an Excel worksheet, data is contained within cells, which are organized into rows and columns. Cells can be accessed by referencing the cell, row, column, or range class.

The Cell Class

To access a specific cell, use the cell class. For example, the following code references the first cell of a worksheet, found in column A, row 1.

tell application "Microsoft Excel"
   tell worksheet "Sheet1" of active workbook
      cell "A1"
   end tell
end tell
--> cell "A1" of worksheet "Sheet1" of active workbook of application "Microsoft Excel" 

The Row Class

To access an entire row of cells, use the row class. For example, the following code references the first row of cells in a worksheet.

tell application "Microsoft Excel"
   tell worksheet "Sheet1" of active workbook
      row 1
   end tell
end tell
--> row "$1:$1" of worksheet "Sheet1" of active workbook of application "Microsoft Excel"

The Column Class

To access an entire column of cells, use the column class. For example, the following code references the first column of cells in a worksheet.

tell application "Microsoft Excel"
   tell worksheet "Sheet1" of active workbook
      column 1
   end tell
end tell
--> column "$A:$A" of worksheet "Sheet1" of active workbook of application "Microsoft Excel"

The Range Class

Regardless of how you reference cells within a worksheet, you are really referencing what is known as a range. A range refers to either a single cell, or multiple cells within a worksheet, and the cell, row, and column classes all inherit the properties of the range class. There are numerous ways to directly reference a range. The following are some examples.

This code demonstrates how to reference a range that represents a single cell in a worksheet, in this case, the first cell in the first row, i.e. the intersection of column 1 and row 1.

tell application "Microsoft Excel"
   tell worksheet "Sheet1" of active workbook
      range "A1"
   end tell
end tell
--> cell "A1" of worksheet "Sheet1" of active workbook of application "Microsoft Excel"

This next example demonstrates how to reference a range that represents multiple cells within a worksheet, in this case, cells 2 through 5 of the first two rows.

tell application "Microsoft Excel"
   tell worksheet "Sheet1" of active workbook
       range "B1:F2"
   end tell
end tell
--> range "B1:F2" of worksheet "Sheet1" of active workbook of application "Microsoft Excel"

Again, there are numerous ways to reference a range of cells, and Excel is pretty flexible. For a chart that outlines different methods, take a look at the AppleScript Reference Guide for Excel, mentioned later in this column.

The Used Range

In some cases, you may not know specifically which range you want to reference. For example, you may just want to reference all of the data contained within a specified worksheet. To do this, you can reference the used range property of the worksheet.

tell application "Microsoft Excel"
   tell worksheet "Sheet1" of active workbook
      used range
   end tell
end tell
--> used range of active sheet of active workbook ¬
of application "Microsoft Excel"

Properties of Ranges

We have discussed numerous ways to reference ranges of cells in Excel. However, what can you do with a range once you have constructed a reference to it? Well, one thing you can do is access its properties. Ranges have numerous properties, but perhaps the two that you may find most useful are the value and formula properties.

By referencing the value property of a range, you can retrieve the value of a specified set of cells in a worksheet. For example, the following code retrieves the values of the used range in a specified worksheet. Notice that the value is returned as a list of lists. Each list represents a row, and each list item represents a cell.

tell application "Microsoft Excel"
   tell worksheet "Sheet1" of active workbook
      value of used range
   end tell
end tell
--> {{1.0, 2.0, 3.0}, {4.0, 5.0, 6.0}}

The formula property of a range returns a similar result. For example:

tell application "Microsoft Excel"
   tell worksheet "Sheet1" of active workbook
      formula of used range
   end tell
end tell
--> {{"1", "2", "3"}, {"4", "5", "6"}}

Of course, these properties are not read-only properties. So, it is also possible to modify them, if desired. For example, the following code demonstrates how to set the value of a single cell.

tell application "Microsoft Excel"
   tell worksheet "Sheet1" of active workbook
      set value of cell "A1" to "A"
   end tell
end tell
Likewise, the following code will set the value of a range of cells.
tell application "Microsoft Excel"
   tell worksheet "Sheet1" of active workbook
      set value of range "A1:C2" to {{"a", "b", "c"}, {"d", "e", "f"}}
   end tell
end tell

Pulling Things Together

Now, let's take a brief look at some sample code that makes use of several of the topics that we have discussed throughout this month's column. The following example code will retrieve the names of any visible items on the desktop. It will then create a workbook, and insert the list of item names into the active worksheet.

— Retrieve a list of items on the desktop
set theFileNames to list folder (path to desktop) without invisibles
— Convert the list of items to a list of lists
repeat with a from 1 to length of theFileNames
   set item a of theFileNames to {item a of theFileNames}
end repeat
— Build a new workbook in Excel, and add the data to the current worksheet
tell application "Microsoft Excel"
   set theWorkbook to make new workbook
   tell active sheet of theWorkbook
      set value of range ("A1:A" & (length of theFileNames)) to theFileNames
   end tell
end tell

In Closing

It may seem like we've only scratched the surface of scripting Excel, and we have. As mentioned at the beginning of this month's column, Excel contains quite an extensive AppleScript dictionary, and there is a lot that you can do with it from a scripting perspective. However, using the techniques we have discussed in this month's column, you should be able to piece together a script that can construct a workbook, create a worksheet, retrieve data from a range of cells in a worksheet, and more.

For more information about scripting Excel, be sure to download the Excel AppleScript Reference Guide that I mentioned earlier. This can be found on the Mactopia website at http://www.microsoft.com/mac/. It is located in the Resources > Developer Center > AppleScript Resources for Office 2004 section.

Until next time, keep scripting!


Ben Waldie is the author of the best selling books "AppleScripting the Finder" and the "Mac OS X Technology Guide to Automator", available from http://www.spiderworks.com, as well as an AppleScript Training CD, available from http://www.vtc.com. Ben is also president of Automated Workflows, LLC, a company specializing in AppleScript and workflow automation consulting. For years, Ben has developed professional AppleScript-based solutions for businesses including Adobe, Apple, NASA, PC World, and TV Guide. For more information about Ben, please visit http://www.automatedworkflows.com, or email Ben at ben@automatedworkflows.com.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

Yasu 4.0.0 β - System maintenance app; p...
Yasu was created with System Administrators who service large groups of workstations in mind, Yasu (Yet Another System Utility) was made to do a specific group of maintenance tasks quickly within a... Read more
Skype 7.37.0.178 - Voice-over-internet p...
Skype allows you to talk to friends, family and co-workers across the Internet without the inconvenience of long distance telephone charges. Using peer-to-peer data transmission technology, Skype... Read more
EtreCheck 3.0.5 - For troubleshooting yo...
EtreCheck is an app that displays the important details of your system configuration and allow you to copy that information to the Clipboard. It is meant to be used with Apple Support Communities to... Read more
Amadeus Pro 2.3.1 - Multitrack sound rec...
Amadeus Pro lets you use your Mac computer for any audio-related task, such as live audio recording, digitizing tapes and records, converting between a variety of sound formats, etc. Thanks to its... Read more
NeoFinder 6.9.3 - Catalog your external...
NeoFinder (formerly CDFinder) rapidly organizes your data, either on external or internal disks, or any other volumes. It catalogs all your data, so you stay in control of your data archive or disk... Read more
WhatsApp 0.2.1880 - Desktop client for W...
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
Hazel 4.0.6 - Create rules for organizin...
Hazel is your personal housekeeper, organizing and cleaning folders based on rules you define. Hazel can also manage your trash and uninstall your applications. Organize your files using a familiar... Read more
Apple iBooks Author 2.5 - Create and pub...
Apple iBooks Author helps you create and publish amazing Multi-Touch books for iPad. Now anyone can create stunning iBooks textbooks, cookbooks, history books, picture books, and more for iPad. All... Read more
MYStuff Pro 2.0.26 - $39.99
MYStuff Pro is the most flexible way to create detail-rich inventories for your home or small business. Add items to MYStuff by dragging and dropping existing information, uploading new images, or... Read more
MarsEdit 3.7.8 - Quick and convenient bl...
MarsEdit is a blog editor for OS X that makes editing your blog like writing email, with spell-checking, drafts, multiple windows, and even AppleScript support. It works with with most blog services... Read more

How to get past Vulture Island's tr...
Vulture Island is a colorful and quirky mish-mash of platforming and puzzles. It’s creative and fresh, but sometimes the game can throw a curveball at you, leaving you stuck as to how you should progress. These tips will help you explore smoothly... | Read more »
The new Clash of Kings is just for Weste...
If you’ve played the original Clash of Kings, you’ll probably recognise the city building, alliance forging and strategic battles in Clash of Kings: The West. What sets this version apart is that it’s tailor made for a Western audience and the... | Read more »
Frost - Survival card game (Games)
Frost - Survival card game 1.12.1 Device: iOS Universal Category: Games Price: $3.99, Version: 1.12.1 (iTunes) Description: *Warning: the game will work on iPhone 5C and above and iPad Pro / 4. Other devices are not supported* | Read more »
How to build and care for your team in D...
Before you hit the trail and become a dog sledding legend, there’s actually a fair bit of prep work to be done. In Dog Sled Saga, you’re not only racing, you’re also building and caring for a team of furry friends. There’s a lot to consider—... | Read more »
How to win every race in Dog Sled Saga
If I had to guess, I’d say Dog Sled Saga is the most adorable racing game on the App Store right now. It’s a dog sled racing sim full of adorable, loyal puppies. Just look at those fluffy little tails wagging. Behind that cute, pixelated facade is... | Read more »
Let the war games commence in Gunship Ba...
Buzz Lightyear famously said, “This isn’t flying, this is falling – with style!” In the case of Gunship Battle: Second War, though, this really is flying - with style! The flight simulator app from Joycity puts you in control of 20 faithfully... | Read more »
How to get a high score in Fired Up
Fired Up is Noodlecake Games’ high score chasing, firefighting adventure. You take control of a wayward firefighter who propels himself up the side of a highrise with blasts of water. Sound silly? It is. It’s also pretty difficult. You can’t... | Read more »
NBA 2K17 (Games)
NBA 2K17 1.0 Device: iOS iPhone Category: Games Price: $7.99, Version: 1.0 (iTunes) Description: Following the record-breaking launch of NBA 2K16, the NBA 2K franchise continues to stake its claim as the most authentic sports video... | Read more »
Dog Sled Saga (Games)
Dog Sled Saga 1.0.1 Device: iOS Universal Category: Games Price: $3.99, Version: 1.0.1 (iTunes) Description: A game by Dan + Lisa As a rookie musher, foster a dogsledding team whose skills will grow if they're treated right. Week by... | Read more »
60 Seconds! Atomic Adventure (Games)
60 Seconds! Atomic Adventure 1.2 Device: iOS Universal Category: Games Price: $2.99, Version: 1.2 (iTunes) Description: 60 Seconds! is a dark comedy atomic adventure of scavenge and survival. Collect supplies and rescue your family... | Read more »

Price Scanner via MacPrices.net

21-inch iMacs on sale for up to $120 off MSRP
B&H Photo has 21″ iMacs on sale for up to $120 off MSRP including free shipping plus NY sales tax only: - 21″ 3.1GHz iMac 4K: $1379 $120 off MSRP - 21″ 2.8GHz iMac: $1199.99 $100 off MSRP - 21″ 1... Read more
13-inch 2.7GHz/256GB Retina MacBook Pro on sa...
Amazon.com has the 13″ 2.7GHz/256GB Retina Apple MacBook Pro on sale for $151 off MSRP including free shipping: - 13″ 2.7GHz/256GB Retina MacBook Pro (sku MF840LL/A): $1348 $151 off MSRP Read more
Apple TVs on sale for up to $50 off MSRP
Best Buy has 32GB and 64GB Apple TVs on sale for $40-$50 off MSRP on their online store. Choose free shipping or free local store pickup (if available). Sale prices for online orders only, in-store... Read more
Apple refurbished 13-inch Retina MacBook Pros...
Apple has Certified Refurbished 13″ Retina MacBook Pros available for up to $270 off the cost of new models. An Apple one-year warranty is included with each model, and shipping is free: - 13″ 2.7GHz... Read more
Duplicate Sweeper Free On Mac App Store For O...
To celebrate the launch of Apple’s latest macOS Sierra, Stafford, United Kingdom based Wide Angle Software has announced that its duplicate file finder software, Duplicate Sweeper, is now available... Read more
13-inch Retina MacBook Pros on sale for up to...
B&H Photo has 13″ Retina Apple MacBook Pros on sale for up to $150 off MSRP. Shipping is free, and B&H charges NY tax only: - 13″ 2.7GHz/128GB Retina MacBook Pro: $1174.99 $125 off MSRP - 13... Read more
Evidence Surfaces Pointing To New A10X Chip F...
Citing a job description for a Project Lead position at Apple’s Austin, Texas engineering labs, Motley Fool’s Ashraf Eassa deduces that development is progressing well on Apple’s next-generation in-... Read more
Check Print’R for macOS Allows Anyone to Easi...
Delaware-based Match Software has announced the release and immediate availability of Check Print’R 3.21, an important update to their easy-to-use check printing application for macOS. Check Print’R... Read more
Apple refurbished 11-inch MacBook Airs availa...
Apple has Certified Refurbished 11″ MacBook Airs (the latest models), available for up to $170 off the cost of new models. An Apple one-year warranty is included with each MacBook, and shipping is... Read more
Apple refurbished 15-inch Retina MacBook Pros...
Apple has Certified Refurbished 2015 15″ Retina MacBook Pros available for up to $380 off the cost of new models. An Apple one-year warranty is included with each model, and shipping is free: - 15″ 2... Read more

Jobs Board

Sr. *Apple* Mac Engineer - Net2Source Inc....
…staffing, training and technology. We have following position open with our client. Sr. Apple Mac Engineer6+ Months CTH Start date : 19th Sept Travelling Job If Read more
*Apple* Retail - Multiple Positions-Norfolk,...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, 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
Lead *Apple* Solutions Consultant - Apple (...
# Lead Apple Solutions Consultant Job Number: 51829230 Detroit, Michigan, United States Posted: Sep. 19, 2016 Weekly Hours: 40.00 **Job Summary** The Lead ASC is an Read more
US- *Apple* Store Leader Program - Apple (Un...
…Summary Learn and grow as you explore the art of leadership at the Apple Store. You'll master our retail business inside and out through training, hands-on Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.