Can't create AutoFiltered Excel worksheet using VB.NET

John Brock · Joined: 04 Oct 2007 Posts: 13

I am trying to create a workbook where one of the worksheets contains
an AutoFiltered table. It looks like it should be simple -- what
I am doing is this:

rng = ws.Range("MyTableRange")
rng.AutoFilter()

Unfortunately I get an exception on the second statement, with the
unhelpful message:

AutoFilter method of Range class failed

The range is rectangular, and contains a legitimate table, with
column headers in the first row and data in most of the cells. If
I create the workbook without calling the AutoFilter statement,
then manually go to the range in question and set AutoFilter from
the Excel Data menu, it works as expected. The worksheet is intended
to be protected, but protection has not yet been set at the point
where I try to set AutoFilter.

I Googled for this, and found two other people complaining of the
same thing, with no answers. I suspect (and hope!) this means that
there is an answer -- otherwise there would be many more queries.

Any help would be gratefully appreciated!
--
John Brock
jbrock@panix.com

Archived from group: microsoft>public>excel>misc

Ron de Bruin · Joined: 04 Oct 2007 Posts: 2

Hi

See this page
http://www.contextures.com/xlautofilter03.html

--
Regards Ron De Bruin
http://www.rondebruin.nl

"John Brock" wrote in message $i64$1@reader1.panix.com...
>I am trying to create a workbook where one of the worksheets contains
> an AutoFiltered table. It looks like it should be simple -- what
> I am doing is this:
>
> rng = ws.Range("MyTableRange")
> rng.AutoFilter()
>
> Unfortunately I get an exception on the second statement, with the
> unhelpful message:
>
> AutoFilter method of Range class failed
>
> The range is rectangular, and contains a legitimate table, with
> column headers in the first row and data in most of the cells. If
> I create the workbook without calling the AutoFilter statement,
> then manually go to the range in question and set AutoFilter from
> the Excel Data menu, it works as expected. The worksheet is intended
> to be protected, but protection has not yet been set at the point
> where I try to set AutoFilter.
>
> I Googled for this, and found two other people complaining of the
> same thing, with no answers. I suspect (and hope!) this means that
> there is an answer -- otherwise there would be many more queries.
>
> Any help would be gratefully appreciated!
> --
> John Brock
> jbrock@panix.com
>

Jim Thomlinson · Joined: 04 Oct 2007 Posts: 2

Your syntax look a little off...

set rng = ws.Range("MyTableRange")
rng.AutoFilter

Give that a try.
--
HTH...

Jim Thomlinson

"John Brock" wrote:

> I am trying to create a workbook where one of the worksheets contains
> an AutoFiltered table. It looks like it should be simple -- what
> I am doing is this:
>
> rng = ws.Range("MyTableRange")
> rng.AutoFilter()
>
> Unfortunately I get an exception on the second statement, with the
> unhelpful message:
>
> AutoFilter method of Range class failed
>
> The range is rectangular, and contains a legitimate table, with
> column headers in the first row and data in most of the cells. If
> I create the workbook without calling the AutoFilter statement,
> then manually go to the range in question and set AutoFilter from
> the Excel Data menu, it works as expected. The worksheet is intended
> to be protected, but protection has not yet been set at the point
> where I try to set AutoFilter.
>
> I Googled for this, and found two other people complaining of the
> same thing, with no answers. I suspect (and hope!) this means that
> there is an answer -- otherwise there would be many more queries.
>
> Any help would be gratefully appreciated!
> --
> John Brock
> jbrock@panix.com
>
>

John Brock · Joined: 04 Oct 2007 Posts: 13

In article ,
Ron de Bruin wrote:
>Hi
>
>See this page
>http://www.contextures.com/xlautofilter03.html

The VBA (not VB.NET) example on that page is:

Sub TurnAutoFilterOn()
'check for filter, turn on if none exists
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter
End If
End Sub

I don't see how this is different than what I am doing now. My
'ws' variable *is* the active worksheet (in fact the only worksheet).
I tried using a single cell range, as in the example, but that
makes no difference. The AutoFilter statement still throws an
exception.

The example given for turning off AutoFiltering uses an AutoFilterMode
variable. I changed my code to:

ws.AutoFilterMode = true
rng = ws.Range("MyTableRange")
rng.AutoFilter()

But now the first statement throws an exception, with the even less
enlightening message:

Exception from HRESULT: 0x800A03EC.

