Applescripting Excel 2004: Selecting multiple worksheets

[Brad Collicott]

I'm converting a script from Office V.x to 2004 that creates multiple
worksheets --up to a couple hundred at a time-- and selects a range of them
for manual printing.

The only part of the script that I can't translate to Excel 2004 is the last
function that selects multiple sheets.

In the old version, I found I could specify a range by using a list
variable:

set sheetRange to {2,3,4}
tell application "Microsoft Excel"
activate
select worksheet sheetRange
end tell

Any suggestions?

Brad

 

[Paul Berkowitz]

I'm converting a script from Office V.x to 2004 that creates multiple
worksheets --up to a couple hundred at a time-- and selects a range of them
for manual printing.

The only part of the script that I can't translate to Excel 2004 is the last
function that selects multiple sheets.

In the old version, I found I could specify a range by using a list
variable:

set sheetRange to {2,3,4}
tell application "Microsoft Excel"
activate
select worksheet sheetRange
end tell

Any suggestions?

This ought to work (and a coercion omitting active workbook too):


tell application "Microsoft Excel"
activate
tell active workbook
select {worksheet 2, worksheet 3, worksheet 4}
end tell
end tell


But it doesn't. Excel can't select a list, it appears. This seems to be a
bug.
--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Brad Collicott]

Oh, crap.

 

[Brad Collicott]

I was able to bypass that little issue via a vb script without too much
difficulty.

Applescript fragment:

set sheetRange to {2,3,4, etc}
run VB macro "cleanSheets" arg1 sheetRange

And the additional VB Script:

Sub cleanSheets(num)
'
' cleanSheets Macro
Sheets(num).Select
End Sub

Not as simple as before, but it works for me since I'm starting off with an
Excel template into which I can embed the macro.

Thanks for your help, Paul. It's immensely helpful to know when you're
barking up the wrong tree! ;-)

Cheers,
Brad

 

[Paul Berkowitz]

It's interesting that 'run VB macro' will bridge the AppleScript list {2, 3,
4} as a VB array that should be (Array(2, 3, 4)) in the macro argument:

Sheets(Array(2, 3, 4)).Select

That's a nice, undocumented coercion/bridge.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Brad Collicott]

Thanks! It was a stab in the dark to condense the VB script from:

Sheets(Array(2, 3, 4)).Select

down to:

Sheets(var).Select

particularly since I have never written a VB script, but like you I was
surprised that the list was passed intact via arg1.