|
本帖最后由 awfitg 于 2018-1-24 14:37 编辑
求哪位老师帮忙解决一下这个问题:
本意是向外部excel表格中根据条件提取数据的,功能已经实现了,但是对于时间的筛选总是不对。附件里面有时间和字符串混用的信息,现在主要是SQL语句的时间条件总不能得到我想要的结果,向老师们讨教。注:附件中日期列有些为一整串字符串,大部分为mm/dd/yyyy hh:mm:ss格式
Public Sub ordeR()
Application.ScreenUpdating = False
Dim pathStr As String, strConn As String
Dim conn As Object, rst As Object, coun As Object, rst2 As Object
Dim strSql As Object, FirstRec, cat As Object
Set conn = CreateObject("ADODB.connection")
Set rst = CreateObject("scripting.dictionary")
Set strSql = CreateObject("scripting.dictionary")
Set cat = CreateObject("ADOX.Catalog")
pathstr2 = ThisWorkbook.Path & "\test.xlsm"
strConn = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathstr2 & ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1'"
conn.CursorLocation = 3
conn.Open strConn
cat.ActiveConnection = strConn
For i = 1 To 5
a = CStr(Replace(Replace(cat.tables(i).Name, "#", "."), "'", "")) '这个地方是另外一个excel表sheet的名称,是个IP地址转译
Sheets.Add before:=Sheets(1)
ActiveSheet.Name = Left(a, Len(a) - 1)
strSql(i) = "select F1,format(LEFT(F2,11),'yyyy/mm/dd')as 日期,F3,F4,F5,F6 from [" & a & "]"
strSql(i) = "select * from (" & strSql(i) & ") where 日期 >#11/09/2017#"
Set rst(i) = conn.Execute(strSql(i))
ActiveSheet.Cells.ClearContents
ActiveSheet.Range("A1").CopyFromRecordset rst(i)
ActiveSheet.Cells.EntireColumn.AutoFit
r = ActiveSheet.Range("A65536").End(xlUp).Row
ActiveSheet.Cells(r + 3, 1) = "共找到:" & rst(i).RecordCount - 1 & "条记录"
Next
rst.RemoveAll
strSql.RemoveAll
Set rst = Nothing
Set strSql = Nothing
conn.Close
Set conn = Nothing
Exit Sub
Application.ScreenUpdating = True
End Sub
|
|