AutoFilter method of Range class failed

Hello,

I'm trying to automate some formatting of an Excel 2007 workbook using 
VB.NET in Visual Studio for Applications to run as a Script Task in SqlServer 
Integration Services.  I have a fairly simple range, and I'm able to do 
everything I want with it, except for Autofilter.  I've tried various methods 
for specifying the range (explicit, A1, UsedRange, Columns, etc.)  No matter 
what I do, I keep getting an exception with the message of "AutoFilter method 
of Range Class failed".

I've declared variables for the application, workbook, worksheet, and range 
and currently have the following code:

xlWorkBook=xlApp.Workbooks.Open("my.xlsx")
xlWorkSheet=CType(xlWorkbook.Sheets("Sheet1"), _
                              Microsoft.Office.Interop.Excel.Worksheet)
xlWorkSheet.Activate()
xlWorkSheet.Unprotect()
xlWorkSheet.Range("A1").Select()
xlWorkSheet.Range("A1").Activate() ' didn't change anything after adding
xlWorkSheet.EnableAutoFilter = True ' didn't change anything after adding
xlWorkSheet.Range("A1").AutoFilter()
xlWorkBook.Save()
xlWorkBook.Close()

I've been banging my head for a couple of days trying to find a solution to 
this.  The onlything that I've found would seem t translate to:

xlWorkSheet.Application.Selection.Autofilter(), however that doesn't seem to 
be supported by the Office 12 PIAs.

If anyone has any ideas, I'm open to trying anything.

Thank you,

kevjlang
0
Utf
4/9/2010 10:17:01 PM
dotnet.languages.vb 830 articles. 0 followers. Follow

1 Replies
1783 Views

Similar Articles

[PageSpeed] 32

Well, I altered the code to use Option Strict Off and changed my call to 
AutoFilter to use an Object as the Range Object's base class, and it works 
now.  However, I'd like to know why early binding isn't working.  Does anyone 
have any ideas as to what might be wrong?  Do I have some version mismatches 
in my PIAs or between the PIAs and the Excel or Window DLLs?

Any advice will be greatly appreciated.

Thank you,

kevjlang

"kevjlang" wrote:

> Hello,
> 
> I'm trying to automate some formatting of an Excel 2007 workbook using 
> VB.NET in Visual Studio for Applications to run as a Script Task in SqlServer 
> Integration Services.  I have a fairly simple range, and I'm able to do 
> everything I want with it, except for Autofilter.  I've tried various methods 
> for specifying the range (explicit, A1, UsedRange, Columns, etc.)  No matter 
> what I do, I keep getting an exception with the message of "AutoFilter method 
> of Range Class failed".
> 
> I've declared variables for the application, workbook, worksheet, and range 
> and currently have the following code:
> 
> xlWorkBook=xlApp.Workbooks.Open("my.xlsx")
> xlWorkSheet=CType(xlWorkbook.Sheets("Sheet1"), _
>                               Microsoft.Office.Interop.Excel.Worksheet)
> xlWorkSheet.Activate()
> xlWorkSheet.Unprotect()
> xlWorkSheet.Range("A1").Select()
> xlWorkSheet.Range("A1").Activate() ' didn't change anything after adding
> xlWorkSheet.EnableAutoFilter = True ' didn't change anything after adding
> xlWorkSheet.Range("A1").AutoFilter()
> xlWorkBook.Save()
> xlWorkBook.Close()
> 
> I've been banging my head for a couple of days trying to find a solution to 
> this.  The onlything that I've found would seem t translate to:
> 
> xlWorkSheet.Application.Selection.Autofilter(), however that doesn't seem to 
> be supported by the Office 12 PIAs.
> 
> If anyone has any ideas, I'm open to trying anything.
> 
> Thank you,
> 
> kevjlang
0
Utf
4/15/2010 2:38:01 AM
Reply:

Similar Artilces:

Autofilter
Is it possible to autofilter a single column within a project? I am using Project 2007. Thanks Larry Larry, I am not sure what you mean by Autofilter a single column. Can you give more detail as to what you are trying to do? "External Update" wrote: > Is it possible to autofilter a single column within a project? I am using > Project 2007. > Thanks > > Larry Hello Larry, Sure. Turn on Autofilters and use the drop-down filter only on the single field. The Autofilter icons will appear at each field heading, but you need not use them...

