An open community 
of Macintosh users,
for Macintosh users.

FineTunedMac Dashboard widget now available! Download Here

Topic Options
#12808 - 11/17/10 03:01 PM Problem w/ AppleScript & Automator on Excel 2008
JoBoy Offline


Registered: 08/05/09
I'm a non-programmer. I need to automate a simple process in Excel where I copy a formula's numerical value from one cell and "paste special" into a different cell after I select the "values" and "multiply" radio buttons in "paste special." I've tried to get AppleScript and also Automator to do this job by recording my keystrokes, but neither one will record them so that I get the result I want. In fact, I can't seem to get AppleScript to record anything. I have Excel 2008. Will either of these apps do what I want if I can find the required instructions somewhere?


Edited by JoBoy (11/17/10 07:49 PM)
_________________________
Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top
#12837 - 11/21/10 04:50 PM Re: Problem w/ AppleScript & Automator on Excel 2008 [Re: JoBoy]
dkmarsh Offline

Moderator

Registered: 08/04/09

The recording function of Script Editor is pretty much non-existent in OS X, except for Finder (and even that's pretty flakey). I don't use Excel, so I can't give you any concrete suggestions, but Excel 2004 AppleScript Reference (<<pdf which seems to insist on downloading rather than displaying within Safari, regardless of pref setting) is a comprehensive (462-page) manual, much of which is probably applicable to Excel 2008 as well.
_________________________

dkmarsh • member, FineTunedMac Co-op Board of Directors

Top
#12838 - 11/21/10 05:13 PM Re: Problem w/ AppleScript & Automator on Excel 2008 [Re: dkmarsh]
JoBoy Offline


Registered: 08/05/09
Originally Posted By: dkmarsh

The recording function of Script Editor is pretty much non-existent in OS X, except for Finder (and even that's pretty flakey)...


If it's non-existent in OS X, does that mean it's being phased out by failure to maintain it until nobody wants to use it? I hate to invest in something that has no future. BTW, the link that you provided for the Excel 2004 AppleScript Reference downloaded in less than 10 seconds. That's pretty good for 1.8MB over the Internet. I'll see if it provides me with a way to get the job done.

Is there anything similar to this reference if I want to use Automator instead of AppleScript?

Thanks a lot for your response and suggestions. They're very much appreciated. smile

P.S. How tough is it for a non-programmer to become reasonably competent using Visual Basic? That seems to be what Microsoft wants us to use on Excel 2008. Visual Basic is also mentioned frequently in the pages I've looked at in the Excel 2004 AppleScript Reference to which you referred me.


Edited by JoBoy (11/21/10 05:43 PM)
_________________________
Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top
#12839 - 11/21/10 06:50 PM Re: Problem w/ AppleScript & Automator on Excel 2008 [Re: JoBoy]
alternaut Offline

Moderator

Registered: 08/04/09
Originally Posted By: JoBoy
P.S. ... [Visual Basic] seems to be what Microsoft wants us to use on Excel 2008. Visual Basic is also mentioned frequently in the pages I've looked at in the Excel 2004 AppleScript Reference to which you referred me.

The pdf DK linked to was made available by Microsoft after it had eliminated Visual Basic from Office for Mac in the transition from Office 2004 to Office 2008. The ensuing protests were loud enough for MS to provide some relief via AppleScript, and to re-introduce VB in Office 2011.
_________________________
alternaut moderator

Top
#12840 - 11/21/10 10:41 PM Re: Problem w/ AppleScript & Automator on Excel 2008 [Re: alternaut]
JoBoy Offline


Registered: 08/05/09
Ooops! That's right. I should have said 2011 instead of 2008. My mistake. I now have 2008 and was trying to make something happen with AppleScript, but found that I couldn't do it. I have no background in AppleScript. I then asked about the degree of difficulty in learning Visual Basic, but I intended to mean "for use in Excel 2011." I'd be willing to purchase 2011 if learning Visual Basic is not a huge mountain to climb for a non-programmer.
_________________________
Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top
#12848 - 11/22/10 06:45 AM Re: Problem w/ AppleScript & Automator on Excel 2008 [Re: JoBoy]
joemikeb Offline
Moderator

Registered: 08/04/09
Loc: Fort Worth, Texas
Originally Posted By: joboy
I'd be willing to purchase 2011 if learning Visual Basic is not a huge mountain to climb for a non-programmer.

After many years of teaching various aspects of software design, programming, etc. at the college freshman to engineering professionals I think it is safe to say the steepness of the hill depends in very large part on the individual. My suggestion would be to get a copy of something like Visual Basic for Dummies or even better and more to the point Excel VBA for Dummies from the library or bookstore to read. After that you would be in a position to make a better judgement of how high your personal learning curve would be than anyone else can possibly guesstimate. Even if you have to buy the Dummies Guide, $20 is not a bad investment before making a much more substantial investment in Office 2011 for Mac.
_________________________
joemikeb • moderator

Top
#12850 - 11/22/10 07:45 AM Re: Problem w/ AppleScript & Automator on Excel 2008 [Re: joemikeb]
JoBoy Offline


Registered: 08/05/09
That's excellent advice. Thank you. I 'll check it out. An answer to one question would be very helpful immediately: Is there any kind of recording feature in Visual Basic for Excel 2011 that would enable me to record a few simple moves such as copy and then paste special (values, multiply)? That would solve an immediate need. Then I could proceed with the general learning experience without undue pressure. I tried to do that one with AppleScript in Excel 2008, but gave up.


Edited by JoBoy (11/22/10 09:13 AM)
_________________________
Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top
#12855 - 11/22/10 01:08 PM Re: Problem w/ AppleScript & Automator on Excel 2008 [Re: JoBoy]
Ira L Offline


Registered: 08/13/09
Loc: California
Don't know about recording features in Visual Basic, but there are many applications that can record key strokes, etc. within other applications (e.g., QuicKeys to name just one).

In the long run it may be easier (and cheaper?) to get one of these instead of going with Visual Basic or something else that is internal to Excel.
_________________________
On a Mac since 1984.
Currently: 27" iMacs, Macbook Air, macOS 10.14.x,; iPhones, iPods and iPads galore!

Top
#12856 - 11/22/10 06:12 PM Re: Problem w/ AppleScript & Automator on Excel 2008 [Re: Ira L]
JoBoy Offline


Registered: 08/05/09
I used to have QuicKeys, but I quit using it when OS X came along. I've cut way back on my small-developer software and it has improved the stability and reliability of my setup.

I just downloaded QK and will try it out. Again, thanks for the suggestion.
_________________________
Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top

Moderator:  alternaut, dianne, dkmarsh