[整理/2012-03-18: 学习笔记 ]

(一)

在VBA中使用ADO对象，必须先为工程引用ADO的对象库，可以单击VB编辑器主菜单【工具】|【引用】命令，打开【引用】对话框，在列表框中找到Microsoft ActiveX Data Objects 2.8 Library选项并选中。

使用ADO对象编程访问数据库，一般按以下步骤编写代码:

(1) 使用Connection对象连接到数据源

(2) 使用Recordset对象的打开记录集方法获得记录集对象(也可使用Command对象执行SQL语句获得记录集对象)

(3) 在程序中访问记录集中的数据(添加、删除、更新和查找等操作)

(4) 使用Connection对象中断连接

示例(以下代码，在”人事管理”数据库的表”Emp”中进行查询，将EmpID列为2008028人员的资料显示到工作表中):

Sub 员工资料()

 Dim cnn As New Connection, rst As Recordset, fld As Field

 Dim strSql As String, i As Long, strConn As String

 strSql = “SELECT * FROM Emp WHERE EmpID=’2008028’”

 strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=’”

 strConn = strConn & ActiveWorkbook.Path & ”\人事管理.mdb’”

 con.ConnectionString = strConn ‘设置连接字符串

 cnn.Open ‘打开连接

 Set rst = cnn.Execute(strSql) ‘执行SQL语句生成RecordSet

 With Worksheets(“sheet1”)

  i = 1

  For Each fld In rst.Fields ‘输出表头

   .Cells(1, i) = fld.Name ‘每列为一个字段名

   i = i + 1

  Next

  j = 1

  Do While Not rst.EOF ‘循环处理记录集中的记录

   i = 1

   j = j + 1

   For Each fld In rst.Fields ‘循处理各字段

    .Cells(j, i) = fld.Value ‘显示字段的值

    i = i + 1

   Next

   rst.MoveNext ‘下一记录

  Loop

 End With

 Set rst = Nothing

 Set cnn = Nothing

End Sub

(二)

因为Excel具有易用性、通用性和庞大的用户群，所以在一些小的应用程序中，可以将Excel作为后台数据库，用来保存用户的数据。以下演示用ADO方式访问Excel数据库的方法。

一 、查询工作表中的数据

使用ADO方式访问Excel工作簿，就是将Excel工作簿作为一个数据库，工作簿中每个工作表为一个数据表，工作表中的一列数据为一个字段。

用ADO访问Excel工作簿，需使用类似下面的连接字符串:

“Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=工作簿名称”

其中Extended Properties设置访问工作簿的版本号。例如，在当前工作簿的工作表”员工”中，保存着员工资料。在工作表Sheet2的单元格B1中输入姓名，单击右侧【查询】按钮，即可使用ADO方式从工作表”员工”中查找数据。在查找数据时，使用Like关键字进行模糊查询。具体代码如下:

Sub 按姓名查询()

    Dim cnn As New Connection, rs As New Recordset

    Dim strSql As String, str1 As String

    On Error Resume Next

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Extended Properties=Excel 8.0;" & "Data Source=" & ThisWorkbook.FullName

    str1 = Worksheets("sheet2").Range("B1")

    strSql = "select * from [员工$] Where 姓名 like '%" & str1 & "%'"

    'Debug.Print strSql

    rs.Open strSql, cnn, adOpenStatic

    With Worksheets("sheet2")

        .Range("A4:I100").ClearContents

        .Range("A4").CopyFromRecordset rs

    End With

    rs.Close

    cnn.Close

    Set rs = Nothing

    Set cnn = Nothing

End Sub

以上代码首先创建数据库连接，再根据用户输入的字符组成一个SQL语句，用该SQL语句打开记录集，最后使用CopyFromRecordset方法将记录集中的数据复制到工作表中。使用CopyFromRecordset方法可将Recordset对象中的内容复制到工作表，从指定区域的左上角开始。该方法的语法格式:

表达式.CopyFromRecordset(Data, MaxRows, MaxColumns)

Data——复制到区域的Recordset对象

MaxRows/MaxColumns——复制到工作表上的最大记录数(字段数)。如果省略参数将复制Recordset对象中的所有记录(搜索字段)

二、 导入其他工作簿数据

使用SQL的INSERT语句可向表中添加一个或多个记录。下面代码使用INSERT语句将另一个工作簿中的数据添加到当前工作表中。

Sub 导入数据()

    Dim cnn As New Connection

    Dim strSql As String, str1 As String

    On Error Resume Next

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Extended Properties=Excel 8.0;" & "Data Source=" & ThisWorkbook.FullName

    strSql = "INSERT INTO [员工$A:I] SELECT * FROM "

    strSql = strSql & " [Excel 8.0;Database="

    strSql = strSql & ThisWorkbook.Path & "\使用ADO.xls" & ";HDR=YES].[员工$A:I];"

    cnn.Execute strSql

   

    cnn.Close

    Set cnn = Nothing

    'Debug.Print strSql

End Sub

以上代码首先创建当前工作薄的ADO连接，接着创建SQL语句从原工作簿(此处为”使用ADO.xls”文件)的指定工作表(“员工”工作表)中选择数据，并插入到目标工作簿中。在Excel工作簿中，每个工作表相当于数据库中表，每一列为一个字段，使用符号$分隔表和列。使用下面的语句从工作簿”使用ADO.xls”的工作表”员工”中获取A~I列的数据:

SELECT * FROM [Excel 8.0;Database=使用ADO.xls;HDR=YES].[员工$A:I];

其中HDR=YES表示工作表有表头(第一行为字段名)

使用下面语句即可将指定工作表的数据添加到当前工作簿中:

INSERT INTO [员工$A:I] SELECT * FROM [Excel 8.0;Database=使用ADO.xls;HDR=YES].[员工$A:I];

注:使用ADO方式，可以在不打开工作簿的情况下获取工作簿中的数据

三、 导入其他工作簿数据

 

Sub ADO()

    ' 以Excel作为数据库-导入数据

    ' 使用ActiveSheet.ListObjects.Add链接Excel执行SQL

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _

    "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=E:\fly桌面\12何剑CQC.xls;", _

    "Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";", _

    "Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;", _

    "Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;", _

    "Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"), Destination:=Range("$C$1")).QueryTable

        .CommandType = xlCmdTable

        .CommandText = Array("select * from [VCLogCQCExport_2011_12_13_09_32$] where 服务器ID=""1""")

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .PreserveColumnInfo = True

        .SourceDataFile = "E:\flystar\5商业服务中心\7数据管理常规\99杂咋数据\12何剑CQC.xls"

        '.ListObject.DisplayName = "表_何剑CQC11"

        .Refresh BackgroundQuery:=False

    End With

End Sub

四、 简单提取其它工作薄的值

Sub Silent_open1()

  Dim myApp As New Application, wkSht As Worksheet

  '隐藏Excel

  myApp.Visible = False

  '打开数据文件，并指定工作表对象

  Set wkSht = myApp.Workbooks.Open(ThisWorkbook.Path & "\8-1.xls").Sheets(1)

  [A1] = wkSht.[A1]

  '关闭Excel

  myApp.Quit

  Set wkSht = Nothing

  Set myApp = Nothing

End Sub

Sub Silent_Open2()

    Dim myObj As Object

    ' GetObject 返回工作表对象的引用

    Set myObj = GetObject(ThisWorkbook.Path & "\8-1.xls")

    [A2] = myObj.Sheets(1).Cells(2, 1)

    '关闭工作簿

    myObj.Close

    Set myObj = Nothing

End Sub