singo1232001

¦^´_ 1# dou10801


    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsNumeric(Target.Value) = fasle Then Exit Sub
Set cn = CreateObject("adodb.connection")
Select Case Application.Version
Case Is < 12: cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; Data Source=" & ThisWorkbook.FullName
Case Else: cn.Open = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; Data Source=" & ThisWorkbook.FullName
End Select

Sql = "select count(*) as ct from [ºK­n$a1:B] where ¥N¸¹ = '" & Format(Target.Value, "000") & "'"
Set rs = cn.Execute(Sql)
Application.EnableEvents = False
If rs.getrows(, , "ct")(0, 0) = 1 Then  '°»¿ù®É ®e©ö¥¢®Ä
    Set rs = cn.Execute("select * from [ºK­n$a1:B] where ¥N¸¹ = '" & Format(Target.Value, "000") & "'")
    Cells(Target.Row, 2).CopyFromRecordset rs '.getrows '(,,array("¥N¸¹","©m¦W"))
Else
    Target.Value = ""
End If
Application.EnableEvents = True
cn.Close
Set cn = Nothing
End Sub