K
kevjlang
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
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