Can't create AutoFiltered Excel worksheet using VB.NET

[John Brock]

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
jb****@panix.com

[Ron de Bruin]

Hi

See this page
http://www.contextures.com/xlautofilter03.html
--
Regards Ron De Bruin
http://www.rondebruin.nl

"John Brock" <jb****@panix.com> wrote in message news:e6**********@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
jb****@panix.com

[Jim Thomlinson]

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
jb****@panix.com

[John Brock]

In article <Oq**************@TK2MSFTNGP04.phx.gbl>,
Ron de Bruin <ro********@kabelfoon.nl> 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" <jb****@panix.com> wrote in message news:e6**********@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
jb****@panix.com

--
John Brock
jb****@panix.com

[Ron de Bruin]

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" <jb****@panix.com> wrote in message news:e6**********@reader1.panix.com...

In article <Oq**************@TK2MSFTNGP04.phx.gbl>,
Ron de Bruin <ro********@kabelfoon.nl> 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" <jb****@panix.com> wrote in message news:e6**********@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
jb****@panix.com

--
John Brock
jb****@panix.com

[Mike S]

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

[Mike S]

John Brock wrote:

In article <Oq**************@TK2MSFTNGP04.phx.gbl>,
Ron de Bruin <ro********@kabelfoon.nl> 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.

Lifted from the MSDN page on AutoFilterMode at
http://msdn2.microsoft.com/en-us/mic...ltermode.aspx:

Remarks

You can set this property to false to remove the arrows, but you
cannot set it to true.

That would explain why the line 'ws.AutoFilterMode = true' throws an
exception.
I'm not very familiar with Excel programming, but in every example I
could find, everyone always sets AutoFilterMode to false (which is
allowed) before calling AutoFilter on their range object. So I would
say try this:

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

Setting AutoFilterMode to false also has the side-effect of removing
any previous AutoFilters that were on the worksheet, which I guess is
important before you call AutoFilter() again...

<snip>

Mike S

[Jim Thomlinson]

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]

In article <e6**********@reader1.panix.com>,
John Brock <jb****@panix.com> 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
jb****@panix.com

[John Brock]

In article <e6**********@reader2.panix.com>,
John Brock <jb****@panix.com> 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
jb****@panix.com