|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub 保存数据()
- Dim cnn As Object, rst As Object, Wb As Workbook
- Dim MyPath As String, i%
- MyPath = ThisWorkbook.Path & "\数据库.xls"
-
- Application.DisplayAlerts = False
- Set Wb = Workbooks.Open(MyPath)
- Range(Rows(2), Rows(99)).Delete '更新数据前先清空原数据
- ActiveWorkbook.Close Savechanges:=True
- Application.DisplayAlerts = True
-
- 'Set cnn = CreateObject("adodb.cnnection")'掉了“o”
- Set cnn = CreateObject("adodb.connection")
- Set rst = CreateObject("ADODB.recordset")
- If Application.Version * 1 < 12 Then
- cnn.Provider = "Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0"
- Else
- cnn.Provider = "Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0"
- End If
- cnn.Open MyPath
- rst.Open "select * from [Sheet1$]", cnn, 1, 3
- For i = 3 To Sheet1.Range("f65536").End(xlUp).Row
- rst.Addnew
- rst.Fields("姓名") = Range("f" & i).Value
- rst.Fields("年龄") = Range("g" & i).Value
- rst.Fields("性别") = Range("h" & i).Value
- rst.Update
- Next i
- rst.Close
- cnn.Close
- Set rst = Nothing
- Set cnn = Nothing
- MsgBox "已输入到数据库"
- End Sub
复制代码 |
|