最近有被问到:假设一个文件夹里有若干个Excel文件,每个文件都有同样的结构。如何在另外一个工作簿中批量获得这些文件的数据?
正好近期有弄好公众号的想法,那么就以这个问题开头,慢慢把自己工作学习的经历整理一下。
一共4篇会详细说明我是如何解决这个工作中非常常见的问题。我使用的方法称为:运用ADO批量访问Excel数据。
不要急着问ADO是什么,慢慢往下看你就明白了。
#创建一个ADO连接对象
Set ADO_cn = CreateObject('adodb.connection')
With ADO_cn
.Provider = 'Microsoft.ACE.OLEDB.12.0'
.ConnectionString = 'Extended Properties = Excel 12.0 macro; Data Source = ' & Filename
.Open
End With
#编写SQL语句查询数据
Sql = 'select * from [Sheet1$]'
Set rs = ADO_cn.Execute(Sql)
With Sheets('Sheet1')
For j = 0 To rs.Fields.Count - 1
.Cells(1, j 1) = rs.Fields(j).Name
Next j
.Range('A' & .Cells(1048576, 1).End(xlUp).Row 1).CopyFromRecordset rs
End With
ADO_cn.Close
Set ADO_cn = Nothing
一起来看这段代码,大致意思是创建一个ADO对象,通过微软提供的数据接口来访问某个文件。
我们知道Excel的行列特性类似于数据库结构,如果将Excel文件看成是一个数据库文件的话,就可以写SQL语句查询数据。
那么不管是10个文件还是100个文件,只要写个循环去遍历所在文件夹中的文件,甚至可以调用VBA的打开文件窗口方法选择文件,文件中需要的数据就都整理在一起了。
暂时听不太懂没关系,这个系列将会解答你的疑惑。
必须要说明的是,此方法下执行的SQL语句是Access规范的,和大型DBS中的SQL语句,在语法上有几处不同。感兴趣的话可以自行搜索,这边不做展开。
下一篇将会具体介绍ADO,Provider,ConnectionString等分别是什么。
联系客服