none
VSTO (VS2010) - Error when Apply AutoFilter to Excel Worksheet RRS feed

  • Question

  • Hi there!

    It should work (works with VBA), but I got error when I try to apply AutoFilter to Excel Worksheet.

    Here is my code

    Dim table = CType(wks.Cells(1, 1), Excel.Range).CurrentRegion
    
            table.Columns.AutoFit()
            table.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlTop
    
            Dim header = CType(table.Rows(1), Excel.Range)
            header.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter
            With header.Interior
                .ThemeColor = Microsoft.Office.Interop.Excel.XlThemeColor.xlThemeColorAccent3
                .TintAndShade = 0.599993896298105
                .PatternTintAndShade = 0
            End With
    
            CType(table.Rows(1), Excel.Range).AutoFilter()

    The code halt when the line bellow is processed

    CType(table.Rows(1), Excel.Range).AutoFilter()

    I got the following error message:

    The method AutoFilter class Range failed.

    Many thanks for any kind of help.

    Best regards,


    Ciro

    Sunday, June 24, 2012 10:15 PM

Answers

  • Hi Ciro,

    It's based on my experience that you snippet will work fine, however it prompt exception message. And it will not throw any exception if you fill parameters in the AutoFilter method. My work round is set "Field := 1" in the method.

    Best Regards,

    T.X.


    征诛志异,三让两家王朝
    功同开辟,一桮万古江南


    • Edited by Tx_OfficeDev Monday, June 25, 2012 4:38 AM
    • Marked as answer by Oric.CQ Monday, June 25, 2012 10:45 AM
    Monday, June 25, 2012 4:29 AM

All replies

  • Hi Ciro,

    It's based on my experience that you snippet will work fine, however it prompt exception message. And it will not throw any exception if you fill parameters in the AutoFilter method. My work round is set "Field := 1" in the method.

    Best Regards,

    T.X.


    征诛志异,三让两家王朝
    功同开辟,一桮万古江南


    • Edited by Tx_OfficeDev Monday, June 25, 2012 4:38 AM
    • Marked as answer by Oric.CQ Monday, June 25, 2012 10:45 AM
    Monday, June 25, 2012 4:29 AM
  • Hi T.X.!

    Thank you for your help.

    Best regards,


    Ciro

    Monday, June 25, 2012 10:46 AM