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

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