利用vba sql 来操控 excel

最重要的四句代码

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