最重要的四句代码
Dim cnn As New ADODB.Connection Dim rs As New ADODB.Recordset cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
strSQL = "select * from [Sheet1$]" ’ 别忘了在sheet 名前加$
1,创建连接:引用Microsoft ActiveX Data Objects 2.X Library; 这个是前期绑定。后期绑定的作法是:Set cnn = CreateObject("ADODB.Connection")。
2,创建recordset.
3,连接到Excel文件(当前工作簿)。
4,简单测试:
在sheet1里
id | user |
Sub test()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
strSQL = "select * from [Sheet1$]" ’ 别忘了在sheet 名前加$
rs.Open strSQL, cnn, adOpenKeyset, adLockPessimistic
Dim i As Integer
With rs
For i = 1 To 10
rs.AddNew
rs.Fields("id").Value = i
rs.Fields("user").Value = "wwsheng12" & i
rs.Update
Next
End With
rs.Close
Set rs = Nothing
End Sub