在 VB.net 中筛选 Excel 日期

Question

我有一行非常简单的代码可以在Excel VBA中工作，但我一生都无法弄清楚它在 VB.NET 中是如何工作的。任何帮助将不胜感激。

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

编辑：完整代码

 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 1

首先，您必须使用 DateSerial 将日期从 DateTimePicker1 转换为日期格式，并使用 ToOADate 将日期的 de 值转换为 OLE Automation

您还必须将条件"=>"的运算符更改为 ">=" ，= 符号必须在后面。

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