[導(dǎo)讀]Sub?Query()
????Dim?Conn?As?Object,?Rst?As?Object
????Dim?strConn?As?String,?strSQL?As?String
????Di
Sub?Query()
????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???'設(shè)置工作簿的完整路徑和名稱
????Select?Case?Application.Version?*?1????'設(shè)置連接字符串,根據(jù)版本創(chuàng)建連接
????Case?Is?=?12
????????strConn?=?"Provider=Microsoft.ACE.OLEDB.12.0;Data?Source="?&?PathStr?&?";Extended?Properties=""Excel?12.0;HDR=YES"";"""
????End?Select
????'設(shè)置SQL查詢語句
????strSQL?=?"SELECT?DISTINCT?發(fā)站?FROM?[LMSData2016.12$]"
????Conn.Open?strConn????'打開數(shù)據(jù)庫鏈接
????Set?Rst?=?Conn.Execute(strSQL)????'執(zhí)行查詢，并將結(jié)果輸出到記錄集對象
????With?Sheet1
????????.Cells.Clear
????????For?i?=?0?To?Rst.Fields.Count?-?1????'填寫標(biāo)題
????????????.Cells(1,?i?+?1)?=?Rst.Fields(i).Name
????????Next?i
????????.Range("A2").CopyFromRecordset?Rst
????????.Cells.EntireColumn.AutoFit??'自動調(diào)整列寬
????End?With
????Rst.Close????'關(guān)閉數(shù)據(jù)庫連接
????Conn.Close
????Set?Conn?=?Nothing
????Set?Rst?=?Nothing
End?Sub
下載文檔
