ExcelVBA-ADO-SQL-001连接数据库

ExcelVBA-ADO-SQL-001连接数据库

【知识点】

ADO中常用的连接字符串

2.Excel

Excel 2003 Excel 2007 Excel 2010 Excel 2013

Excel 12.0 Xml中的后缀XML、MACRO可以省略

Xlsx文件

Provider=Microsoft.ACE.OLEDB.12.0;DataSource=c:\myFolder\myExcel2007file.xlsx;

Extended Properties="Excel 12.0Xml;HDR=YES";

Provider=Microsoft.ACE.OLEDB.12.0;DataSource=c:\myFolder\myExcel2007file.xlsx;

Extended Properties="Excel 12.0Xml;HDR=YES;IMEX=1";

-------------------------------------------------------------------------------

Xlsb文件

Provider=Microsoft.ACE.OLEDB.12.0;DataSource=c:\myFolder\myBinaryExcel2007file.xlsb;

Extended Properties="Excel12.0;HDR=YES";

-------------------------------------------------------------------------------

Xlsm文件

Provider=Microsoft.ACE.OLEDB.12.0;DataSource=c:\myFolder\myExcel2007file.xlsm;

Extended Properties="Excel 12.0Macro;HDR=YES";

-------------------------------------------------------------------------------

Xls文件(Excel97-2003)

Provider=Microsoft.ACE.OLEDB.12.0;DataSource=c:\myFolder\myOldExcelFile.xls;

Extended Properties="Excel8.0;HDR=YES";

Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\MyExcel.xls;

Extended Properties="Excel8.0;HDR=Yes;IMEX=1";

参数说明

HDR=Yes：

这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。默认值YES

Excel 8.0：

对于Excel 97以上、2003及以下版本都用Excel 8.0，Excel 2007以上用Excel 12.0

IMEX(IMport EXport mode)：

IMEX是用来告诉驱动程序使用Excel文件的模式，其值有0、1、2三种，分别代表导出、导入、混合模式。当我

们设置IMEX＝1时将强制混合数据（数字、日期、字符串等）转换为文本。

但仅仅这种设置并不可靠，IMEX＝1只确保在某列前8行数据至少有一个是文本项的时候才起作用，它只是把查

找前8行数据中数据类型占优选择的行为作了略微的改变。例如某列前8行数据全为纯数字，那么它仍然以数字

类型作为该列的数据类型，随后行里的含有文本的数据仍然变空。另一个改进的措施是IMEX＝1与注册表值

TypeGuessRows配合使用，TypeGuessRows值决定了ISAM 驱动程序从前几条数据采样确定数据类型，默认为“8

”。

可以通过修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的该注册表值来更改采

样行数，设置为0时表示采样所有行。

　　IMEX 三种模式：

　　当 IMEX=0 时为“汇出模式”(Export mode)，该模式开启的Excel档案只能用来做“写入”用途。

　　当 IMEX=1 时为“汇入模式”(Import mode)，该模式开启的Excel档案只能用来做“读取”用途。

　　当 IMEX=2 时为“连結模式”(Linked mode)，该模式开启的Excel档案支持“读取”和“写入”用途。

选择数据区域：

"SELECT [列名一],[列名二] FROM [表一

HDR=NO，也就是工作表没有标题，用F1，F2...引用相应的数据列。

"SELECT * FROM [Sheet1$a5:d10]"，选择A5到D10的数据区域。

数据区域也可以用Excel中定义的名称表示，假如有个工作簿作用范围的数据区名称datarange,查询语句为：

"SELECT * FROM [datarange]"

如果数据区名称作用范围是工作表，需要加上工作表名："SELECT *FROM [sheet1$datarange]"

有密保的工作簿：

如果Excel工作簿受密码保护，即使通过提供正确的密码与连接字符串，也无法打开它来进行数据访问。如果您

尝试打开，将收到以下错误信息：“无法解密文件”。

Sub Test1()

Dim Conn As Object, Rst As Object

Dim strConn As String, strSQL As String

Dim i As Integer, PathStr As String

Set Conn =CreateObject("ADODB.Connection")

Set Rst =CreateObject("ADODB.Recordset")

PathStr = ThisWorkbook.FullName '设置工作簿的完整路径和名称

Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接

Case Is <= 11

strConn ="Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Datasource=" & PathStr

Case Is >= 12

strConn ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr &";Extended Properties=""Excel12.0;HDR=YES"";"""

End Select

'设置SQL查询语句

strSQL = "select * 部门 from [随机数据库$]"

Conn.Open strConn '打开数据库链接

Set Rst = Conn.Execute(strSQL) '执行查询，并将结果输出到记录集对象

With Sheets("Sheet1")

.Cells.Clear

For i = 0 To Rst.Fields.Count - 1 '填写标题

.Cells(1, i + 1) = Rst.Fields(i).Name

Next i

.Range("A2").CopyFromRecordset Rst

' .Cells.EntireColumn.AutoFit '自动调整列宽

' .Cells.EntireColumn.AutoFit '自动调整列宽

End With

Rst.Close '关闭数据库连接

Conn.Close

Set Conn = Nothing

Set Rst = Nothing

End Sub

再试一个“部门去重”

strSQL ="select DISTINCT 部门 from [随机数据库$]"

再试一个“年龄>59”

strSQL = "select * from [随机数据库$] where 年龄>59"