Feed
 

Who's an Automator Ace?

Avatar Rick Churchill
I am following a blog on how to use Automator to export contacts to an Excel spreadsheet as a way of understanding how Automator works. All was going well up to the point where the article told me to drag "Create New Excel Workbook" which should be in the sub-menu "Documents" under Library between "Developer" and "Files & Folders". Unfortunately I have nothing between these two items.

Can anyone help?

Is the reason that this item is missing because the action requires Microsoft support and it has been withdrawn since the article was written? I have Excel for Mac 2016

I also cannot see a way of opening a new Numbers spreadsheet to use instead?

Re: Who's an Automator Ace?

Avatar Trevor Hewson
I've nudged Tony a couple of times with a view to having a talk at Bournemouth on Automator but so far he's not managed to press gang a suitably knowledgeable speaker.

Keep up the good work Rick - and I look forward to hearing your talk sometime in the new year perhaps! :)

Re: Who's an Automator Ace?

Avatar Mick Burrell
New Workbook is on the File menu. I think pretty much all apps have New on the file menu.

Re: Who's an Automator Ace?

Avatar Rick Churchill
Thanks Mick but you are referring to the Excel programme not Automator. I am trying to get Automator to open a new Workbook.

Re: Who's an Automator Ace?

Avatar Mick Burrell
Sorry, yes, I was.

Very surprised that Apple have built anything into Automator that would deal with Excel. Indeed, a search for Excel turns up nothing on my copy of Automator. Can you provide a link to what you're following? I'll have a look.

Re: Who's an Automator Ace?

Avatar Rick Churchill
This is the link to the article I was following: How to use Mac Automator to export Contact to Excel

I have a screenshot but I don't know how to put this into this form.

Re: Who's an Automator Ace?

Avatar Mick Burrell
Rick - a little research suggests that Documents was installed in Automator by Word 2011 but was so problematic it was pulled by later versions of Office and I see you're running 2016. perhaps that's the issue.

Learn using a non Microsoft application?

Re: Who's an Automator Ace?

Avatar Rick Churchill
Thanks I thought it might be something like that. Microsoft are only now getting the full functionality of the macro editor in 2016 which was in 2011.

I can use "Set Application to use files" under the "Files and Folders"sub-heading. This could be Excel or Numbers but then I'm stuck. I cannot see at present how to copy from the clipboard and paste into the application. When I have time I'll have another go.

Re: Who's an Automator Ace?

Avatar Tony Still
Rick: I hope you're turning into that Automator Ace!

I have always been frustrated by the lack of actions available to Automator; I think they have to be provided by the application in the same way that it provides AppleScript functions. However, some generic functions are available and I think you should be able to manage your 'Create New Excel Workbook'.

You'll need a 'Run AppleScript' action, then insert the following fragment of AppleScript:

on run {input, parameters}
activate application "Excel"
delay 1.0
tell application "Excel"
tell application "System Events"
keystroke "n" using command down -- select all
end tell
end tell
end run

I have inserted the name "Excel" but check that that's what it's really called (it might be "Microsoft Excel", for example). You may also need to append the line 'return input'.

I think it's clear what this does, the delay is a hack because we're faking the user interface rather than using a 'proper' entry point. Enjoy.

So, are there really no AppleScript experts out there? I too would like a presentation on Automator, AppleScript or both.

Re: Who's an Automator Ace?

Avatar Rick Churchill
Thanks I'll have a go soon.
 
Feed