AutoFilter method of Range class failed

  • Follow


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
Reply Utf 4/9/2010 10:17:01 PM

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
Reply Utf 4/15/2010 2:38:01 AM

1 Replies
1065 Views

(page loaded in 0.039 seconds)

6/6/2014 2:54:03 PM


Reply: