Excel VBA常见问题:如何通过VBA编程将符合条件的数据库记录输入到EXCEL中?

2015-04-15 16:57 阅读 242 次 评论关闭

Excel VBA常见问题如何通过VBA编程符合条件数据库记录输入EXCEL中?

现在有access格式的数据表 TEST

货号                   货名                    规格                  单价....

1-01                 货品1                   1M                  250.00

1-02                 货品2                   4Kg                 100.00

................

N-99               货品N                   999                 999.99

现在我想在EXCEL的单元格中输入货号,通过VBA代码自动从数据表中查找出相应的记录,并在相邻的列分别自动录入货品、规格、单价等内容,从而实现EXCEL自动数据录入。请问这VBA代码应如何写?谢谢!

解答:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rs As New ADODB.Recordset
Dim Query As String
Dim Cnn As String
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
Cnn = "Driver=Microsoft Access Driver (*.mdb);DBQ=C:\*.mdb"
Query = "SELECT * FROM TEXT WHERE 货号='" & Target & "'"
With Rs
    .Open Query, Cnn, adOpenStatic, adLockReadOnly
    If .RecordCount = 0 Then
    MsgBox "没有此货号!"
         Target.ClearContents
    Else
        Target.CopyFromRecordset Rs
    End If
   .Close
End With
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

版权声明:本文著作权归原作者所有,欢迎分享本文,谢谢支持!
转载请注明:Excel VBA常见问题:如何通过VBA编程将符合条件的数据库记录输入到EXCEL中? | 猎微网

评论已关闭!