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.
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. 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
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.
Reply With Quote
Resolved", if the query is solved
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...