Excel unresponsive problem

I have one of those nasty jobs that involves taking things written in depths of antiquity (well, last touched 10 years ago, written in 2000) and trying to get them functional on a new machine. The things being a cobbled together system of Applescript and Excel, including several Excel 4 Macros. I am trying to take this creaking device (known to work fine using Excel 2011 on Snow Leopard) and get it working with Excel 2011 on Mavericks. So the problems are with the transition from Snow Leopard to Mavericks, not with the Excel version. (I will try a Yosemite upgrade just in case this is a known Mavericks bug)

I have two problems.

One is that sometimes, a worksheet is opened and activated, but the script stalls unless (the worksheet is left as a plain white sheet, not even the normal grid lines visible) unless I manually interact with it in some way (all I need to do is just click on it). This is just straight Applescripts, admittedly perhaps using an old idiom for file opening, but as I said, works perfectly on earlier systems. This does not happen when I step through the script in Script Debugger/Applescript Editor, only when I run it normally.

The second problem also only happens when I run it through normally, but not when I step through in Script Debugger - at one point in the middle of an old Excel 4 Macro (using the ‘run XLM Macro’ command), it halts and beachballs for some minutes. It does this immediately after receiving input from a user form. But it does NOT do this if I am stepping through in the Debugger! Or when run on an earlier OS. So it is not a problem with the script per se, but something dependent on how it is called, presumably related to the previous issue. Any suggestions?

I know the ‘right’ way to deal with the second issue is probably to rewrite the Macro in VBA, but I’m reluctant to do this, especially if I am not sure if it will solve the problem.

Hi davecake,

Welcome to MacScripter!

I work with Excel 2011 and the script below works for me when opening and closing workbooks.
As you can see I put a loop in to execute the opening and closing a few times.

set thisItem to (path to desktop folder as string) & "test-workbook.xlsm"


tell application "Microsoft Excel"
	
	activate
	
	repeat with i from 1 to 5
		
		open thisItem
		
		set value of cell "B3" to "xxxxxx"
		
		delay 2
		
		run XLM macro "testMacro"
		
		delay 1
		
		close front workbook saving no
		
	end repeat
	
end tell

Not sure about your second query, it’s been a little while since I worked with Excel Macros.

HTH

[EDIT]
Just tried recording a new macro in Excel and executing the amended script above.
All worked fine once I’d saved the workbook as a ‘.xlsm’ (Excel Macro-Enabled Workbook) format.
I also switched off the Macro Security option, in the Excel Security Preferences, to stop the warning dialog appearing.

I finally worked this one out.
A couple of files that were causing trouble had external links, so I was using

open workbook workbook file name (workbookpath & “filename.xls” update links update external links only

instead of a normal open - and unless I did this in just the right order of opening files etc, some things would neither fail nor issue a warning, but just take a really long time. Absolutely no indication of what was going on (no dialogs, errors, etc) but clearly something was working in the end, but only via a very time consuming and roundabout mechanism.

Once I sorted out opening files in just the right way (in this case, using Excel commands not Finder ones), and in just the right order, etc it all worked fine.
So, the ways of Excel are subtle and mysterious.

David