Test restore to RSG fails
An internal processing error has occurred. Try restarting the Exchange System Manager or the Microsoft Information Store service or both. ID no: c1041724 Exchange System Manager Restarting the services or ESM do not work or a server reboot. I then check the Event viewer and find the following 3 errors. Information Store (3984) Recovery Storage Group: Attempted to attach database 'C:\Program Files\Exchsrvr\Recovery Storage Group\Mailbox Store (TEMPSYDEX).edb' but it is a database restored from a backup set on which hard recovery was not started or did not complete successfully....

Problem with serializing multiple collections with a base class #2
I have the following code. When I serialize the DriverCollection I would like to get both the Driver Collection and the DeletedItems collection. I end up with only the Driver collection. I have tried numerous attributes with no success. Any help would be appreciated... using System; using System.IO; using System.Text; using System.Xml; using System.Xml.Serialization; using System.Runtime.Serialization; using System.Runtime.Serialization.Formatters.Binary; using System.Collections; namespace SerialTest { #region BusinessObjectBase /// Summary description for BusinessObjectBase...

Populating a Cell based on a Range of Values (Part II)
Sorry, but my I'm still not sure how to make my formula evaluate a full range of values. For example, the cell I need to populate will have values based on what is calculated in another cell as follows: If value in cell A1 is 1.0 and > 0 in new cell .900 - .999 1 in new cell .800 - .899 2 in new cell all the way down to <.100 which should put a 10 in the new cell. Thanks again for all the help! It is definitely a learning experience for me. Ken -- akkrug Part of the formula is working: =IF(AND(A1>=0.8,A1<=0.899),...

A general rule for using methods that create objects
Objects created by invoking methods do not seem to behave like normal objects in VBA. Consider that in other programming languages, once you set an equality, you can use either side of the expression interchangeably. For instance: A = B C = sqrt(A) will give you the same thing as A = B C = sqrt(B) But consider two examples where this interchangeability is not true in VBA. You can say: Set Ws = DBEngine.CreateWorkspace( Arguments ) (ie, A = B) and then you can say: Workspaces.Append Ws (similar to C = sqrt(A)) but you cannot do: Workspaces.Append DBEngine.Cr...

Sum range in Excel
If you have numbers in a range say B4:B7 and the last row is blank, should the function read =SUM(B4:B8) to include the blank row or ius it ok to leave it at =SUM(B4:B7). A colleague who works in this area insists it is good practice to use the first option. Any thoughts appreciated. Aidan. I would use B4:B8, in case anyone inserts a row at row 8 and adds a value, it will automatically get added. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Aidan" <Aidan@discussions.microsoft.com> wrote in message news:FBD41B1E-ED23-4DE...

subscript out of range....
Sub AAA() Dim StartCol As Long Dim EndCol As Long Dim ColNdx As Long Set sht = Sheets(x) StartCol = 1 ' column A EndCol = 28 ' column AB For ColNdx = EndCol To StartCol Step -1 If Application.CountA(sht.Columns(ColNdx)) = 1 Then sht.Columns(ColNdx).Delete End If Next ColNdx End Sub Once worked fine, but now error on this line.... Set sht = Sheets(x) Try quote marks around x ... "x", without quotes VBA thinks it is a variable. If it is a variable, it is not declared. "J.W. Aldr...

Paste damages formula in autofilter #3
That doesn't seem to work as the paste sends a #REF into the offset o paste so the formula becomes =IF(OFFSET(#REF!,0,-1)>0,1,0) on the righ which returns the #REF error. Think solution would be easier to solve with a macro executin everytime an update occurs maybe?. There's got be a way to do it cause I have seen a worksheet where i works and I don't believe it is likely it was done with macros. (sidenote, database is a central one used to modify other worksheet using macros. Each of these worksheets uses different sets of filters The data pane on the right records subtotal...

.NET WebService method doesn't understand xsd list
I've posted this before but no one seems to respond. I need to know if this is a bug I am implementing a webservice against a preexisting schema/wsdl The schema specifies an element called Type that is a xml schema list of qname (i.e. it wants the message to have a space deimited list of qnames.) However wsdl.exe interprets this incorrectly as 'XmlQualifiedName Types' rather than 'XmlQualifiedName [] Types'. Simply changing the type in the C# class to an array results in incorrect serialization/exceptions. This is a fairly common piece of schema so I'm surprised ...

Macro question
Hi, I have a macro set up to automate some tasks I am responsible for. Part of the macro is to go to sheet2 and select the entire range of data. When recording the macro I went to the start of the data (cell A2) and pressed Ctrl + Shift + down arrow. The resultant range came out to be A2:F307. I ran the macro on another set of data and it selected the same exact range of cells (A2:F307) when it needs to be A2:F272. Is there a way to change the macro so it knows to dynamically select the range of data each time it's run? You should always post YOUR code for comments. Assuming C...

Please Help!
I have posted this problem before but no one has responded. Two of the files I am trying to load have vital info. I have uninstalled MS Office and then reinstalled with no results. Everytime I try and load an xls file I get the message: "Class not registered. Looking for object with CLSID:{AC9F90-E877-11CE-9F68- 00AA00574A4F}" Excel will load but I can't get the file to open. Any help would be very much appreciated. Try this http://support.microsoft.com/default.aspx?scid=kb;en-us;182500&Product=xlw it seems you missed 2 characters in the string you posted, the KB...

shortcut on autofilter function
i have many columns of data using the autofilter. is there a shortcut that either returns all the fields to the all function or a shortcut that returns all the data to the page thanks One line of code will do this (maybe put in your Personal.xls): Sub Foo() Activesheet.ShowAllData End Sub Then assign it to a New (Macro) Icon in your toolbar. It will always be available on any sheet that has an autofilter range assigned. HTH "derwood" <darren.irvine@gmail.com> wrote in message news:1132654133.294598.246590@g43g2000cwa.googlegroups.com... >i have many columns of data u...

Validation Criteria of "Custom" fails to work
Additional information: - I'm using Office XP - I'm using Windows XP =========== In Validation / Settings Tab / Validation Criteria / "Allow" Box / Custom: I type in a formula as something like as follows: =day(A2)-today() OR =J3-J1 Even if the cell (where validation applys) is invalid, there is no red circle around. I clicked "formula auditing toolbar | Circle Invalid Data", but it still won't work. Do you know why? How to use "custom" validation criteria? DAY() will return a number between 1 and 31, depending on the day of the month. TODAY() ...

Range names in Macros
If i have the following code to sort two columns linked as a macro... Sheets("low").Select Range("B4:B15").Select Selection.sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("C4:C15").Select Selection.sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom If i want to later change this range, it is obviously better to have a range name. how would you code this into VB? would you also h...

Failed update procedure
Can anyone tell me why the Office 2004 11.2.0 Update is not working on my Mac. It tells me that it can't find any programs to update, but the whole Office suite 2004 is installed. I run 10.4.3. Thanks in advance. Cees Hi Cees- Is it possible that you are already updated to 11.2? Launch one of the Office apps, go to its name in the menu bar & select About... To see what version number displays. Another possibility is if Office is installed for one user on a multi-user system & you are running the updater while logged in as a different user. HTH |:>) On 1/4/06 12:36 PM, i...

AutoFilter and Frequency Function
I am trying to see if I can create a frequency function that will change as I autofilter different criteria. I have a file with over 15,000 rows returning data on sales opportunities including key fields I want to filter on: organization, product line, geography and status (Won, Lost, Open). In each case I want to see the change in distribution of "days in funnel" whether I am looking at all the data or just one geography or just one product line. It appears that the Frequency function does not recognize the filtered out data and continues to return values for the entire data set....

autofilter
hello and good new year i'm on w2k and office 2003 on excel when i do two cols A B 1 =a+1 2 =a+1 etc..... i go to for example 500 and after i make auto filter on A and B if i use autofilter i see Filter mode but if after i do only 10 line and not 500 i have 1 of 500 records found and not 1 of 10. i have two problem : with 500 line i have not the count of record found and if i delete some line i must desable autofilter for have the good count of line thanks for your help Debra Dalgleish as some work arounds at: http://www.contextures.com/xlautofilter02.ht...

Deployment Manager Snap-in Failed
In MSCRM 1.2 we tried to create a new Business Unit with a new name. This caused users in our original BU to have problems, so we tried to delete the new BU after moving all users back to the initla BU. In the process, MSCRM failed and we get a snap-in failure message when we try to open the Deployment Manager. We have verified within Active Directory that all users have the propoer permission. Can we simply run a reinstall to fix the snap-in failure issue? Make sure when logging into the server to run deployment manager that you log in as an administrator in the domain, not just log...

Autosum with Autofilter
I have a spreadsheet with numerous values in one column and different identifying data in other columns. I can sort the data nicely using autofilter, however, my autosum at the bottom of the values column continues to display the total value of all the rows, not just the ones that I have filtered to view. I need to be able to filter the spreadsheet and to have it autosum only those rows in view on screen. Is this possible? Many thanks Peter Use the SUBTOTAL() function which will work only on filtered rows. -- Regards Ken....................... Microsoft MVP - Excel ...

Modify Shipping Method
I have a shipping method called "UPS RED". I would like to change it to "UPS 1". What is the correct way to do this so that all customer addresses that have UPS RED will be changed to UPS 1? Hmm, since the customer class rolldown will not change the Customer Address MSTR, I think you will need to use a SQL Update statement. It would go something like this: To update the customer address master: UPDATE RM00102 set SHIPMTHD = 'UPS 1' where SHIPMTHD = 'UPS RED' To update the customer master: UPDATE RM00101 set SHIPMTHD = 'UPS 1' where SHIPMT...

Range Lookup
I want to do something like a vlookup but instead of looking for a specific number, I want to look for a range and then return a value. An example would be I want to return a letter grade based on test scores. I have the low end of the ranges in column A, high end in column B and in this case, the Grade would be in Column C. How do I do this without having to write a long IF statement? Try... =VLOOKUP(D1,$A$1:$C$10,3,TRUE) ....where D1 contains your lookup value. Hope this helps! In article <F969091F-B558-45EA-9761-C254AB1E6FF7@microsoft.com>, "Jake" <Jake@disc...

Autofilter #11
When Autofilter is "on" the column filtered has the down arrow turned blue. I have difficulty seeing blur. Is there a way to change the color to let's say yellow? i dont think there is a way to change the colour of the drop down arrow there is a way to make the arrow visible / invisible. and there is als a function that would colour that cell (column header) for whic autofilter is on. this is from excel help, to make the dropdown arrow invisible. you ca use this to hide all the column headers that currently do not hav autofilter on. Worksheets("Sheet1").Range(&q...

Excel 2002
....I have a large amount of amount to produce multiple charts from, so using Autofilter is a bit of a necessity. Using the Chart Wizard, I can easily select the filtered data range but it doesn't seem possible to select the (filtered) headings as the X-axis (time period). Any ideas or workrounds? Thanks in advance, Amanda when you set up your autofilter range leave the row you want to use for your axis out of the range. "Birmangirl" wrote: > ...I have a large amount of amount to produce multiple charts from, so using > Autofilter is a bit of a necessity. Using the...

Reference cells in named range
I have a large sheet with data. One of the columns is defined as a named range (Key_x). On another sheet, I would like to reference to some cells in that column. Is it possible to use something like =Key_x!5 to get the value of the 5th cell (row) in this named range? Thanks for any hints. Hi Luca, You could use OFFSET, as follows. RANHGE("KEY_X").OFFSET(nRows,0) if in the same column, HTH, Sharon "Luca Brasi" wrote: > I have a large sheet with data. One of the columns is defined as a named > range (Key_x). > On another sheet, I would like to reference t...

The best method for forumlas and charts???
Hello all, I'm looking for suggestions on the best way to go about getting the information I want to display in a chart. Depending on the response, I may also need help implementing the suggestion. Here's what I want to do: On Sheet3 I have a range of data, for example: Columns A B C D 20-Sep R Y G 21-Sep Y n/a R 22-Sep n/a G Y 23-Sep R R n/a On Sheet7, I want to have 16 line graph charts that...