Need to program Autofilter on with Excel Interop 1

[AndyH1]

I'm generating Excel using the Excel 11 Object Library as in:

http://www.beansoftware.com/ASP.NET-Tutorials/Generating-Excel-Reports.aspx

It works fine and I can generate an Excel report with the titles formatted as black on grey.

The customer wants AutoFilter set as on by default though, and I can't seem to find how to do this.

Iv'e tried 'xlWorkSheet.AutoFilter = True' but I get an error saying this is read only, so this is obviously not the way to assign the value just determine it.

xlWorkSheet.Range("$A1").AutoFilter() seems the most likely way to set Iv'e found but this gives System.Runtime.InteropServices.COMException: AutoFilter method of Range class failed and I can't determine why. Iv'e tried various types of ranges such as ("$A1:$IV1") but no joy.

Can anyone advise on this?
Also if anyone knows a good reference for this sort of programming (VB.Net generation of Excel) would be grateful

Thanks
Andy

 

[jebenson]

Try this:

xlWorkSheet.Range("$A1").Select()
xlWorkSheet.Application.Selection.AutoFilter()

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Sorwen]

The easist thing to do when working with an Excel is to just record a macro. When working with an Excel 11 Object the commands are almost exactly the same.

This:

'
'
' Macro1 Macro
' Macro recorded 8/19/2008 by User
'

'
    Range("A1:D8").Select
    Selection.AutoFilter

Becomes:

xlWorkSheet.Range("A1:D8").Select()
xlWorkSheet.Application.Selection.AutoFilter()

Like jebenson said.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Sorwen]

Ooopsie.

xlWorkSheet.Application.Selection.AutoFilter()

Should only need to be.

xlWorkSheet.Selection.AutoFilter()

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Sorwen]

No it is application. I should have double checked before I said.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Sorwen]

Because you do Sheet1.Application.Selection.AutoFilter in Excel.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[AndyH1]

Thanks jebenson for solving it, and Sorwen for the suggestion of using Macros to figure out what to do. Was tearing what little hair I have out trying to solve this.

many thanks
Andy