Unable to "open text file" or "save workbook as" in Excel 2016

AppleScript
#1

I spent many fruitless hours Googling for a working example of AppleScript code to Open a CSV file and to do a Save As a .xlsx file in Excel 2016 (v16.14.1), and I was wondering if anyone had any advice or suggestions.

I can’t get the “open text file” AppleScript command to work in Excel unless the text file had been previously opened by Excel manually. I hacked my way around my inability to open the csv file under AppleScript by issuing:

open -A “Microsoft Excel” ~/Downloads/2018-06-26_test.csv

from within a bash script, which did open it in Excel, but that’s just skating around the problem.

Unsurprisingly, just like I couldn’t open a CSV file from within AppleScript, I can’t save an .xlsx file either.

All variants of:

save workbook as workbook theWorkBookName filename theLogFileName file format Excel98to2004 file format with overwrite

fail miserably. I suspect that I am running into Sandboxing issues. I keep getting Invalid Object References, "Microsoft Excel got an error: The object you are trying to access does not exist” or parameter errors.

As an aside, when I right-click on the title of the CSV document from the ~/Downloads directory that I manually opened in Excel, the following path is exposed:

2018-06-26_test.csv
Downloads
Data
com.microsoft.Excel
Containers
Library
D_ave_G
Users
Macintosh HD
D_ave_G_MBP

All I’ve got is a dated 2004 Excel AppleScript reference, and I’m getting nowhere fast. I’d be grateful for any guidance.

Thanks,
Dave

#2

This works fine for me in Excel 2011:

tell application "Microsoft Excel"
  
  ### Microsoft Excel 14.7.2 (Office 2011) on macOS 10.12.6
  
  (*
  Excel VBA
  Workbooks.Open Filename:= _
          "Macintosh HD:Users:Shared:Dropbox:SW:DEV:KM:Test:test.csv"
  *)
  
  set csvFileHFSPath to "Macintosh HD:Users:Shared:Dropbox:SW:DEV:KM:Test:test2.csv"
  open text file filename csvFileHFSPath
  
end tell

Note that you must use a HFS path.

If figured this out by opening the Excel Dictionary in SD7 and looking for a “open” command.

#3

Hi Jim - many thanks for taking the time to reply.

I can confirm that the code you provided works like a charm on Excel 2011. Unfortunately, that same code uniformly fails on Excel 2016 with the following error message in SD:

AppleScript Execution Error
Microsoft Excel got an error: Parameter error.

Intuition tells me that the problem has to do with sandboxing - either the way in which it was implemented by Microsoft, or intentional restrictions inherent in sandboxing itself.

There is no Excel 2016 AppleScript documentation of which I am aware, period. If I am mistaken on that score, someone please let me know.

This situation is enormously frustrating. I don’t know if it is a bug in terms of sandbox implementation within Excel, or if it is just the new normal with sandboxing that filesystem operations are disallowed unless files being referenced reside within a containerized hierarchy belonging to the application. If so, how do files get moved into an application’s containerized hierarchy? I’m not even sure I’m using the appropriate terminology here when I say “containerized hierarchy” - I’m just referring to the
path I observed when I right-clicked on the title bar of the Excel 2016 document into which I manually loaded the CSV file: D_ave_G_MBP:MacintoshHD:Users:D_ave_G:Library:Containers:com.microsoft.Excel:Data:Downloads:2018-06-26_test.csv

If this is the new normal, then the future is less bright than it should be in terms of our ability to automate Excel via AppleScript. Microsoft EOLed Excel 2011 8 months ago, and also removed it from their Office 365 portal.

I am hoping that there are options of which I am unaware that don’t leave us all out in the cold…

If anyone reading this can shed any light on the situation, it would be greatly appreciated.

Thanks again for the suggestion, Jim!

#4

This seems to work (with the Script Editor language dropdown at top left set to Javascript)

(() => {
    'use strict';

    // TESTED WITH MS OFFICE 16.14.1
    // MACOS SIERRA

    const main = () => {
        'use strict';

        const fp = filePath('~/Desktop/Book1.csv');

        return Application('Microsoft Excel')
            .openTextFile({
                filename: fp
            })
    };

    // GENERIC FUNCTION -----------------------------------

    // filePath :: String -> FilePath
    const filePath = s =>
        ObjC.unwrap(ObjC.wrap(s)
            .stringByStandardizingPath);


    // MAIN --
    return main();

})();
#5

and with Applescript, this also seems to work:

use AppleScript version "2.4"
use framework "Foundation"
use scripting additions

on run
    
    
    set fp to filePath("~/Desktop/book1.csv")
        
    tell application "Microsoft Excel"
        
        open fp
        
    end tell
end run

-- GENERIC ----------------------------------------

