Menu bar script runs differ from Script Editor runs

Scripting Forums AppleScript | Mac OS X
#1

As a newbie, I am unsure why a script that calls an Excel macro runs to completion from Script Editor, but stalls upon reaching that macro call when run from the menu bar. I’ve been careful to ensure that I’m using the same script file, but cannot see the problem. Can anyone help me?

#2

If you post the script, we can certainly try.

#3 
set savePath to ("Users:Daily:Documents:Properties:Birdwood Road:Solar & Battery Power:" as string)
tell application "Mail"
	set theMessage to a reference to first item of (get selection)
	if exists mail attachments of theMessage then
		set attachment_file to first mail attachment of theMessage
		set attachment_name to name of the first mail attachment of theMessage
		set theOpenedFile to open for access file (savePath & attachment_name) with write permission
		save attachment_file in savePath & attachment_name
		close access theOpenedFile
	end if
end tell
tell application "Microsoft Excel"
	activate
	open savePath & "Solar System Performance Data.xlsm"
	run VB macro "PopulateSheet"
	quit application "Microsoft Excel"
end tell
display dialog "All done!" buttons {"OK"} default button 1
#4

Can you post a copy of your Excel spreadsheet so I can test it?

#5

It is a complex spreadsheet tracking my solar system & battery performance. It finds a csv file attached to a selected email, saves it, then copies its contents into the said spreadsheet, moves the data around and ends up with a single line in the first sheet of the spread. All of the movement in the spread is performed by the macro, which works well independently of the script; it is the initiation of the macro that doesn’t work when I call it from the menu bar. The whole thing works when initiated from the Script Editor’s Run command. I cannot see how to attach an Excel file to this response; only pdf’s seem possible.

#6

Since Im not an Excel macro guy, can you post a simple Excel file with a VBA macro with the same name so I can test?

#7

change the “quit application “Microsoft Excel”” line to just “Quit”

Also since your Macro probably made changes to the File, you should have a command in here to save the changes or when you send the Quit command, it will pause till you deal with the dialog that will pop-up to ask to save changes.

#8

Hi robertfern, I appreciate your looking at this for me. The macro saves the file once it has made those changes, without any pause to wait for me to action anything. I should have mentioned that the point at which the menu-initiated call stops working is immediately after the line "open savePath …’ and just before the macro should execute; the file opens on my desktop and I can then initiate the macro myself with either a button or a key. The script does not continue after the macro’s successful completion, so I close the file manually myself.

Again, I cannot see how to attach an Excel file to this reply.

#9

I just realized your savePath is not good. It must be a full path. It is missing the Disk name.

Try this…

set savePath to (path to documents folder as text) & "Properties:Birdwood Road:Solar & Battery Power:"

Also when I run it, another pop-up asks if I want to enable Macros.

#10

Changing the savePath as you’ve suggested did not affect the performance in any way, although I recognise the apparent need to specify the Disk name. Of course, the previous savePath was being recognised because the file was opening no matter which method I used to initiate the command.

I don’t get a Macro request pop-up in either method.

#11

I fixed my security macro pop-up. It was a setting in Excel.
My script is just the Excel portion of yours and it runs fine for me both from the script editor and from the menubar, although it still asks to save changes.

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

set savePath to (path to documents folder as text) & "Properties:Birdwood Road:Solar & Battery Power:"
set saveFile to savePath & "Solar System Performance Data.xlsm"
tell application "Microsoft Excel"
	activate
	open saveFile
	run VB macro "PopulateSheet"
	save saveFile
	quit
end tell
display dialog "All done!" buttons {"OK"} default button 1

** EDIT ** change savePath back to original, added saveFile

#12

I had to retain the actual target file name separately from the savePath because I save the email attachment into that same folder temporarily using that savePath (I delete it in the macro after the hard work is done). The other difference with my script remains that I don’t use a “save savePath” command because I save the amended file in the macro.

I cannot see any other differences, yet mine still doesn’t run past the “run VB macro” command when initiated from the menu.

#13

Did you shorten the quit line to just “quit” ?

#14

I did shorten that command.

#15

The only other thing I can think of is the VBA Macro is not returning properly.
Any way to post the code for that?

or again can you post the full Excel file? like so…
Solar System Performance Data.xlsm.zip (11.7 KB)

#16

The Macro was working successfully for several months (years?) before I thought to try my hand at wrapping it in AppleScript, so I don’t have any doubt that it reads the saved data file, moves the data into the correct places in the Excel file and saves the file. I used to just open the Excel file after saving the mail attachment in the nominated folder, and I could initiate the macro with the Macro button or a Ctrl-t combination. For that reason, I don’t think the problem is caused by the macro not returning correctly; this fault actually prevents the macro running.

I wonder if the menu-initiated operation fails to open the macro because it cannot find the macro. I haven’t refreshed my memory about Personal Macro files in Excel, so I will do that before taking any further steps, in case I need to specifically open such a file.

I’m reluctant to upload the file and a relevant data file to test it out, because its operation is not simple to set up in a foreign environment and may deceive you into believing it is not working for no other reason than it doesn’t find the right file or folder.

#17

Is there a typo in the name of the Macro.
Make a version with fake data

#18

There is no typo, and that macro runs when the script is called from the Script Editor, so that confirms its name is correct. I have tried placing the macro in a personal macro file, but, so far, neither initiation of the script can find that macro file, so I need to do more work on it.