Sub ChaXunData()
Application.ScreenUpdating = False
Dim Bvalue$, Dvalue$ '为文本格式了,但是修改为数值型后面的代码报错
With Sheet9
Bvalue = .Range("B1").Value
Dvalue = .Range("D1").Value
If Bvalue = "" And Dvalue = "" Then
Else
Dim iRow&
Dim cnn As New ADODB.Connection, rs As New ADODB.Recordset, SQL$
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
'注意下面的语句的取值范围,如有增加需要修改取值范围
If Bvalue <> "" And Dvalue <> "" Then
SQL = "Select ITEM, ITEM_DESC,WO_QTY,ACT追踪,WO_Number,排产日期 From [PLAN$A:Z] Where WO_Number='" & Bvalue & "' And 排产日期=#" & Dvalue & "#"
ElseIf Bvalue <> "" And Dvalue = "" Then
SQL = "Select ITEM,ITEM_DESC,WO_QTY,ACT追踪,WO_Number,排产日期 From [PLAN$A:Z] Where WO_Number='" & Bvalue & "'"
ElseIf Dvalue <> "" And Bvalue = "" Then
SQL = "Select ITEM,ITEM_DESC,WO_QTY,ACT追踪,WO_Number,排产日期 From [PLAN$A:Z] Where 排产日期=#" & Dvalue & "#"
End If
Set rs = cnn.Execute(SQL)
iRow = .Range("A" & .Rows.Count).End(3).Row
If iRow > 2 Then .Range("A3:E" & iRow).ClearContents
.Range("A3").CopyFromRecordset rs
cnn.Close
Set cnn = Nothing
Set rs = Nothing
End If
End With
Application.ScreenUpdating = True
End Sub
Sub modifiedData()
Application.ScreenUpdating = False
Dim iRow&, i&, arr()
With Sheet9
iRow = .Range("A" & .Rows.Count).End(3).Row
If iRow > 2 Then
arr = .Range("A3:F" & iRow).Value
Dim cnn As New ADODB.Connection, SQL$
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
For i = 1 To UBound(arr)
SQL = "Update [PLAN$A:Z] set [ITEM]='" & arr(i, 1) & "',[ITEM_DESC]='" & arr(i, 2) & "',[WO_Qty]='" & arr(i, 3) & "',[ACT追踪]='" & arr(i, 4) & "',[WO_Number]='" & arr(i, 5) & "',[排产日期]=#" & arr(i, 6) & "# where [WO_NUMBER]='" & arr(i, 5) & "'"
cnn.Execute SQL
Next i
MsgBox "数据修改完毕！", vbOKOnly, "提示"
End If
cnn.Close
Set cnn = Nothing
End With
Application.ScreenUpdating = True
End Sub