-- filePath :: String -> FilePath
on filePath(s)
    ((current application's ¬
        NSString's stringWithString:s)'s ¬
        stringByStandardizingPath()) as string
end filePath
#6

Have you tried opening the Excel 2016 Scripting Dictionary, in either SD7 or Script Editor?

#7

@D_ave_G,
It also works with Excel 2011. So a good solution for both Excel versions.

#8

I have indeed, always and from the get-go.

My apologies for being ambiguous in terms of what I meant by “documentation”. The Scripting Dictionary is the standard repository for an application’s AppleScript vocabulary, and thus the definitive AppleScript documentation for an application. By documentation, in this case, I was referring to the “Excel 2004 AppleScript Reference”, a massive, 462 page tome that Microsoft released for Excel 2004 14 years ago. I have seen other vendors do similar things in terms of documenting their application’s AppleScript functionality on a MUCH smaller scale, but nothing so large or thorough as Microsoft’s 2004 document. Indeed, even Microsoft itself gave it up after 2004, as nothing has been released since, at least from what I can tell.

The Scripting Dictionary for 2011 and 2016 are identical, with the exception of 4 new types in 2016 (see below for the 2016 Command Syntax section):

XlPlatform for parameter “origin”, instead of type “origin” for parameter “origin”
XlTextParsingType for parameter “data type”, instead of type “type9” for parameter “origin”
XlTextQualifier for parameter “text qualifier”, instead of type “text qualifier” for parameter “text qualifier”
XlColumnDataType for parameter “field info”, instead of type “list” for parameter “field info”

open text file
open text file (verb) : Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data. (from Microsoft Excel Suite)

COMMAND SYNTAX
open text file filename text
origin XlPlatform
start row integer
data type XlTextParsingType
text qualifier XlTextQualifier
consecutive delimiter boolean
tab boolean
semicolon boolean
comma boolean
space boolean
use other boolean
other char text
field info list of XlColumnDataType
decimal separator text
thousands separator text

#9

Hello ComplexPoint,

Thank you very much for both of your tour de force responses with solutions in both JavaScript and AppleScript Obj-C. They do indeed work, and I greatly appreciate you taking the time to develop and share both solutions with everyone.

The problem is, both solutions are workarounds to what appears to be either the inability of Microsoft’s implementation of AppleScript support in a Sandboxed environment to open or save files not in a containerized hierarchy, or the prohibition of an application to open or save files outside of a containerized hierarchy enforced by sandboxing itself.

My original solution, also a workaround, was to use the open command at the shell script level, short and sweet, which also works:

set thePosixPathToLogfile to “/Users/D_ave_G/Downloads/2018-06-26_test.csv”

tell application “Microsoft Excel”
do shell script "open -a “Microsoft Excel” " & thePosixPathToLogfile
end tell

The problem here, too, is that my solution is a workaround, and what I am trying to do here is to see if there is a way to do the operation in “proper” AppleScript - aka, using the documented AppleScript vocabulary in the Scripting Dictionary of Excel.

Please don’t get me wrong - I love a good workaround: any port in a storm…

Unfortunately, even with a workaround that hacks around the AppleScript limitations for opening a file in a sandboxed environment, it appears that we have to rely on Excel’s AppleScript to do a “save as” on the file to convert it to a workbook format instead of a CSV format, and save commands do not work any better in a sandboxed environment than the “open text file” command does, so we’re stuck either way.

Thank you again for your time and advice - both of which I truly appreciate.

Regards,
Dave

#10

My lingering curiosity is about why the JXA version works. It appears, at first sight at least, to be consistent with the advertised API …

Testing again, this seems to work here in the current version of Excel (16.14.1):

use AppleScript version "2.4"
use framework "Foundation"
use scripting additions

set fp to filePath("~/Desktop/book1.csv")

tell application "Microsoft Excel"
    open text file filename fp
end tell

-- GENERIC -------------------------------------------------
-- https://github.com/RobTrew/prelude-applescript

-- filePath :: String -> FilePath
on filePath(s)
    ((current application's ¬
        NSString's stringWithString:s)'s ¬
        stringByStandardizingPath()) as string
end filePath
#11

Perhaps the difference between Excel versions is in the default expectation of file path strings (HFS vs Posix) ?

#12

It’s a good thought, one originally posed by @JMichaelTX . The HFS path string works just fine in the code example Jim provided in Excel 2011. I’ve always just naturally gravitated to Posix paths when working with do shell script because, in a *nixy environment, it’s always been my understanding that slash delimited paths were the only game in town.

As an aside, I’ve always relied on my old CLI friends in the shell for a variety of things that are easier, more powerful, less awkward, or simply possible than is the case with AppleScript equivalents. For example, I much prefer sed/gsed and a nice regex to the arduous and painful hoops AppleScript makes you jump through for text manipulation. Part of that, of course, stems from the tendency people have to use the tools they know and love. IMHO, Apple got a lot of things right by extending AppleScript with do shell script, as you can bring in just about anything you can imagine. And feeding native AppleScript embedded in a shell script within a heredoc via our friend osascript is so amazing as, to quote Douglas Adams, to “make a brave man weep”…:slight_smile:

#13

Use of ASObjC, through verbose, makes RegEx easy and possible in AppleScript. Here’s a RegEx handler I refactored from @ShaneStanley’s scripts. I put it in my Script Library, and just call it when needed.

RegEx Handler to Return Capture Groups

There is also a handler for RegEx Change, and other use cases elsewhere in this forum.

ASObjC RegEx Change Handler
#14

Talk about a red herring! In retrospect, it looks like this has nothing to do with sandboxing - which was an errant assumption on my part.

Thanks to DrewMcD’s 2014 post over at Stack Overflow, the syntax necessary to do the Save in Excel is in hand and tested under Excel 2016. I wanted to share the code DrewMcD provided and close the loop with my sincere thanks to Jim and ComplexPoint for all of their help

set workbookName to "Test.xlsx"
set destinationPath to (path to desktop as text) & workbookName

tell application "Microsoft Excel"
	set name of active sheet to "Sheet1"
	tell workbook 1
		tell sheet 1
			save active workbook in destinationPath as Excel XML file format
		end tell
	end tell
end tell