Istha Istha - 7 months ago 40
Vb.net Question

Filtering excel dates in VB.net

I have a very simple line of code that works in Excel VBA, but I can't for the life of me figure out how it works in VB.NET. Any help would be appreciated.

Selection.AutoFilter Field:=8, Criteria1:=">=" & sdate


edit: complete code

Dim sDate as Date
Dim xlapp As New Excel.Application
sdate = DateTimePicker1.Value.Date
OpenFileDialog1.Filter = "Excel Documents | *.xls; *.xlsx; *.xlsxm"
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
xlWB= xlapp.Workbooks.Open(OpenFileDialog1.FileName)

xlWB.Worksheets("Data").Range("H1").Select()
xlapp.Selection.AutoFilter()
xlapp.Selection.AutoFilter(Field:=8, Criteria1:="=>" & sDate)

Answer

first, you must convert date from DateTimePicker1 to Date format with DateSerial, and convert de value of date to OLE Automation with ToOADate

you must also change operator of criteria "=>" to ">=", the = sign must be after.

Dim sDate As Date
Dim xlapp As New Excel.Application
Dim xlWB As Excel.Workbook
Dim dpDate As Date = DateTimePicker1.Value.Date
sDate = DateSerial(dpDate.Year, dpDate.Month, dpDate.Day)
OpenFileDialog1.Filter = "Excel Documents | *.xls; *.xlsx; *.xlsxm"
If OpenFileDialog1.ShowDialog = DialogResult.OK Then
    xlWB = xlapp.Workbooks.Open(OpenFileDialog1.FileName)

    xlWB.Worksheets("Data").Range("H1").Select()
    xlapp.Selection.AutoFilter()
    xlapp.Selection.AutoFilter(Field:=8, Criteria1:=">=" & sDate.ToOADate)
End If