Getting past Excel's security

Very green apple scripter here and it’s my first post here as well. Sorry if it shows. I have a Mac Excel 2011 file that I want to start up in the middle of the night and run an embedded macro. I’m on OSX 10.12.6. The macro includes accessing a database on our network. I found clues as to how to get past Excel’s macro warning here: https://macscripter.net/viewtopic.php?id=34453 and I was able to make it work.

However, once I am in the spreadsheet, I still have a banner across the top of the spreadsheet that says “Security Warning Data connections have been disabled”. This is due to the fact that the macro queries a database. As a person, I can just click on the Enable Content button and my macro runs fine, but I don’t know how to automate clicking that button. Can anybody point me in the direction of where to find the name of the window and button (or if that is even the right approach for this button)?

Here is my code:


tell application "Finder"
	activate
	open document file "somefile.xlsm" of folder "Folder1" of folder "Folder2" of folder "Folder3" of startup disk
	delay 2
end tell

tell application "System Events"
	tell process "Microsoft Excel"
		set macros to button "Enable Macros" of window 1
		click macros
		-- delay 5
		-- set content to button "Enable Content" of window 2
		-- click content
	end tell
end tell

If I un-comment the 3 lines towards the end, I just get an error. It doesn’t click the button. Thank-you,
Lyle

Model: Mac Mini (Late 2014)
AppleScript: 2.5
Operating System: Mac OS X (10.12.6 beta 6)

Basically, this is just a bump. It is hard to believe that nobody has wanted to automate a data fetch process to wake up and run in the middle of the night, but nobody has posted even a nonworking suggestion. Of course nobody is responsible for responding to my question, and I can’t offer you anything other than the satisfaction of helping a stranger and my gratitude. Unless it really can’t be done… which would seem weird.

On the other hand, maybe it’s true, because my Google searches are turning up nothing. :frowning:

Thanks for your time.

You need to try to drill down through UI elements to find the right terminology to address the element you need.

I don’t know if Excel 2011 was a real Cocoa app or if it might have still been a Carbon app brought over from OS9 at that time. If it’s a Carbon app you might just be out of luck for UI scripting.

You’ve got 4 main options for trying to track down the terminology of UI elements for AppleScript:

  1. UI Browser https://pfiddlesoft.com/uibrowser/
  2. Accessibility Inspector https://developer.apple.com/library/archive/documentation/Accessibility/Conceptual/AccessibilityMacOSX/OSXAXTestingApps.html
  3. Script Debugger https://latenightsw.com
  4. Just using script editor to get the the top UI elements, picking one and getting it’s elements, repeating process until you find what you want.

Here’s a post: https://macscripter.net/viewtopic.php?id=46211

Giving some examples of how to find UI elements with just Script Editor.

Let me know how it goes or if that’s helpful. Feel free to post the results of some of the results returned for the UI elements.

Thanks for your help t.spoon

I will be digging into this. If I find a solution, I will certainly post it back here.

That UI Browser app is a pretty cool application!

When I click the “Switch to Screen Reader” button and mouse over the problem button, the window says:

ELEMENT PATH (starting at leaf element):
button “Enable Content” (button 2)
unknown (UI element 7)
unknown “layout container” (UI element 7)
standard window “Somefile.xlsm” (window 2)
application “Excel”

I’m pretty sure I can’t use “unknown” as the object type. Presumably there is a type and Excel just doesn’t report it? Is it likely there is some other method to find this out? Or do you just have to know it?

By the way, I was able to ascertain that I am working with a Carbon application: https://appleinsider.com/articles/10/06/09/microsoft_reveals_office_for_mac_2011_will_be_32_bit_only. Hoping it can still be made to function.

You should be able to address things by their UI element number when there’s nothing better available.

You can try:


tell application "System Events"
	tell application process "Excel"
		tell window 2
			tell UI element 7
				tell UI element 7
					tell button "Enable Content"
						click it
					end tell
				end tell
			end tell
		end tell
	end tell
end tell

A downside to this is that UI elements can change unexpectedly - you open a new toolbar or close one, which comes in order before the element you’re trying to address, and suddenly what was “UI element 7” becomes “UI element 6” or “UI element 8.”

So to make the script more robust, I’d recommend getting all the info you can on the things being address based on their order and see if there’s anything else you can target.

tell application "System Events"
	tell application process "Excel"
		tell window 2
			set elementProperties to the properties of UI element 7
			return elementProperties
		end tell
	end tell
end tell

Often you can find something in the properties that can be used for better targeting. Like role, role description, description, title, or such. Then you can use that for targeting so your script isn’t subject to the whims of current UI configuration. Say the “title” of your “UI element 7” is “Microsoft Self Destruct Button.” You could use

tell application "System Events"
	tell application process "Excel"
		tell window 2
			set element2 to the first UI element whose title is "Microsoft Self Destruct Button"
                                tell element2 -- etc
                                end tell
		end tell
	end tell
end tell

I also should warn you that in my experience, with Carbon apps, sometimes “click it” doesn’t work - sometimes it’s impossible to simulate the click with straight UI scripting in Applescript.

If you run into this situation, you can probably still get the position of the UI element through UI scripting, and then use a tool to move the mouse and click there. Clunky, but generally pretty reliable. We’ll look at that if “click it” doesn’t work.

One reason you didn’t get a bunch of people jumping on this to help you was probably that it’s hard for us to help when we can’t recreate the environment easily. Very few of us probably have Excel 2011, then we’d also have to create a document that brings up the button you want to dismiss. If I had that in front of me, I could probably make you a functioning script in 10 minutes. Walking you through it is harder, and can turn into a long process of “run this script and send me the feedback,” repeat process.

Worked like magic! Thank-you so much! Now on to automating the actual macro.

The first Element 7 had a title of “layout container”. I used your suggestion and it still worked. So then I tried the second Element 7 and got this result:
{minimum value:missing value, orientation:missing value, position:{63, 173}, class:UI element, role description:“unknown”, accessibility description:missing value, focused:missing value, title:missing value, size:{1318, 21}, value:missing value, help:missing value, enabled:true, maximum value:missing value, role:“AXUnknown”, entire contents:{}, subrole:missing value, selected:missing value, name:missing value, description:“unknown”}
Doesn’t seem like there is much in there to grab onto (to my untrained eye). I’m not sure, but I’m hoping to run this script on a seldom used computer in the middle of the night. Nobody will be using it much to mess up the UI, so it should be pretty stable.

Glad it’s working.

That second “Element 7” is pretty void of information.

You could see how many of the prior 6 elements have role "role:“AXUnknown”

If it’s all 6 of them, then that’s pretty useless. But if it’s none of them, then targeting it as

set element3 to the first UI element whose role is "AXUnknown"

Would probably be more reliable. Or if there’s only a few other elements with that “role,” then you’re still marginally safer using

set element3 to the third UI element whose role is "AXUnknown"

[or whatever]

But my guess is that you’re “OK” safe here. At a guess, it seems to me reasonably likely that the UI elements of Window 2 might change, but once you get one level beyond that, I’m guessing you’re on a toolbar or something whose elements probably don’t change spontaneously.

I found that the object in question was the first UI element with a role of “AXUnknown”. So after opening the file, here is the AS I will be using to get past Microsoft’s security.


tell application "System Events"
	tell process "Microsoft Excel"
		tell button "Enable Macros" of window 1
			click it
		end tell
		delay 2
		tell window 2
			set element2 to the first UI element whose title is "layout container"
			tell element2
				set element3 to the first UI element whose role is "AXUnknown"
				tell element3
					tell button "Enable Content"
						click it
					end tell
				end tell
			end tell
		end tell
	end tell
end tell

It works. Thanks again for your time and expertise!
(edited to use “click it” on the first button push too)