【VBA技巧】- 从Excel文件或Access数据库中获取指定列数据

        最近部门有一个海外客户，需要我们提供英文的结算单，由于国内的柜台系统只有提供中文版的结算单，所以只能通过柜台导出所需要的基础数据，然后转换为英文版的结算单。因为基础数据是csv结尾的文件，所以我就想能够通过把csv文件导入到Excel或Access中（也可以导入到SQL Server数据库中），然后再获取相应的列数据，对其进行中英文转换。

        具体转换细节就不做介绍了，今天主要为大家介绍如何从Excel文件或Access数据库中获取指定列数据。其实微软提供2种引擎可供在不打开Excel和Access的情况下获取数据。分别是Microsoft.Jet.OLEDB.4.0（以下简称 Jet 引擎）和Microsoft.ACE.OLEDB.12.0（以下简称 ACE 引擎）。

        在测试过程中，我发现以上两个引擎都可以用来访问Excel扩展名为*.xlsm文件。但Jet引擎只能用于访问扩展名为.mdb的Access文件，ACE引擎既可以访扩展名为.mdb的Access文件，也可以访问扩展名为.accdb的Access文件。

        为了更加直观的演示，我制作了一个虚拟数据，数据内容如下图所示：

        通过上图可以看出，Sheet1工作表中有5列11行数据，如果制作的报表中只需要其中的姓名、学号、身份证以及出生日期。那该如何操作呢？其中姓名、学号和身份证3列可以直接从上图中获取，出生日期列需要经过截取而来。

        从Excel中获取数据，可以通过以下代码实现，代码如下：

''连接Excel

Sub test1()

    Dim conn As New Connection

    Set rs = CreateObject(''adodb.recordset'')

    

    ''conn.ConnectionString = ''Provider=Microsoft.ACE.OLEDB.12.0;Data Source='' & ThisWorkbook.Path & ''\'' & ThisWorkbook.Name & '';Extended Properties=''Excel 12.0 Xml;HDR=YES''''

    conn.ConnectionString = ''provider=microsoft.jet.oledb.4.0; extended properties=''excel 8.0;hdr=yes;imex=2'';data source='' & ThisWorkbook.FullName

    conn.Open

    Sql = ''select 姓名, 学号, 身份证, mid(身份证,7,8) as 出生日期 from [Sheet1$]''

    

    ''填写新表的列名称

    rs.Open Sql, conn, 1, 3

    For i = 0 To rs.Fields.Count - 1

        Worksheets(''Sheet2'').Cells(1, i 1) = rs.Fields(i).Name

    Next

    ''把查询的结果集放入到A2单元格区域

    Worksheets(''Sheet2'').Range(''A1'').Offset(1, 0).CopyFromRecordset conn.Execute(Sql)

    

    rs.Close

    conn.Close

    Set rs = Nothing

    Set conn = Nothing

End Sub

        核心的代码就是使用Jet和ACE引擎，上段代码的作用是从当前打开的工作簿Sheet1工作表中获取姓名、学号、身份证以及出生日期信息。其中出生日期信息通过对身份证列进行mid截取，再把得到的列通过as关键词变更列名为出生日期。

        上面的代码放入模块后，按下F5或点击绿色图标会提示如下错误（如果正常运行请忽略，第一次使用会出现）：

        出现上图的错误是由于Excel VBE中未引用相应的Library，可以通过【工具】→【引用】，勾选类似【Microsoft ActiveX Data Objects *.*】并点击确定。我这里是Microsoft ActiveX Data Objects 6.1，根据各位小伙伴安装的Office版本的不同，版本号也会不同。

        前期准备完成后，我们一起来看看该段代码如何运作的，详见如下动态图：

        其实对于conn.ConnectionString也可以使用ACE引擎，把Jet引擎注释掉即可，可以获得同样的效果。

        为了充分演示Jet和ACE引擎，我特地建立了2个Access文件，除了文件格式不一样外，里面的数据都是一样的，如下图所示：

        为了演示Jet和ACE引擎的区别，如下动态图分别使用ACE和Jet引擎来连接.accdb和.mdb文件，效果如下：

        通过对比，可以发现使用ACE引擎可以连接.accdb和.mdb文件，但Jet引擎只能连接.mdb文件，当连接.accdb文件的时候，提示不可识别的数据库格式，如下图所示：

        通过上面的介绍，已经能够从导出的数据中有条件的筛选出所需要的列数据，然后需要做的就是对其进行中文翻译英文即可。