Excel Ado类模块使用示例

86 阅读 0 评论 57 点赞

我是靠谱客的博主酷酷店员，这篇文章主要介绍Excel Ado类模块使用示例，现在分享给大家，希望可以做个参考。

同志们经常在VBA中使用Ado来查询、新增、删除、修改数据时，是不是经常的一遍又一遍的新敲代码，或许是到电脑里面去翻以前写过的代码再复制过来使用，是不是觉得有些麻烦。下面我将收集到的资料整理出来供大家使用，以后只需要给出数据库的路径和名称，要执行的SQL文本就可以。

本文起到一个示例作用，比如其他频繁在VBA中使用的语句也可以做成类模块来调用，在书写时快捷、代码简洁。下面是示例

第一步，在VBE窗口中引用 Microsoft ActiveX Data Objects

第二步，将常用的代码写到类模块里面。(1.代码界面简洁；2.以后可以直接调用。3.在其他地方使用可以把类模块导出来。)

（一）新建类模块，并命名。(类模块一般以字母c开始，我给命名为clsAdo)

（二）在类模块clsAdo中写入代码。

复制代码

Private cnn As ADODB.Connection
Private rs As ADODB.Recordset
Private x_Fn As String
Private x_SqlText As String
Property Get Fn() As String '取得文件路径和名称
    Fn = x_Fn
End Property
Property Let Fn(xFn As String) '赋值文件路径和名称
    x_Fn = xFn
End Property
Property Get SqlText() As String '取得SQL文本
    SqlText = x_SqlText
End Property
Property Let SqlText(xSqlText As String) '赋值SQL文本
    x_SqlText = xSqlText
End Property
Private Sub Class_Initialize()  '初始化Ado
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
End Sub
Private Sub Class_Terminate() '查询结束后销毁Ado
    If cnn.State = 1 Then
        cnn.Close
    End If
    Set cnn = Nothing
    Set rs = Nothing
End Sub
Public Function RunCnn() As Recordset '因为要返回值所以用到了Function函数
    If Len(x_SqlText) > 0 Then
        Call Conn
        If rs.State <> 0 Then rs.Close
            rs.Open SqlText, cnn, adOpenStatic, adLockReadOnly  '执行SQL语句
            Set RunCnn = rs   '返回值
    End If
End Function
Private Sub Conn() 'Ado连接，12.0哪里根据你的Excel版本
    If cnn.State = 0 And Len(x_Fn) > 0 Then
        With cnn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Extended Properties = Excel 12.0;" & "Data Source =" & Fn
            .Open
        End With
    End If
End Sub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Private cnn As ADODB.Connection
Private rs As ADODB.Recordset
Private x_Fn As String
Private x_SqlText As String
Property Get Fn() As String '取得文件路径和名称
    Fn = x_Fn
End Property
Property Let Fn(xFn As String) '赋值文件路径和名称
    x_Fn = xFn
End Property
Property Get SqlText() As String '取得SQL文本
    SqlText = x_SqlText
End Property
Property Let SqlText(xSqlText As String) '赋值SQL文本
    x_SqlText = xSqlText
End Property
Private Sub Class_Initialize()  '初始化Ado
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
End Sub
Private Sub Class_Terminate() '查询结束后销毁Ado
    If cnn.State = 1 Then
        cnn.Close
    End If
    Set cnn = Nothing
    Set rs = Nothing
End Sub
Public Function RunCnn() As Recordset '因为要返回值所以用到了Function函数
    If Len(x_SqlText) > 0 Then
        Call Conn
        If rs.State <> 0 Then rs.Close
            rs.Open SqlText, cnn, adOpenStatic, adLockReadOnly  '执行SQL语句
            Set RunCnn = rs   '返回值
    End If
End Function
Private Sub Conn() 'Ado连接，12.0哪里根据你的Excel版本
    If cnn.State = 0 And Len(x_Fn) > 0 Then
        With cnn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Extended Properties = Excel 12.0;" & "Data Source =" & Fn
            .Open
        End With
    End If
End Sub