最近部门有一个海外客户,需要我们提供英文的结算单,由于国内的柜台系统只有提供中文版的结算单,所以只能通过柜台导出所需要的基础数据,然后转换为英文版的结算单。因为基础数据是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文件的时候,提示不可识别的数据库格式,如下图所示:
通过上面的介绍,已经能够从导出的数据中有条件的筛选出所需要的列数据,然后需要做的就是对其进行中文翻译英文即可。
联系客服