从零开始学习SQL(图文教程)

335081548 · 发表于 2014-1-31 10:05

用ADO+SQL在Excel文件中，插入新的数据

Sub 在Excel文件中插入新的数据()
Dim conn As Object
Dim Sql As String, PathStr As String
PathStr = ThisWorkbook.FullName '设置工作簿的完整路径和名称
Set conn = CreateObject("ADODB.Connection")
Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接
Case Is <= 11
conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
conn.Open
Sql = "INSERT INTO [Sheet1$] (编号,商品名称, 单位,数量,单价,金额) VALUES (21,'洗衣机','台',100,2500,250000)"
conn.Execute Sql
MsgBox "插入数据成功", , "雪山飞狐"
conn.Close
Set conn = Nothing
End Sub

复制代码

INSERT INTO [Sheet1$] (编号,商品名称, 单位,数量,单价,金额) VALUES (21,'洗衣机','台',100,2500,250000)

INSERT INTO [工作表名$] (字段1,字段2 , 字段3,字段4,字段5 ,字段6,...) VALUES (字段1的值,字段2的值 ,字段3的值, 字段4的值,字段5的值 ,字段6的值 ,...)
注意的是VALUES ( 字段1的值, 字段2的值 , 字段3的值, 字段4的值, 字段5的值 , 字段6的值 ,...)
文本必须加上双引号或单引号，数值不用，日期加上"#"

335081548 · 发表于 2014-1-31 10:10

用ADO+SQL更新Excel工作中内容。（相当于选择性粘贴（乘）除的用法）
下面举例进行说明

Sub 在Excel中更新文件中的数据()
Dim conn As Object
Dim Sql As String, PathStr As String
PathStr = ThisWorkbook.FullName '设置工作簿的完整路径和名称
Set conn = CreateObject("ADODB.Connection")
Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接
Case Is <= 11
conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
conn.Open
Sql = "UPDATE [Sheet1$] SET 数量 = 数量 /100 WHERE [单位] = '个'"
conn.Execute Sql
MsgBox "数据更新成功", , "雪山飞狐"
conn.Close
Set conn = Nothing
End Sub

复制代码

UPDATE [Sheet1$] SET 数量 = 数量 /100 WHERE [单位] = '个'

UPDATE [工作表$] SET 要更新的字段 = 表达式 WHERE [条件字段] = 条件表达式
具体下载附件操作一下。

335081548 · 发表于 2014-1-31 12:39

本帖最后由 335081548 于 2014-1-31 12:46 编辑

用ADO_SQL在Excel中批量插入数据

Sub 使用ADO_SQL批量插入新记录()
Dim Conn As Object
Dim SQL As String
'设置连接字符串
Set Conn = CreateObject("ADODB.Connection")
Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接
Case Is <= 11
Conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & ThisWorkbook.FullName
Case Is >= 12
Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
Conn.Open
'将表格中的记录插入Sheet2表中
SQL = "INSERT INTO [Sheet2$] SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].[Sheet1$]"
'执行SQL语句
Conn.Execute SQL
MsgBox "插入数据成功", , "雪山飞狐"
Conn.Close
Set Conn = Nothing
End Sub

复制代码

INSERT INTO [Sheet2$] SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].[Sheet1$]

INSERT INTO [存储数据工作表名$] 查询语句

查询语句=SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & 工作簿路径+工作簿名及后辍名 & "].[查询的工作名$]

INSERT INTO [存储数据工作表名$] SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & 工作簿路径+工作簿名及后辍名 & "].[查询的工作名$]

本示例是把工作表1的数据插入到工作表2，用查询语句来实现批量插入。（也可以用查询语句，实现有条件批量插入）

335081548 · 发表于 2014-1-31 12:45

在Excel中使用以下语句也可以成功。
就是楼上的红色字体没有添加

Sub 使用ADO_SQL批量插入新记录2()
Dim Conn As Object
Dim SQL As String
'设置连接字符串
Set Conn = CreateObject("ADODB.Connection")
Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接
Case Is <= 11
Conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & ThisWorkbook.FullName
Case Is >= 12
Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
Conn.Open
'将表格中的记录插入Sheet2表中
SQL = "INSERT INTO [Sheet2$] SELECT * FROM [" & ThisWorkbook.FullName & "].[Sheet1$]"
'执行SQL语句
Conn.Execute SQL
MsgBox "插入数据成功", , "雪山飞狐"
Conn.Close
Set Conn = Nothing
End Sub

复制代码

335081548 · 发表于 2014-2-2 15:35

Sub 用ADO打开带密码的Excel()
Dim Wb As Workbook
Dim Conn As Object, Rst As Object
Dim strConn As String, strSQL As String
Dim i As Integer, PathStr As String
Set Conn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
PathStr = ThisWorkbook.Path & "\有密码的Excel工作簿.xls" '设置工作簿的完整路径和名称
Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接
Case Is <= 11
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
Set Wb = Workbooks.Open(Filename:=PathStr, Password:="1234")
Wb.Windows(1).Visible = False
Conn.Open strConn '打开数据库链接
strSQL = "select * from [Sheet1$]" '设置SQL查询语句
Set Rst = Conn.Execute(strSQL) '执行查询，并将结果输出到记录集对象
With Sheet3
.Cells.Clear
For i = 0 To Rst.Fields.Count - 1 '填写标题
.Cells(1, i + 1) = Rst.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset Rst
.Cells.EntireColumn.AutoFit '自动调整列宽
End With
Rst.Close: Conn.Close '关闭数据库连接
Set Conn = Nothing: Set Rst = Nothing
Wb.Close False
End Sub

复制代码

雪狼（初级） · 发表于 2014-2-3 13:57

十分感谢，好资料

hacsky · 发表于 2014-2-4 11:51

非常感谢分享，学习了。。

kimkuemok · 发表于 2014-2-22 12:44

学习了，太好了。

h3533 · 发表于 2014-2-22 21:43

第一次接触SQL，感谢老师！学到了很多。

紅顏灬惹亼醉。 · 发表于 2014-2-24 18:07

很好！

		自动登录	找回密码
密码			免费注册

[原创] 从零开始学习SQL(图文教程)