macOS Applescript with Excel

[tayjayt]

I am using Applescript to automatically calculate an excel spreadsheet every second, in order to accurately keep time on an hours spreadsheet for work. Here is the command line:

global iteration_interval
on idle
	tell application "Microsoft Excel"
		try
			calculate
		end try
	end tell
	return iteration_interval
end idle

on run
	set iteration_interval to 1
end run

I am running this as an application, and it works for what I need it to, but it is sloppy for a few reasons.

I want the application to open the file called “Hours.xlsm” when I open the app (which I have called “Runner”). I know I can specify the path to “hours.xlsm”. ie. TaylorJamesesktop:OG:Hours. That is sloppy, because I want to be able to move the file around, share, etc., and have the application search for it and open it wherever it is stored. Maybe I can even store the spreadsheet within the application resource contents?

The next problem is this. When the application “Runner” is operating, I cannot work in any cell in the spreadsheet, or any other spreadsheet for that matter, because obviously it is calculating every second. Can I specify the cells that it calculates, or at least the spreadsheet? Maybe I’m asking too much or maybe I should use a more robust programming tool. Applescript, I know, isn’t anything close to Java. Any help so very much appreciated! Regards, Taylor.

 

[kryten2]

have the application search for it and open it wherever it is stored

Took a look at the mdfind and mdls commands. Man mdfind and man mdls will give you more info on how to use them.

Can I specify the cells that it calculates, or at least the spreadsheet?

Open the Excel dictionary in Script Editor.

Quote from the Excel 2004 dictionary :

Command: activate object
Activates the object.
Syntax
activate window/sheet/workbook/pane Required. An expression that returns a window, sheet,
workbook, or pane object.
Example
This example activates Sheet1.
activate object worksheet "Sheet1"
This example selects cells A1:C3 on Sheet1 and then makes cell B2 the active cell.
activate object worksheet "Sheet1"
select range "A1:C3" of active sheet
activate object range "B2"
This example activates Book4.xls. If Book4.xls has multiple windows, the example activates the first
window, Book4.xls:1. The application must be activated for this example to run successfully.
activate object workbook "Book4.xls"