Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb,microsoft.public.vb.ole.automation,microsoft.public.vb.ole
|
|||
|
|||
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("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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb,microsoft.public.vb.ole.automation,microsoft.public.vb.ole
|
|||
|
|||
Can't create AutoFiltered Excel worksheet using VB.NET
Hi
See this page http://www.contextures.com/xlautofilter03.html -- Regards Ron De Bruin http://www.rondebruin.nl "John Brock" wrote in message ... 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 |
#3
Posted to microsoft.public.vb.ole,microsoft.public.vb.ole.automation,microsoft.public.dotnet.languages.vb,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Can't create AutoFiltered Excel worksheet using VB.NET
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 |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb,microsoft.public.vb.ole.automation,microsoft.public.vb.ole
|
|||
|
|||
Can't create AutoFiltered Excel worksheet using VB.NET
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 ... 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 -- John Brock |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb,microsoft.public.vb.ole.automation,microsoft.public.vb.ole
|
|||
|
|||
Can't create AutoFiltered Excel worksheet using VB.NET
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 ... 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 ... 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 -- John Brock |
#6
Posted to microsoft.public.vb.ole,microsoft.public.vb.ole.automation,microsoft.public.dotnet.languages.vb,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Can't create AutoFiltered Excel worksheet using VB.NET
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 |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb,microsoft.public.vb.ole.automation,microsoft.public.vb.ole
|
|||
|
|||
Can't create AutoFiltered Excel worksheet using VB.NET
John Brock wrote: 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. Lifted from the MSDN page on AutoFilterMode at http://msdn2.microsoft.com/en-us/mic...erm ode.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 |
#8
Posted to microsoft.public.vb.ole,microsoft.public.vb.ole.automation,microsoft.public.dotnet.languages.vb,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Can't create AutoFiltered Excel worksheet using VB.NET
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 |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb,microsoft.public.vb.ole.automation,microsoft.public.vb.ole
|
|||
|
|||
Can't create AutoFiltered Excel worksheet using VB.NET
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 |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.dotnet.languages.vb,microsoft.public.vb.ole.automation,microsoft.public.vb.ole
|
|||
|
|||
Can't create AutoFiltered Excel worksheet using VB.NET
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I create GST worksheet in excel | New Users to Excel | |||
How do I create a worksheet within a worksheet in Excel? | Excel Worksheet Functions | |||
create linked autofiltered sheets? | Links and Linking in Excel | |||
Create new excel worksheet | Excel Programming | |||
Create Excel Worksheet in C# / VB | Excel Programming |