473,732 Members | 2,261 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Can't create AutoFiltered Excel worksheet using VB.NET

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("MyTab leRange")
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.co m

Jun 5 '06 #1
9 13831
Hi

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

"John Brock" <jb****@panix.c om> wrote in message news:e6******** **@reader1.pani x.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("MyTab leRange")
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.co m

Jun 5 '06 #2
Your syntax look a little off...

set rng = ws.Range("MyTab leRange")
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("MyTab leRange")
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.co m

Jun 5 '06 #3
In article <Oq************ **@TK2MSFTNGP04 .phx.gbl>,
Ron de Bruin <ro********@kab elfoon.nl> wrote:
Hi

See this page
http://www.contextures.com/xlautofilter03.html
The VBA (not VB.NET) example on that page is:

Sub TurnAutoFilterO n()
'check for filter, turn on if none exists
If Not ActiveSheet.Aut oFilterMode Then
ActiveSheet.Ran ge("A1").AutoFi lter
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.AutoFilterMo de = true
rng = ws.Range("MyTab leRange")
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.EnableAutoFi lter 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.c om> wrote in message news:e6******** **@reader1.pani x.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("MyTab leRange")
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.co m


--
John Brock
jb****@panix.co m

Jun 5 '06 #4
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.c om> wrote in message news:e6******** **@reader1.pani x.com...
In article <Oq************ **@TK2MSFTNGP04 .phx.gbl>,
Ron de Bruin <ro********@kab elfoon.nl> wrote:
Hi

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


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

Sub TurnAutoFilterO n()
'check for filter, turn on if none exists
If Not ActiveSheet.Aut oFilterMode Then
ActiveSheet.Ran ge("A1").AutoFi lter
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.AutoFilterMo de = true
rng = ws.Range("MyTab leRange")
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.EnableAutoFi lter 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.c om> wrote in message news:e6******** **@reader1.pani x.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("MyTab leRange")
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.co m


--
John Brock
jb****@panix.co m

Jun 5 '06 #5

Jim Thomlinson wrote:
Your syntax look a little off...

set rng = ws.Range("MyTab leRange")
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

Jun 5 '06 #6

John Brock wrote:
In article <Oq************ **@TK2MSFTNGP04 .phx.gbl>,
Ron de Bruin <ro********@kab elfoon.nl> wrote:
Hi

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


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

Sub TurnAutoFilterO n()
'check for filter, turn on if none exists
If Not ActiveSheet.Aut oFilterMode Then
ActiveSheet.Ran ge("A1").AutoFi lter
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.AutoFilterMo de = true
rng = ws.Range("MyTab leRange")
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.AutoFilterM ode = 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.AutoFilterMo de = false
rng = ws.Range("MyTab leRange")
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

Jun 5 '06 #7
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("MyTab leRange")
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

Jun 5 '06 #8
In article <e6**********@r eader1.panix.co m>,
John Brock <jb****@panix.c om> 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("MyTab leRange")
rng.AutoFilter( )

Unfortunatel y 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.Got o Reference:="MyT ableRange"
Selection.AutoF ilter
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.Offic e.Interop.Excel .Worksheet
...

ws.Range("MyTab leRange").Activ ate()
ws.Application. Selection.AutoF ilter()

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.co m

Jun 8 '06 #9
In article <e6**********@r eader2.panix.co m>,
John Brock <jb****@panix.c om> 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.Offic e.Interop.Excel .Worksheet
...

ws.Range("MyTab leRange").Activ ate()
ws.Application. Selection.AutoF ilter()

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("MyTab leRange").Selec t()
ws.Application. Selection.AutoF ilter()

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.co m

Jun 8 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
13987
by: Brian Johnson | last post by:
I am trying to create multiple (named) worksheets in excel using .Net PIA's. I have successfully created an excel document and set cell values, but have been unable to manipulate which worksheet I am using and naming the worksheet. Here is current sample (VB.Net): dim xl as new excel.application() xl.workbooks.Add() xl.cells(1,1).Value = "test"
3
9245
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works only exporting to single worksheet. but i need to export data to multiple worksheets. it is very urgent to us. so please help me in code.
3
20283
by: Otie | last post by:
I found the following under the GetObject help notes and in the example for GetObject: "This example uses the GetObject function to get a reference to a specific Microsoft Excel worksheet (MyXL). It uses the worksheet's Application property to make Microsoft Excel visible, to close it, and so on. Using two API calls, the DetectExcel Sub procedure looks for Microsoft Excel, and if it is running, enters it in the Running Object Table. The...
11
4054
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it again, Excel hangs. OR if I open Excel again (say from a desktop icon) before I close Access, Excel hangs. (this has happened for both 97 & 2000 for me) I of course thought that I mustn't be unloading a variable properly.
8
13957
by: Horst Walter | last post by:
I create an Excel worksheet in C# (should be similar in VB) Connection String: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";" + "Extended Properties=Excel 8.0;"; Create string: "CREATE TABLE S1 (Dt date, St char(40), Cr currency)" Works fine, but the worksheet is named _S1 (UNDERSCORE!) and not S1.
1
10745
by: javzxp | last post by:
Hi I'd like to use C# to open an existing Excel workbook and save each worksheet it contains into a new Excel file. The name of each new Excel file should be the name of the worksheet copied from the existing file. So, as an example:
1
2573
by: Randall Arnold | last post by:
I'm converting a vbscript program to vb.net. Witht he exception of .net idiosyncrasies, most of it is working well with the same code. My only problem is that some properties and methods are missing from the Chart object. For instance, the following line worked fine in vbscript (csPowerSpeed is the chart): objXL.csPowerSpeed.seriescollection.newseries() However, in VS.Net, newseries is no longer avaialable as a method for series...
0
1116
by: devolper | last post by:
I am devolping a web application which which .net reports to a excel work book .each report in one excel sheet.i am using the following code.......... Dim objDestinationExcel As New Excel.Application Dim objDestinationWBS As Excel.Workbooks objDestinationWBS = objDestinationExcel.Workbooks objDestinationWBS.Open(TemplateFileName) Dim objDestinationWB As Excel.Workbook objDestinationWB =...
1
3378
by: TG | last post by:
Hi! I have an application in which I have some checkboxes and depending which ones are checked those columns will show in the datagridview from sql server or no. After that I have 2 buttons: 1) export to excel button exports the visible columns from the datagridview to excel (this works fine)
0
8944
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8772
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9303
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8184
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6732
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6030
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4546
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4803
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2176
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.