求帮忙改VBA简单代码 急用 谢谢！！！

fengfenghome

求大神帮忙改一下VBA代码希望通过  提取结果.xlsm 中的按钮在UserForm1中填写起始时间和截止时间 来选取对应时间内的数据。代码在窗体中
以上数据只是我自己为了方便操作编的数据，真正要处理的数据量很大，所以希望能够通过按钮来减少操作步骤实现筛选功能。
SQL语句
Private Sub CommandButton1_Click()
  Dim cnn As Object, rs As Object, SQL$
    [a1].CurrentRegion.Offset(1, 0).ClearContents
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & ThisWorkbook.Path & "\数据来源.xlsm"
    SQL = "Select 公司,日期,cstr(编号),种类,费用 from [Sheet1$a2:m] where 日期>=#TextBox1.Text# and 日期<=#TextBox2.Text# "
    Set rs = cnn.Execute(SQL)
    [a2].CopyFromRecordset rs
    cnn.Close
    Set cnn = Nothing
    Set rs = Nothing
End Sub



谢谢帮忙！！！

蓝桥玄霜

1. Private Sub CommandButton1_Click()
   
2.     Dim cnn As Object, rs As Object, SQL$
   
3.     On Error Resume Next
   
4.     [a1].CurrentRegion.Offset(1, 0).Clear 'Contents
   
5.     Cells.ClearOutline
   
6.     Set cnn = CreateObject("ADODB.Connection")
   
7.     cnn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & ThisWorkbook.Path & "\数据来源.xlsm"
   
8.     SQL = "Select 公司,日期,cstr(编号),种类,费用 from [Sheet1$a2:m] where 日期 between #" & TextBox1.Text & "# And #" & TextBox2.Text & "# "
   
9.     Set rs = cnn.Execute(SQL)
   
10.     [a2].CopyFromRecordset rs
    
11.     cnn.Close
    
12.     Set cnn = Nothing
    
13.     Set rs = Nothing
    
14.     [a1].CurrentRegion.Select
    
15.     Selection.Sort Key1:=[a2], Order1:=xlAscending, Key2:=[d2], Order1:=xlAscending, Header:=xlYes
    
16.     Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), _
    
17.         Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    
18. End Sub
    

复制代码

fengfenghome

蓝桥玄霜 发表于 2016-5-10 15:22

谢谢版主， 由于不会设置最佳答案 实在抱歉。 非常感谢！！！

LMY123

SQL = "Select 公司,日期,cstr(编号),种类,费用 from [Sheet1$a2:m] where 日期 between #" & TextBox1.Text & "# And #" & TextBox2.Text & "# "