I need a "hide" script for Microsoft Excel

Question

Level 1

0 points

I need a "hide" script for Microsoft Excel

Hi. I just "upgraded" everything, new Intel based Macbook Pro, and Office 2008 for Macs. Why did I upgrade? Good question! Anyways, when I used Office 2004 for macs, I used a macro to hide several rows so that I could view information in a condensed format. then, I would unhide, and have the entire spreadsheet once again.

I am new to applescript, and have been trying to absorb as much as I can. So far, I wrote a script that can select and range, and fill in a color.

Anyways, I need a script that can hide several rows (ranges?) in a spreadsheet, and then a script to unhide them again.

Thanks!

Timo

macbook pro, Mac OS X (10.5.4), Microsoft Excel 2008 for Macs

Posted on Sep 7, 2008 6:26 PM

Reply

Answer 1

Ettor

Level 2

285 points

Sep 7, 2008 8:24 PM in response to Timorama

No need for Applescript for this task, Timorama. To hide rows or columns (and thereafter unhide them) assign a keystroke combination to the Excel>Format menu for each command. You can do this in Excel alone. Go to the Customize Keyboard item under the Tools menu. Assign your keystroke combination there.

Reply

Answer 2

Timorama Author

Level 1

0 points

Sep 7, 2008 9:01 PM in response to Ettor

But I need to hide about 150 rows. When I had the use of Macros with Excel 2004, I could record all the the actions, and then implement the saved Macro.

Can I do this with the method you describe, or is there a better way to do this with Applescript?

T

Reply

Answer 3

Hiroto

Level 5

7,461 points

Sep 9, 2008 1:53 PM in response to Timorama

Hello

Perhaps something like this.
It is based upon 'Excel 2004 AppleScript Reference' that you can obtain from the link below but NOT tested, for I don't have this application. Also Excel 2008 may vary.

(* hide rows 1 through 5, 20 through 25 and 30 through 40 *)
tell application "Excel"
tell active sheet
set hidden of range "1:5,20:25,30:40" to true -- hide
end tell
end tell

(* un-hide rows 1 through 5, 20 through 25 and 30 through 40 *)
tell application "Excel"
tell active sheet
set hidden of range "1:5,20:25,30:40" to false -- un-hide
end tell
end tell

cf.
http://www.microsoft.com/mac/developers/default.mspx
Excel2004AppleScriptRef.pdf

Table Suite > Table Suite Classes > Class: range (> properties > hidden)
p.331

Using the Excel Dictionary > How to reference cells and ranges
pp.15-18

Good luck,
H

Reply

Answer 4

Timorama Author

Level 1

0 points

Sep 9, 2008 3:20 PM in response to Hiroto

Thanks Hiroto!

I used your script, and got the error message:

*Microsoft Excel got an error: Can’t set hidden of range "1:5,20:25,30:40" of active sheet to true*

I'm not sure what that means, but maybe the "hidden" or "Hide" feature is not part of applescript? I checked the applescript dictionary and the terms "hidden field" and "hidden item" are there. I substituted those in your sample script, but got the same message.

Thanks for the help, but I'm still stumped.

Timo

Reply

Answer 5

Hiroto

Level 5

7,461 points

Sep 9, 2008 4:17 PM in response to Timorama

Hello Timo,

Well, I don't know why it fails with such error.
Possibly the range reference for setting and getting its 'hidden' property must be an entire SINGLE row or column...? For test, I'd try the following simple version.

tell application "Excel"
tell active sheet
set hidden of range "5:5" to true -- hide row 5
end tell
end tell

If you can hide the row 5 manually and yet this script fails to hide row 5, I'd conclude either Excel 2008 is different from Excel 2004 in this regard or 'Excel 2004 AppleScript Reference' is not accurate in the first place.

If this script works, you'd be able to write one 'set hidden of range ...' statement per each row to hide. Tedious, it might be.

Hope this may help.
Hiroto

Reply

Answer 6

Timorama Author

Level 1

0 points

Sep 9, 2008 4:37 PM in response to Hiroto

Hiroto,

I gave that a go, and got the error message:

Microsoft Excel got an error: Can’t set hidden of range "5:5" of active sheet to true.

This is basically the same error message. I suspect you are correct, that something is different with Microsoft Excel 2008, or Applescript has changed. Its frustrating that Excel has been "improved", has totally dropped macros, and recommends Applescript instead. Then, with some thing as seemingly simple as hiding a row (or several), it is difficult, if not impossible to do.

But I digress. Thanks so much for your thoughts and ideas!

Timo

Reply

Answer 7

Timorama Author

Level 1

0 points

Sep 9, 2008 6:33 PM in response to Timorama

Hiroto,

I have been reading more examples in the 2004 guide you provided the link to. I made a minor change to the script, and added the word "format" as follows:

(* hide rows 1 through 5, 20 through 25 and 30 through 40 *)
tell application "Microsoft Excel"
tell active sheet
set hidden of format of range "1:5,20:25,30:40" to true -- hide
end tell
end tell

The script appears to run, no error messages appear. However, nothing at all happens to my excel file. I think there are issues either with Excel 2008 or Applescript or both. Sheesh.

Timo

Reply

Quick Links

I need a "hide" script for Microsoft Excel