把Excel当做数据库,用ActiveX Data Objects打开连接并使用Select语句来查询数据,效率要比Workbook.Open和Range().Value效率高的多。
下面来看以个例子:
Sub ExeSQL()
' 引用Microsoft ActiveX Data Objects 2.5
' 引用Microsoft Scripting Runtime
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim fs As New FileSystemObject
Dim extenName$, connStr$, sqlStr$
extenName = fs.GetExtensionName(ThisWorkbook.FullName) ' 文件扩展名
If extenName = "xls" Then '03
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;" & _
"Data Source=" & ThisWorkbook.FullName
ElseIf extenName = "xlsx" Then '07
connStr="Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;" & _
"Data Source=" & ThisWorkbook.FullName
End If
Set fs = Nothing
sqlStr = "select x.来源,x.访问次数,x.订单总数,y.成功交易量,y.销售额" & _
" from [订单表$] as x inner join [收入表$] as y" & _
" on x.来源=y.来源"
conn.Open connStr
Set rs = conn.Execute(sqlStr)
Sheets("新表").Range("A2").CopyFromRecordset rs
' Sheets("新表").Range("A2").CopyFromRecordset conn.Open(connStr)
Set rs = Nothing: conn.Close: Set conn = Nothing
End Sub
说明:
1、数据库的ConnectionString(连接字符串)的获取,我推荐一个网址:www.connectionstring.com/;
2、ConnectionString里的DataSource(数据源)是ThisWorkbook.FullName(本工作薄);
3、Excel作为数据源时,默认工作表的第一行为字段;
3、本工作薄里面有“订单表”、“收入表”和“新表”三个表,第一行(字段)分别为[来源],[访问次数],[...]等。