Excel AutoFilter Using VB.NET and VS 2008

This may need to be in the VB.NET forum, but thought I'd try here first. I have a VB.NET application that creates an excel workbook in the background based on data read and processed. Due to various data type values, I wanted to programmatically implement the AutoFilter method after creating the workbook. This way, the user can routinely select the filter for whichever column they wish to filter by and only look at the responses they care to see. Unfortunately I'm getting error after error and am not sure. I've looked all over the web and it is obvious that others have had the problem, but there seems to be very little on how to resolve it.

Any help would be greatly appreciated.

The best way is to record a macro in Excel 2007 and then amend that code in vb.net... anyways show us the code that you have tried :)

Sure... I have recorded a macro... doesn't work in this case. Again, I'm not sure why...

Recorded Macro:

Code:

---

    Range("A3:E31").Select
    Selection.AutoFilter
    Range("A4").Select

---

Selection represents a selected range object, so I've used...

Code:

---

wsExcel.Range(.Cells(rowStart + 2, colDrawing), .Cells(rowIndex - 1, colResponse)).AutoFilter()

---

Can I see the complete Sub?

I don't have a problem with that, but is a bit involved... I have other methods that handle creating the workbook and closing because there are several more of these type of methods... only without the autofilter obviously.:bigyello: I've had to cut the method down to size for this post because it is too long... But this should get the point... If you need to see the whole thing, I've attached the *.vb file as well...

Code:

---

Public Sub ProcessCompatibilityReport(ByVal OutputFilePath As String, ByVal FVCollection As FrontViewDiagramCollection, ByVal WDCollection As WiringDiagramCollection, _
                                            ByVal StationName As String, ByVal StationNumber As String, ByVal WorkOrder As String, ByVal UserID As String)

        'Initialize FileName
        Dim FileName As String = Nothing

        Try

            'Add New Workbook
            AddNewWorkbook()

            'Initialize Row/Column Constants
            Const rowStart As Integer = 1
            Const colDrawing As Integer = 1
            Const colObjectID As Integer = 2
            Const colObjectType As Integer = 3
            Const colResultType As Integer = 4
            Const colResponse As Integer = 5

            With wbExcel

                'Assign Worksheet array values
                wsExcel(0) = CType(.Worksheets("Sheet1"), Excel.Worksheet)
                wsExcel(1) = CType(.Worksheets("Sheet2"), Excel.Worksheet)
                wsExcel(2) = CType(.Worksheets("Sheet3"), Excel.Worksheet)

                'Change name of Sheet1
                wsExcel(0).Name = "DrawingCompatibilityReport"

                'Assign FileName Value
                FileName = OutputFilePath & "\" & RemoveInvalidPathNameCharacters(StationName.ToUpper) & "- Drawing Compatibility Report"

                'Delete Last two sheets
                wsExcel(1).Delete()
                wsExcel(2).Delete()


                With wsExcel(0)


                    'Initialize Header/Footer Information
                    'Removed to condense

                    'Format Front View Evaluation Headers
                    'Removed to condense

                    Dim rowIndex As Integer = rowStart + 3

                    'Initialize Progress
                    _Progress.Reset()
                    Dim intProgressCtr As Integer = 0
                    _Progress.Text = "Generating Compatibility Report: FV Data"

                    If FVCollection.Count > 0 Then

                        'Obtain, group, and sort Front-View Compatibility data for each drawing
                        For Each FV As FrontViewDiagram In FVCollection

                            Dim FVCompatibilityQuery = From fvc In FV.CompatibleResponseCollection Where fvc.Compatible = False _
                                                      Order By fvc.Result, fvc.Response, fvc.ObjectType, fvc.ObjectID


                            For Each FVCompatRecord In FVCompatibilityQuery
                                .Cells(rowIndex, colDrawing) = FV.DrawingName
                                .Cells(rowIndex, colObjectID) = FVCompatRecord.ObjectID
                                .Cells(rowIndex, colObjectType) = FVCompatRecord.ObjectType
                                .Cells(rowIndex, colResultType) = FVCompatRecord.Result.ToString
                                .Cells(rowIndex, colResponse) = FVCompatRecord.Response
                                rowIndex += 1
                            Next

                            'Update ProgressBar
                            intProgressCtr += 1
                            _Progress.Value = CInt(Math.Round(intProgressCtr * 100 / FVCollection.Count, 0))

                        Next

                        If FVCollection.Count > 0 Then
                            'Format Last Row
                            With .Range(.Cells(rowIndex - 1, colDrawing), .Cells(rowIndex - 1, colResponse))
                                With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                                    .LineStyle = Excel.XlLineStyle.xlContinuous
                                    .Weight = Excel.XlBorderWeight.xlMedium
                                    .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                                End With
                            End With

                            .Range(.Cells(rowStart + 2, colDrawing), .Cells(rowIndex - 1, colResponse)).AutoFilter()

                        End If

                    End If

                 
                    'Clear Progress
                    _Progress.Reset()

                    'Format Page
                    With .PageSetup
                        .Zoom = 75
                        .PaperSize = Excel.XlPaperSize.xlPaperLetter
                        .LeftMargin = 0.1
                        .RightMargin = 0.1
                        .Orientation = Excel.XlPageOrientation.xlLandscape
                    End With

                End With

                'Clear Excel Worksheets Resource
                wsExcel = Nothing

            End With

        Catch exExtract As System.Exception

            MsgBox("Error generating Drawing Compatibility Report.  Excel Processes may hang.  See Administrator" & vbCrLf & "Error: " & Err.Number & vbCrLf & exExtract.Message, _
                          CType(MsgBoxStyle.OkOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), Err.Source & ": Excel Output Error")

            'Close/Kill the current Excel reference without saving
            If wsExcel IsNot Nothing Then
                wsExcel = Nothing
            End If

            'Force the CloseWorkbook method to close without saving
            FileName = Nothing

        Finally

            'Close Workbook
            CloseWorkbook(FileName, Extension.xlsx)

        End Try

    End Sub

---

The basic syntax is (Just an Example)

Code:

---

Range1 = xlsSheet.Range("A3:E31")
Range1.Select()
xlsSheet.Application.Selection.AutoFilter()

---

Since I cannot open the app here in the office as I don't have access to vb.net so I will have a look at it in the evening when I go home....

I've tried that once... but the AutoFilter is not available for xlsSheet.Application.Selection... At least not in my version, which could indicate a reference being off.