April, 2007
Page 90

              set cl to cell i of ur

              set cmt to Excel comment of cl -- no error if nothing (dummy comment)

              set vis to visible of cmt --get any property , returns missing value if empty

              if vis is not missing value then

                   set cmtText to Excel comment text cmt

                   set AppleScript's text item delimiters to {oldName}

                   set chunks to text items of cmtText

                   set AppleScript's text item delimiters to {newName}

                   set cmtText to chunks as Unicode text

                   delete cmt

                   add comment cl comment text cmtText

              end if

          end repeat

             set AppleScript's text item delimiters to {""}

     end repeat

end tell

Remember from Working with Columns and Rows that we cannot repeat with cl in (cells of ur) nor set cl to item i of (cells of ur). Instead we must repeat with i from 1 to (count cells of ur) – since count cells works while getting cells (or every cell) does not – and then get cell i of ur.

We do not have Worksheet Functions such as Substitute in AppleScript either (see the introduction to this chapter). We could enter a function such as Substitute in an unused cell and get its value (result), but another way to do it is using the built-in AppleScript text item delimiters to replace text, and restore them to the default {""} at the end.

And it works.

An Extra – Deleting Hyperlinks

It's too soon to know if Excel in Office 2008 will have an option in the UI to not create automatic hyperlinks (that would be nice!), but this routine will delete all the hyperlinks from the active sheet:

Public Sub DeleteActiveSheetHyperlinks()

On Error Resume Next

ActiveSheet.Hyperlinks.Delete

On Error GoTo 0

End Sub

Simple:

tell application "Microsoft Excel"

     try

          delete every hyperlink of active sheet

     end try

end tell

One More

At the very end of these articles, closing Chapter 6 Entourage, is a script for exporting selected contacts to Excel with just the selected fields you want.