|
Private Sub CommandButton3_Click()
Dim sSql As String, sSql1 As String
Dim oCnn As Object, oRst As Object
Set oCnn = CreateObject("ADODB.connection")
Set oRst = CreateObject("ADODB.recordset")
oCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
If TextBox1 <> "" Then
sSql = " and 产品料号='" & TextBox1.Text & "'"
sSql1 = " and 产品料号='" & TextBox1.Text & "'"
End If
If TextBox2 <> "" Then
sSql = sSql & " and 日期=#" & TextBox2.Text & "#"
sSql1 = sSql1 & " and 日期=#" & TextBox2.Text & "#"
End If
If sSql <> "" Then
sSql = "select * from [入库单$a:k] where " & Mid(sSql, 6)
Else
sSql = "select * from [入库单$a:k]"
End If
oRst.Open sSql, oCnn, 1, 1
If oRst.RecordCount = 0 Then
MsgBox "没有符合条件的查询结果!", vbInformation
GoTo 100
End If
If sSql1 <> "" Then
If Dir(ThisWorkbook.Path & "\" & TextBox1.Text & TextBox2.Text & ".xls") <> "" Then Kill ThisWorkbook.Path & "\" & TextBox1.Text & TextBox2.Text & ".xls"
sSql1 = "select * into [" & ThisWorkbook.Path & "\" & TextBox1.Text & TextBox2.Text & ".xls].[Sheet2] from [入库单$a:k] where " & Mid(sSql1, 6)
oCnn.Execute (sSql1)
Workbooks.Open ThisWorkbook.Path & "\" & TextBox1.Text & TextBox2.Text & ".xls"
Else
If Dir(ThisWorkbook.Path & "\查询.xls") <> "" Then Kill ThisWorkbook.Path & "\查询.xls"
sSql1 = "select * into [" & ThisWorkbook.Path & "\查询.xls].[Sheet2] from [入库单$a:k]"
oCnn.Execute (sSql1)
Workbooks.Open ThisWorkbook.Path & "\查询.xls"
End If
100:
oRst.Close
oCnn.Close
请注解一下,谢谢!!1 |
|