xlsx filtered range import using interop help

[k7s41gx]

Hey All,


I am having some issues trying to get the autofilter/filtered range to work. I am trying to apply a filter and then used that filtered range to add rows to datatable RAW. I'm not sure how to accomplish this. Any help would be much appreciated. Code below.


Public Sub TESTER()
Dim xlApp2 As Excel.Application = Nothing
Dim xlWorkBooks2 As Excel.Workbooks = Nothing
Dim xlWorkBook2 As Excel.Workbook = Nothing
Dim xlWorkSheet2 As Excel.Worksheet = Nothing
Dim xlWorkSheets2 As Excel.Sheets = Nothing
Dim xlApp3 As Excel.Application = Nothing
Dim xlWorkBooks3 As Excel.Workbooks = Nothing
Dim xlWorkBook3 As Excel.Workbook = Nothing
Dim xlWorkSheet3 As Excel.Worksheet = Nothing
Dim xlWorkSheets3 As Excel.Sheets = Nothing
Dim fileName2 As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "2018RAW.xlsx")
Dim fileName3 As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "2019RAW.xlsx")
Dim counta As Integer
Dim countb As Integer
Dim RAW As New DataTable

TextBox2.Text = "2018RAW"
counta = 0
countb = 0
xlApp2 = New Excel.Application
xlApp2.Visible = False
xlApp2.DisplayAlerts = False
xlWorkBooks2 = xlApp2.Workbooks
xlWorkBook2 = xlWorkBooks2.Open(fileName2)
xlWorkSheets2 = xlWorkBook2.Sheets
xlWorkSheet2 = xlWorkBook2.Sheets("2018RAW")
counta = xlWorkSheet2.UsedRange.Rows.Count()
countb = xlWorkSheet2.UsedRange.Columns.Count()

ProgressBar1.Value = 0
ProgressBar1.Maximum = 33 + counta

Dim top_left = xlWorkSheet2.Cells(2, 1)
Dim bottom_right = xlWorkSheet2.Cells(counta, 33)
Dim range = xlWorkSheet2.Range(top_left, bottom_right)
range.AutoFilter(21, Form3.Label1.Text, Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, True)
Dim srange = range.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Excel.XlSpecialCellsValue.xlTextValues)
Dim values As Object(,) = CType(srange.Value2, Object(,))
counta = srange.Rows.Count()

Label2.Text = "Parsing Data, please wait......"
For i As Integer = 1 To 33
TextBox2.Text = "Adding Column " & xlWorkSheet2.Cells(1, i).value.ToString
RAW.Columns.Add(xlWorkSheet2.Cells(1, i).value.ToString)
'Form3.DataGridView1.Columns.Add(i, xlWorkSheet2.Cells(1, i).value.ToString)
ProgressBar1.Value = ProgressBar1.Value + 1
Next

Dim t(32) As Object

For row = 1 To counta
If CStr(xlWorkSheet2.Cells(row, 21).value) = Form3.Label1.Text Then
For col = 1 To 33
t(col - 1) = values(row, col)
Next
TextBox2.Text = "Row " & row
RAW.Rows.Add(t)
ProgressBar1.Value = ProgressBar1.Value + 1
Else
TextBox2.Text = "Row " & row
ProgressBar1.Value = ProgressBar1.Value + 1
End If
Next
Form3.DataGridView1.DataSource = RAW
TextBox2.Text = ""
Label2.Text = "Loading Complete!"
End Sub

Continue reading...