Question Excel Autofilter

[gene7135]

I am using the following code to export a datatable into a Excel spreadsheet.

I am having issues trying to set the AutoFilter on for the top row.

With the code as it stands does turn on the Autofilter , however it gives an error "Unable to Set the Autofilter property of the range class."

Any ideas?

    Public Shared Sub PopulateSheet(ByVal dt As DataTable, ByVal File As String)        Dim oXL As Excel.Application = CType(CreateObject("Excel.Application"), Excel.Application)
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range
        oXL.Visible = True


        oWB = oXL.Workbooks.Add
        oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)


        Dim dc As DataColumn
        Dim dr As DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            oXL.Cells(1, colIndex) = dc.ColumnName
            oXL.Cells(1, colIndex).Style = "Accent1"
        Next
        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                oXL.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
            Next
        Next


        oSheet.Cells.Select()
        oSheet.Columns.AutoFit()
        oSheet.Rows.AutoFit()
        oXL.Cells(1, colIndex).AutoFilter = True


        oXL.Visible = True
        oXL.UserControl = True


        oWB.SaveAs(File)
        oRng = Nothing


        'Close All Objects of Excel
        NAR(oSheet)


        oWB.Close(False)
        NAR(oWB)


        oXL.Workbooks.Close()
        NAR(oXL.Workbooks)


        'quit and dispose app
        oXL.Quit()
        NAR(oXL)


        'VERY IMPORTANT
        GC.Collect()
    End Sub

 

[JohnH]

oXL.Cells(1, colIndex).AutoFilter = True

The VB IDE would be more useful for you if you turned on Option Strict. AutoFilter is a method, not a property, and you should specify some parameter, see AutoFilter Method
It is not clear to me what you want to autofilter, but I'm guessing the last column only, that can be done using the Columns range and set 'field' parameter to 1:

CType(oSheet.Columns(colIndex), Excel.Range).AutoFilter(1)

 

[gene7135]

Thank you for replying.

I will turn on Option Strict as suggested.
Maybe I am using the wrong terms in reguards to the Autofilter.

I just want to turn on the column header filter option as shown in the pic below.
I am not looking to apply a filter, just turn on the option for the End User who will be using the Exported shpreadsheet.

 

[JohnH]

I'm not familiar with the first image you posted, but this code will enable filters for all columns for me:

oSheet.Cells.AutoFilter(1)

 

[gene7135]

That worked Perfectly! Thanks!