Re: Excel (2004) Hidden rows show up in tab delimited text file
Re: Excel (2004) Hidden rows show up in tab delimited text file
- Subject: Re: Excel (2004) Hidden rows show up in tab delimited text file
- From: Paul Berkowitz <email@hidden>
- Date: Wed, 29 Jul 2009 17:21:39 -0700
- Thread-topic: Excel (2004) Hidden rows show up in tab delimited text file
Title: Re: Excel (2004) Hidden rows show up in tab delimited text file
This will replace the original worksheet with the "visibly same" content minus the hidden rows. Then save it as a tab delimited file the same way you were doing before:
tell application "Microsoft Excel"
set ur to used range of active sheet
set visibleRows to {}
repeat with eachRow in (get rows of ur)
if not hidden of eachRow then set end of visibleRows to item 1 of (get value of (contents of eachRow))
end repeat
set c to count visibleRows
set newRange to get resize ur row size c
clear range ur
set value of newRange to visibleRows
end tell
The critical command (in the repeat loop) checks whether the row is hidden, and if it isn't, adds the value (i.e. what you see in the cells - text, numbers, whatever) to the visibleRows list. You have to use the explicit 'get' value (just as in the repeat loop itself 'get rows') to evaluate the _expression_ to the actual value from list item. Because the result - each row - is itself a range, it's expressed with double braces {{cell, content, here}}. To turn the final list visibleRows into a new range it must consist of a list {list of rows} of which each item (each row) has only one set of braces, not two. That's why the script gets 'item 1' of the value - that's a single-braced list.
'get resize' is a simple way to create a new range with the correct number of (visible only) rows, without having to bother counting the list, counting the columns, working out the letter name of the column from the address of the last cell, etc. etc..
'clear range' empties the worksheet.
Then you can set the value of the resized range to the list visibleRows you had extracted.
You have to use a repeat loop because unfortunately 'every row of ur whose hidden is false' doesn't work - it just times out - as with all 'whose' clauses on rows, columns and cells, even in a limited used range. They fail. But the repeat loop is pretty fast nonetheless. With this type of repeat loop (repeat with variable in list), you always have to remember to use the explicit 'get' (or a variable for the list) and especially to evaluate 'contents of...' Or you can use this construction, where you can forget some of those annoyances:
set allRows to rows of ur
repeat with i from 1 to count allRows
set theRow to item i of allRows
if not hidden of theRow then set end of visibleRows to item 1 of (get value of theRow)
end repeat
--
Paul Berkowitz
> From: Bert Groeneveld <email@hidden>
> Date: Thu, 30 Jul 2009 00:11:25 +0200
> To: AppleScript-Users <email@hidden>
> Subject: Excel (2004) Hidden rows show up in tab delimited text file
>
> Hi, I have a stay open script (headless script, on idle script,
> whatever you call it) that every 30 seconds watches an in-folder for
> available Excel files. As soon as an Excel file is available, the
> script tells Excel to open it and save it as a tab delimited text file
> in an out-folder for further processing. So far so good. But:
> Sometimes (unpredictable) the Excel file has a lot of invisible rows
> (the Excel operator has sometimes turned on the autofilter option that
> hides thousands of rows). The hidden rows are of no interest to me.
> I'm only interested in the (for example one hundred) rows that I can
> see. But when I tell Excel to save as tab delimited text file,
> unfortunately all the invisible rows show up in the text file. And all
> I want is a text file that contains only the visible rows. Can anyone
> help by telling me how to work around this. It would be nice if
> someone could post example code. I'm losing too much time on this.
>
> Bert.
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> AppleScript-Users mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
> Archives: http://lists.apple.com/archives/applescript-users
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden