From: "Data _null_;" on
Perhaps FILTER.SHOW.ALL

Equivalent to choosing the Show All command from the Filter submenu on
the Data menu. Displays all items in a filtered list.

Syntax

FILTER.SHOW.ALL()

On 10/29/09, Ya Huang <ya.huang(a)amylin.com> wrote:
> Hi there,
>
> Anyone experienced in reading (use DDE) a Excel file with autofilter turned
> on? Assuming a sheet with 100 rows, someone used it to add or
> delete data, for that he used the autofilter to find the 20 rows
> he want to edit. At the end, he forgot to turn off the filter (or
> choose all from the pull-down menu). A DDE code to read the sheet
> will return the 20 rows only.
>
> I guess what I'm looking for is a way to turn off the filter first,
> then read.
>
> Thanks
>
> Ya
>
From: "Huang, Ya" on
Great! Thanks null, you saved my day.

The code has been changed to the following and it worked.

options NOXWAIT NOXSYNC xmin;
x "start excel";

data _null_ ;
x = SLEEP(3) ;
run ;

filename cmds DDE 'excel|system' ;

data _null_ ;
file cmds ;
put '[FILE-OPEN("C:\temp\test.xls")]';
put '[FILTER.SHOW.ALL()]';
run ;


-----Original Message-----
From: Data _null_; [mailto:iebupdte(a)gmail.com]
Sent: Thursday, October 29, 2009 8:51 AM
To: Huang, Ya
Cc: SAS-L(a)listserv.uga.edu
Subject: Re: DDE to read Excel file with autofilter turned on?

Perhaps FILTER.SHOW.ALL

Equivalent to choosing the Show All command from the Filter submenu on
the Data menu. Displays all items in a filtered list.

Syntax

FILTER.SHOW.ALL()

On 10/29/09, Ya Huang <ya.huang(a)amylin.com> wrote:
> Hi there,
>
> Anyone experienced in reading (use DDE) a Excel file with autofilter
> turned on? Assuming a sheet with 100 rows, someone used it to add or
> delete data, for that he used the autofilter to find the 20 rows he
> want to edit. At the end, he forgot to turn off the filter (or choose
> all from the pull-down menu). A DDE code to read the sheet will return

> the 20 rows only.
>
> I guess what I'm looking for is a way to turn off the filter first,
> then read.
>
> Thanks
>
> Ya
>