I am trying to open a password protected Microsoft Excel file using AppleScript.
I have written two different AppleScripts and both “fail” when trying to enter the workbook password.
The first script is as follows:
tell application "Microsoft Excel"
activate
set TheFileToOpen to "/Users/JoelC/Documents/Projects On The Go/Budget and Spend Tracking/20140101_budget and expenditure.xlsx"
set TheWorkBook to "20140101_budget and expenditure.xlsx"
set TheWorksheet to "Budget_2021"
set ThePassword to "abcdefgh"
open TheFileToOpen
tell application "System Events" in window "Password" of "Microsoft Excel" to keystroke ThePassword
end tell
The second script is as follows:
ell application "Microsoft Excel"
activate
set TheFileToOpen to "/Users/JoelC/Documents/Projects On The Go/Budget and Spend Tracking/20140101_budget and expenditure.xlsx"
set TheWorkBook to "20140101_budget and expenditure.xlsx"
set TheWorksheet to "Budget_2021"
set ThePassword to "abcdefgh"
open TheFileToOpen
unprotect workbook TheWorkBook password ThePassword
unprotect sheet TheWorksheet password ThePassword
end tell
Open in Script DebuggerOpen in Script Debugger
I would GREATLY appreciate any and all help in getting this to work.
Try opening the workbook using the ‘open workbook’ command.
set TheFileToOpen to (path to documents folder) & "Projects On The Go:Budget and Spend Tracking:passbook.xlsx" as text
tell application "Microsoft Excel"
activate
open workbook workbook file name TheFileToOpen as text password "abcdefgh" write reserved password "abcdefgh"
end tell
Note the two password options… the first is to open the document, the second is to allow editing.
NB You don’t mention your versions but I’m using Sierra and Excel 2011, if that makes a difference. Also, what happens when your scripts ‘fail’? Is there an error message? If so, what is it?
If you really want to use the standard open (instead of Excel’s open workbook) and use UI scripting to work the dialogues, then here is how you might approach that:
tell application "Microsoft Excel" to activate
tell application "System Events" to tell application process "Microsoft Excel"
tell window "Password" -- open file
set value of text field 1 to "abcdefgh"
delay 0.2
perform action "AXPress" of button "OK"
end tell
delay 0.2
tell window "Password" -- enable editing
set value of text field 1 to "abcdefgh"
delay 0.2
perform action "AXPress" of button "OK"
end tell
end tell
Again, the second window is to enter the password to enable editing for the workbook. If you do not wish to enable that, then remove the ‘set value’ line and change the button to “Read Only” from “OK”.
@Mockman, as a start, a HUGE thank you as your suggested code worked. I will admit that I am little discouraged as there i no way that I would have figured that out on my own, I am finding the AppleScript syntax tough to understand.
I now have an almost finished script I just need to figure out how to get the Excel window to minimize (i.e. it is the same problem that I noted https://macscripter.net/viewtopic.php?id=48806 )
I am running Office 365 for Mac and OS X 10.15 (i.e. Catalina).
Glad it helps. Incidentally, one of the other advantages in using ‘open workbook’ is that you can assign it to a variable, which then references the newly opened workbook, e.g. set someDoc to open workbook workbook file name….
Try ‘collapsed’. I have no idea what ‘miniaturize’ does, it’s not in the 2011 dictionary but ‘collapse’ will ‘minimize’ the window (equivalent to typing command-m or selecting Minimize Window from the Window menu).
Look around for Microsoft’s documentation. They have a significant document for each of the three main (at the time) office apps, usually with ‘2004’ in the name, as that’s the version of Office that they were released around. While they’re chock full of duplicate stuff (especially the Excel doc), they do provide some discussion and usage examples for most of the commands that were available at the time.