Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |