Can't create AutoFiltered Excel worksheet using VB.NET

J

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!
 
G

Guest

Your syntax look a little off...

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

Give that a try.
 
J

John Brock

Hi

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

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!

 
R

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 said:
Hi

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

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!
Click to expand...
 
M

Mike S

Jim said:
Your syntax look a little off...

set rng = ws.Range("MyTableRange")
rng.AutoFilter
Click to expand...

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.
 
M

Mike S

John said:
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.
Click to expand...

Lifted from the MSDN page on AutoFilterMode at
http://msdn2.microsoft.com/en-us/microsoft.office.tools.excel.worksheet.autofiltermode.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
 
J

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!
Click to expand...

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)?
 
J

John Brock

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)?
Click to expand...

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AutoFilter method of Range class failed 1
Assigning AutoFiltered Range to Range Object 5
Advanced Filter Error - I cannot spot my Error 3
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
Count the number of AutoFiltered records 6
VBA: How can I use a string variable as autofilter criteria? 4
Iterate through the autofiltered rows 2
Autofilter on Multiple Worksheets 5

Top