VBA SQL连接

创建法
      不需要引用ADO相关组件，直接使用CreateObject函数创建ADO对象，即：
      Set cnn = CreateObject("ADODB.connection")     '创建ado对象
      Set rst = CreateObject("ADODB.recordset")         '创建记录集

 

Private Sub CommandButton1_Click()

Dim cnn As Object, rs As Object
Dim strsql As String

Set cnn = CreateObject("ADODB.connection")     '创建ado象
Set rs = CreateObject("ADODB.recordset")         '创建记录集

cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ActiveWorkbook.FullName    'ActiveWorkbook.FullName  活动的文件名
strsql = "select * from [Sheet1$]"   'group by 姓名

'rs.Open strsql, cnn, adOpenKeyset, adLockReadOnly
Set rs = cnn.Execute(strsql)

Range("M1:AA65536").ClearContents
Range("M1").CopyFromRecordset rs

'Ran