利用recordset读取excel数据

先在在form中添加datagrid，详细参考我blog前面的文章

Dim adoRS As New ADODB.Recordset
Dim cn As New ADODB.Connection

Private Sub Form_Load()
Dim strConn As String
Dim strSQL As String
Dim strDataFile As String
Dim strTableName As String
strDataFile = "F:/test.xls"
With cn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:/test.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
.Open
End With
strSQL = "Select * from [Sheet1$]"
adoRS.CursorLocation = adUseClient
Set adoRS.ActiveConnection = cn
adoRS.Open strSQL, cn, 2, 3
adoRS.MoveLast

'添加数据
'adoRS.Open strSQL, cn, 1, 2
'adoRS.MoveLast
'adoRS.AddNew
'adoRS("姓名") = "李斯"
'adoRS("年龄") = 34
'adoRS("性别") = "男"
'adoRS.Update

'修改数据
'str = "Select * from [Sheet1$] where 姓名='王'"
'adoRS.Open str, cn, 2, 3
'adoRS("姓名") = "李斯"
'adoRS("年龄") = 34
'adoRS("性别") = "男"
'adoRS.Update
'adoRS.Close
'adoRS.Open strSQL, cn, 2, 3 '显示全部数据

'删除数据
'adoRS.Open "Select * from [Sheet1$]", cn, 2, 3
'adoRS.Delete
'adoRS.Close

Set DataGrid1.DataSource = adoRS.DataSource
DataGrid1.Refresh