可以使用ado直接调用EXCEL的数据,而不需要使用ACCESS。
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic '打开Sheet1,也可以是定义的名称,或一个Range。
..
..
..
oRS.Close
oConn.Close
更具体的方法可以参考微软的Knowledge Base的文章:“Q278973 xcelADO Demonstrates How To Read/Write Data in Excel Workbooks”。
用这个方法调用EXCEL数据是非常快的。
能否用VB的ADO的方法访问Excel文件,如果能该怎样实现?
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)
ado方法
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)
我说一种dao的方法(也可用ado)
假如excel数据
male female children teens
11 cc 78 ee
22 xx 33 ff
45 uu 56 oo
用ISAM第一行略过
在excel中定义名称(插入-名称-定义)
名称范围 : myRange1
male female children teens
名称范围 : myRange2
11 cc 78 ee
22 xx 33 ff
45 uu 56 oo
private Sub Command_click1
im dbtmp As DAO.Database
im tblObj As DAO.TableDef
im rs As DAO.Recordset
et dbtmp = OpenDatabase_
("<complete path>\test.xls", False, True, "Excel 8.0;")
DoEvents
Set rs = dbtmp.OpenRecordset("select * from `myRange2`")
While Not rs.EOF
For x = 0 To rs.Fields.Count - 1
Debug.Print rs.Fields(x).Value
Next
rs.MoveNext
Wend
End Sub