There is also a ws.FilterMode, which is read-only, so I can't do
anything with it, and a ws.EnableAutoFilter variable, which looks
promising, and which I can successfully set true, but which doesn't
seem to make any difference.

Come on, *somebody* must have done this!

>"John Brock" wrote in message $i64$1@reader1.panix.com...
>>I am trying to create a workbook where one of the worksheets contains
>> an AutoFiltered table. It looks like it should be simple -- what
>> I am doing is this:
>>
>> rng = ws.Range("MyTableRange")
>> rng.AutoFilter()
>>
>> Unfortunately I get an exception on the second statement, with the
>> unhelpful message:
>>
>> AutoFilter method of Range class failed
>>
>> The range is rectangular, and contains a legitimate table, with
>> column headers in the first row and data in most of the cells. If
>> I create the workbook without calling the AutoFilter statement,
>> then manually go to the range in question and set AutoFilter from
>> the Excel Data menu, it works as expected. The worksheet is intended
>> to be protected, but protection has not yet been set at the point
>> where I try to set AutoFilter.
>>
>> I Googled for this, and found two other people complaining of the
>> same thing, with no answers. I suspect (and hope!) this means that
>> there is an answer -- otherwise there would be many more queries.
>>
>> Any help would be gratefully appreciated!
>> --
>> John Brock
>> jbrock@panix.com
>>
>
>

--
John Brock
jbrock@panix.com

Ron de Bruin · Joined: 04 Oct 2007 Posts: 2

Sorry, can't test in on this moment for you
I have a new machine and not have VB.NET installed on this moment

--
Regards Ron De Bruin
http://www.rondebruin.nl

"John Brock" wrote in message $kut$1@reader1.panix.com...
> In article ,
> Ron de Bruin wrote:
>>Hi
>>
>>See this page
>>http://www.contextures.com/xlautofilter03.html
>
> The VBA (not VB.NET) example on that page is:
>
> Sub TurnAutoFilterOn()
> 'check for filter, turn on if none exists
> If Not ActiveSheet.AutoFilterMode Then
> ActiveSheet.Range("A1").AutoFilter
> End If
> End Sub
>
> I don't see how this is different than what I am doing now. My
> 'ws' variable *is* the active worksheet (in fact the only worksheet).
> I tried using a single cell range, as in the example, but that
> makes no difference. The AutoFilter statement still throws an
> exception.
>
> The example given for turning off AutoFiltering uses an AutoFilterMode
> variable. I changed my code to:
>
> ws.AutoFilterMode = true
> rng = ws.Range("MyTableRange")
> rng.AutoFilter()
>
> But now the first statement throws an exception, with the even less
> enlightening message:
>
> Exception from HRESULT: 0x800A03EC.
>
> There is also a ws.FilterMode, which is read-only, so I can't do
> anything with it, and a ws.EnableAutoFilter variable, which looks
> promising, and which I can successfully set true, but which doesn't
> seem to make any difference.
>
> Come on, *somebody* must have done this!
>
>
>>"John Brock" wrote in message $i64$1@reader1.panix.com...
>>>I am trying to create a workbook where one of the worksheets contains
>>> an AutoFiltered table. It looks like it should be simple -- what
>>> I am doing is this:
>>>
>>> rng = ws.Range("MyTableRange")
>>> rng.AutoFilter()
>>>
>>> Unfortunately I get an exception on the second statement, with the
>>> unhelpful message:
>>>
>>> AutoFilter method of Range class failed
>>>
>>> The range is rectangular, and contains a legitimate table, with
>>> column headers in the first row and data in most of the cells. If
>>> I create the workbook without calling the AutoFilter statement,
>>> then manually go to the range in question and set AutoFilter from
>>> the Excel Data menu, it works as expected. The worksheet is intended
>>> to be protected, but protection has not yet been set at the point
>>> where I try to set AutoFilter.
>>>
>>> I Googled for this, and found two other people complaining of the
>>> same thing, with no answers. I suspect (and hope!) this means that
>>> there is an answer -- otherwise there would be many more queries.
>>>
>>> Any help would be gratefully appreciated!
>>> --
>>> John Brock
>>> jbrock@panix.com
>>>
>>
>>
>
>
> --
> John Brock
> jbrock@panix.com
>

Jim Thomlinson · Joined: 04 Oct 2007 Posts: 2

Sorry. I missed the title of the post...
--
HTH...

Jim Thomlinson

"Mike S" wrote:

>
> Jim Thomlinson wrote:
> > Your syntax look a little off...
> >
> > set rng = ws.Range("MyTableRange")
> > rng.AutoFilter
> >
>
> No. In VB.NET, the "Set" and "Let" keywords are not supported.
> Therefore, when you are setting a reference to an object, you can't
> type "Set x = y" anymore; instead you type "x = y", same as a normal
> assignment. VB.NET code that uses "Set" or "Let" will not compile;
> however, if you are using the Visual Studio .NET IDE to write your
> code, the IDE will simply delete any "Set"s or "Let"s if you try to
> type them, in order to save you from writing code that won't compile.
>
> --
> Mike S
>
>

John Brock · Joined: 04 Oct 2007 Posts: 13

In article ,
John Brock wrote:
>I am trying to create a workbook where one of the worksheets contains
>an AutoFiltered table. It looks like it should be simple -- what
>I am doing is this:
>
> rng = ws.Range("MyTableRange")
> rng.AutoFilter()
>
>Unfortunately I get an exception on the second statement, with the
>unhelpful message:
>
> AutoFilter method of Range class failed
>
>The range is rectangular, and contains a legitimate table, with
>column headers in the first row and data in most of the cells. If
>I create the workbook without calling the AutoFilter statement,
>then manually go to the range in question and set AutoFilter from
>the Excel Data menu, it works as expected. The worksheet is intended
>to be protected, but protection has not yet been set at the point
>where I try to set AutoFilter.
>
>I Googled for this, and found two other people complaining of the
>same thing, with no answers. I suspect (and hope!) this means that
>there is an answer -- otherwise there would be many more queries.
>
>Any help would be gratefully appreciated!

Well I figured out how to do this, and I guess I should put it into
the record, in particular because the answer is a bit non-obvious
(and even wierd).

I tried using Excel's Record New Macro... feature to see what
AutoFiltering a table looks like in VBA. The result was:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/8/2006 by John Brock
'

'
Application.Goto Reference:="MyTableRange"
Selection.AutoFilter
End Sub

I had not seen Selection mentioned in any of the examples that were
sent to me, so I went looking for it in Visual Studio, but the only
place I found a Selection object was under the Application object.
It seemed strange to AutoFilter a range on a single worksheet by
doing something to the entire Application (which might have several
workbooks open), but it worked! Can anyone explain the logic to me?

In any case, the code looks like this:

Dim ws as Microsoft.Office.Interop.Excel.Worksheet
...

ws.Range("MyTableRange").Activate()
ws.Application.Selection.AutoFilter()

Also, does anybody have any idea what the AutoFilter function of
a Range object does (given that it doesn't turn AutoFiltering on)?
--
John Brock
jbrock@panix.com

John Brock · Joined: 04 Oct 2007 Posts: 13

In article ,
John Brock wrote:
[...]

>It seemed strange to AutoFilter a range on a single worksheet by
>doing something to the entire Application (which might have several
>workbooks open), but it worked! Can anyone explain the logic to me?
>
>In any case, the code looks like this:
>
> Dim ws as Microsoft.Office.Interop.Excel.Worksheet
> ...
>
> ws.Range("MyTableRange").Activate()
> ws.Application.Selection.AutoFilter()
>
>Also, does anybody have any idea what the AutoFilter function of
>a Range object does (given that it doesn't turn AutoFiltering on)?

Of course...

ws.Range("MyTableRange").Select()
ws.Application.Selection.AutoFilter()

also works, and using Select/Selection looks a bit more harmonious.

What exactly is the difference between Select and Activate anyway?
--
John Brock
jbrock@panix.com

Why can't create Excel.Application object using CreateObject Hi, there, I try to create an object using CreateObject function, I got "nothing" from the return variable without any error messages, but It works well on another computer, we have the same environment same references

replace Excel 2000 -> Excel 2003 I am using the following code to edit data cell by cell: What:="", I get a Microsoft error "Microsoft Excel cannot find any data to replace. Check if your search formatting and criteria are defined correctly. I

Excel chart from VB to Excel Hi I've a Chart object in a VB6 form together with some text boxes, my question is can I "copy" the chart object from the form and place it in a Excel chart ?? I know I can create the chart in a Excel my question is can I

Create a control from scratch Hi, I want to create a control from scratch (not but a wrapper around an existing control). Does anyone know of a some reference material that would identify what I need to do. I'm thinking about something that would identify all the properties, events,

How to asign the DataSource when create the OleDbConnection? I am just a new learner in VB.Net, this question should be very stupid for you, but I really need help. I have successfully make a simple programm by using to connect the Access mdb file in the same folder. I want to add a function like th