|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
沉默的流星 发表于 2013-4-10 19:36
老师:
您加入的 “and a.手机=b.手机”,在您的第二个模块《sub 插入数据不提示是否有重复》可以 ... - Sub 插入数据()
- Dim tt
- tt = Timer
- Dim cnn As Object, rs As Object, SQL$
- Set cnn = CreateObject("adodb.connection")
- Set rs = CreateObject("adodb.recordset")
- cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\通讯录.mdb"
- SQL = "select a.* from [Excel 8.0;Database=" & ThisWorkbook.FullName & "].[" & ActiveSheet.Name & "$" & [a1].CurrentRegion.Address(0, 0) _
- & "] a left join 通讯录 b on a.姓名=b.姓名 and a.手机=b.手机 where b.姓名 is null"
- rs.Open SQL, cnn, 1, 3
- If rs.RecordCount Then
- SQL = "insert into 通讯录 " & SQL
- cnn.Execute SQL
- MsgBox rs.RecordCount & "行数据已经添加到数据库!用时" & Timer - tt & "秒", vbInformation
- Else
- MsgBox "工作表的数据数据库中已经存在。", vbInformation
- End If
- rs.Close
- cnn.Close
- Set rs = Nothing
- Set cnn = Nothing
- End Sub
复制代码 |
|