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


        最近部门有一个海外客户,需要我们提供英文的结算单,由于国内的柜台系统只有提供中文版的结算单,所以只能通过柜台导出所需要的基础数据,然后转换为英文版的结算单。因为基础数据是csv结尾的文件,所以我就想能够通过把csv文件导入到ExcelAccess中(也可以导入到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文件的时候,提示不可识别的数据库格式,如下图所示:



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


扫一扫,分享到微信朋友圈