|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
VBA跨工作簿ADO 添加一行数据,用INSERT INTO 语句,没报错。但是程序运行结束,打开工作簿查看还是没添加成功。
存储的数据.rar
(55.86 KB, 下载次数: 3)
'测试的代码
Sub addDate1()
Set Cn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
PathStr = "C:\Users\Administrator\Desktop\存储的数据.xls"
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
Cn.Open strConn
sql = "INSERT INTO [Page1$] VALUES(8,B-200,12345)"
rs.Open sql, Cn, 3, 3
'第二种方法测试
rs.AddNew
rs("序号") = 8
rs("单号") = "B-200"
rs("数量") = 12345
rs.Update
rs.Close
Cn.Close
Set rs = Nothing
Set Cn = Nothing
End Sub
'测试的代码2
Sub AddRecord()
Dim conn As Object
Dim rs As Object
Dim file_path As String
Dim sheet_name As String
Dim sql As String
'设置Excel文件路径和工作表名称
file_path = "C:\Users\Administrator\Desktop\存储的数据.xls"
sheet_name = "Page1"
'创建ADO连接对象
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file_path & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
conn.Open
'创建ADO记录集对象
Set rs = CreateObject("ADODB.Recordset")
'设置SQL语句
sql = "SELECT * FROM [" & sheet_name & "$]"
'打开记录集
rs.Open sql, conn, 2, 2
'在记录集中添加一行记录
rs.AddNew
rs("序号") = 8
rs("单号") = "B-200"
rs("数量") = 12345
rs.Update
'关闭记录集和连接
rs.Close
conn.Close
MsgBox "添加成功!"
End Sub
|
|