Moving from Microsoft Office VBA to AppleScript:
MacTech's Guide to Making the Transition

Page Prev and Page Next buttons at bottom of the page.

April, 2007
Page 45

          "Mac HD:Folder:File.txt" format tab delimiter

Note that you can also use the numbers 1, 2, 3, etc. for the format parameter, and it works! So for translating from VBA macros don't go to the trouble of opening VB Editor to scour the Object Browser and work out which number is equivalent to which parameter. Just use the same number in the script. (There are quite a few enumerations where you can do this.) But it's a lot more useful having the proper names when writing your own scripts from scratch.

For opening text files, there is a much more powerful command, however – open text file. Your VBA macro might well be using the VBA equivalent – OpenText – and then you should too.

Workbooks.OpenText FileName:="Mac HD:Folder:File.txt", _

   DataType:=xlDelimited, Tab:=True

to:

tell application "Microsoft Excel"

     open text file filename ¬

          "Mac HD:Folder:File.txt" data type delimited with tab

end tell

Note that in AppleScript the boolean command values true and false following a parameter name (tab true, tab false) compile as with and without respectively preceding the parameter name (with tab, without tab). Don't be alarmed when that first happens. You can type them either way. A group of booleans all of the same value compile together like so:

tab true consecutive delimiter true

compiles as:

with tab and consecutive delimiter

The VBA OpenText command might come with some rather complex information in the optional FieldInfo argument:

   Workbooks.OpenText FileName:="Mac HD:Folder:File.txt", _

      DataType:=xlDelimited, Tab:=True, _

      FieldInfo:=Array(Array(3, 9), Array(1, 2))

Without getting into exactly what that means here (it has to do with specifying the data types of specific columns) you can translate that FieldInfo value to:

     open text file filename ¬

          "Mac HD:Folder:File.txt" data type delimited ¬

          field info {{3, 9}, {1, 2 }} with tab

where the VBA Arrays become lists in AppleScript. If you need to understand and not just transcribe them, or if there is a problem with that method, read up on field info in either the VBA Help or the Excel AppleScript Reference, and do it this way, as specified by the dictionary:

     open text file filename ¬

          "Mac HD:Folder:File.txt" data type delimited ¬

          field info {{3, skip column}, {1, text format}} with tab

Add a worksheet or chart

From here on, to save space I will omit many of the tell application "Microsoft Excel" blocks for the shorter excerpts. But it always applies. All statements will compile only in Excel tell blocks.

To add a new worksheet to the active workbook, placing it just to the left of the active worksheet:

   ActiveWorkbook.Worksheets.Add

becomes:

make new worksheet at active workbook

You can always use "in" instead of "at" if it sounds better to you:

make new worksheet in active workbook

works just the same. (You may have noticed that AppleScript has many synonyms and alternative ways of saying the same thing. That sometimes confuses people coming from other programming languages.)

Again, you can set read/write properties at inception.

set newSheet to make new worksheet at active workbook with properties ¬

     {name:"Test 1", display page breaks:false}

Using Before or After in VBA allows one to place the new worksheet precisely. To add one worksheet to the beginning of the workbook: