Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset
'OLE DB + ODBC Driver 方式:
'adoConnection.Open "Data Provider=MSDASQL.1;driver=Microsoft Excel Driver (*.xls);DBQ=e:\temp\book2.xls"
'Microsoft.Jet.OLEDB.4.0 方式,(建议)
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=e:\temp\book2.xls;Extended Properties='Excel 8.0;HDR=Yes'"
adoRecordset.Open "select * from [sheet1$]", adoConnection, adOpenKeyset, adLockOptimistic
Debug.Print adoRecordset.RecordCount
Dim i As Integer
Do Until adoRecordset.EOF
For i = 0 To adoRecordset.Fields.Count - 1
Debug.Print adoRecordset.Fields.Item(0).Name
Debug.Print adoRecordset.Fields.Item(0).Value
Next i
adoRecordset.MoveNext
Loop
'注: OLE DB + ODBC Driver 方式不支持以下语句,但 Microsoft.Jet.OLEDB.4.0 方式支持!
adoConnection.Execute "insert into [sheet1$](F1) values(3)"
adoRecordset.AddNew Array("f1", "f2", "f3", "f4"), Array(1, 2, 3, 4)
'用 In 语句将 Excel 表作为外部数据库打开,用 Set 语句免去关闭记录集
Set rs = cn.Execute("select * from [sheet1$] in ""c:\test\1.xls"" ""Excel 8.0;""")
'用 GetRows 方法直接将数据传递给数组(Variant 类型)
arr_sheet1 = rs.GetRows()
Set rs = cn.Execute("select * from [sheet2$] in ""c:\test\2.xls"" ""Excel 8.0;""")
arr_sheet2 = rs.GetRows()
以下從CSDN中摘錄
Private Sub Command1_Click()
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
Dim i%, j%, arrData As Variant, strTmp As String
cn.Open "Provider=SQLOLEDB.1;Password=;Persist Security Info=False;User ID=sa;Initial Catalog=Test;Data Source=ljx"
rs.Open "select * from t1", cn, 1, 1
arrData = rs.GetRows '把數值賦給二維陣列
For i = LBound(arrData, 2) To UBound(arrData, 2)
strTmp = ""
For j = LBound(arrData, 1) To UBound(arrData, 1)
strTmp = strTmp & vbTab & arrData(j, i)
Next
Debug.Print strTmp '列印陣列的資料
Next
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing