AutoFilter Automation from VB.NET
 

I have searched the groups and haven't really found the answer to my
question...

Why can I not get the AutoFilter functionality to work through VB.NET/COM
Interop automation?
Example:

'xlRange = Excel.Range object for some specific range, "2:2" for the
second row
xlRange.AutoFilter(Field:=1)

This code doesn't work. It actually causes the AutoFilter drop down boxes
to appear on the cells in the range, but the drop down buttons do not work.
I do not have multiple worksheets selected nor is the worksheet protected,
the really weird thing is this:
If I open the template file, place some dummy data in the template, turn on
Autofilter, save the template, the turn AutoFilter off and delete the data,
save the template again the above code works just fine. Why can I not use
this code on a new template, one which hasn't gone through the preceding
hoops.

I am using VB.NET, 1.1 Framework and Office 2003.

Thanks in advance, anything would be appreciated

James Glover

AutoFilter Automation from VB.NET
 

Are you trying to apply an autofilter to a group of empty cells? I suspect
that these are outside the UsedRange. If I apply a bold format to the
blank cells, then it works - this would be consistent with your observation.
this is what I believe this is happening in your case. Excel has a property
called the usedrange. This specifies the rectangular area of cells that
Excel considers to be in use. Excel only stores information on the cells
that it considers to be in use. So it sounds like you are trying to apply
an autofilter to an area outside this range. When you manually put in some
data and apply the autofilter, then this area gets included in the used
range. Note that the usedrange does not necessarily start in cell A1. If
you want to predefine a usedrange, you could say format A1 and Z26 as bold
as an example. Note that when you close and save the file, excel will
recalculate the usedrange, so if you did the above, then deleted rows 2:30
for example, the usedrange would be reset on closing.
--
Regards,
Tom Ogilvy

"James Glover" wrote in message
...
I have searched the groups and haven't really found the answer to my
question...

Why can I not get the AutoFilter functionality to work through VB.NET/COM
Interop automation?
Example:

'xlRange = Excel.Range object for some specific range, "2:2" for the
second row
xlRange.AutoFilter(Field:=1)

This code doesn't work. It actually causes the AutoFilter drop down boxes
to appear on the cells in the range, but the drop down buttons do not
work.
I do not have multiple worksheets selected nor is the worksheet protected,
the really weird thing is this:
If I open the template file, place some dummy data in the template, turn
on
Autofilter, save the template, the turn AutoFilter off and delete the
data,
save the template again the above code works just fine. Why can I not use
this code on a new template, one which hasn't gone through the preceding
hoops.

I am using VB.NET, 1.1 Framework and Office 2003.

Thanks in advance, anything would be appreciated

James Glover

AutoFilter Automation from VB.NET
 

Well, the cells should not be empty.
I will see if I can determine if they are outside the UsedRange.

Thanks

James

"Tom Ogilvy" wrote in message
...
Are you trying to apply an autofilter to a group of empty cells? I suspect
that these are outside the UsedRange. If I apply a bold format to the
blank cells, then it works - this would be consistent with your
observation.
this is what I believe this is happening in your case. Excel has a
property
called the usedrange. This specifies the rectangular area of cells that
Excel considers to be in use. Excel only stores information on the cells
that it considers to be in use. So it sounds like you are trying to apply
an autofilter to an area outside this range. When you manually put in
some
data and apply the autofilter, then this area gets included in the used
range. Note that the usedrange does not necessarily start in cell A1. If
you want to predefine a usedrange, you could say format A1 and Z26 as bold
as an example. Note that when you close and save the file, excel will
recalculate the usedrange, so if you did the above, then deleted rows 2:30
for example, the usedrange would be reset on closing.
--
Regards,
Tom Ogilvy

"James Glover" wrote in message
...
I have searched the groups and haven't really found the answer to my
question...

Why can I not get the AutoFilter functionality to work through
VB.NET/COM
Interop automation?
Example:

'xlRange = Excel.Range object for some specific range, "2:2" for the
second row
xlRange.AutoFilter(Field:=1)

This code doesn't work. It actually causes the AutoFilter drop down
boxes
to appear on the cells in the range, but the drop down buttons do not
work.
I do not have multiple worksheets selected nor is the worksheet
protected,
the really weird thing is this:
If I open the template file, place some dummy data in the template, turn
on
Autofilter, save the template, the turn AutoFilter off and delete the
data,
save the template again the above code works just fine. Why can I not
use
this code on a new template, one which hasn't gone through the preceding
hoops.

I am using VB.NET, 1.1 Framework and Office 2003.

Thanks in advance, anything would be appreciated

James Glover