excel与spl server远程交互

来源:www.accessoft.com 点击数:6390 　 | 收藏 | 复制

时　间:2018-06-08 21:17:09
作　者:萤火虫 ID：66752 城市：大理
摘　要:excel与spl server远程交互/Excel VBA + ADO + SQL
正　文:

将excel工作表数据导入到spl serve,可以充分利用SQL Server数据库在数据查询等方面的优势，提高数据

管理与分析处理效率

1，[开始]→[程序]→[Microsoft SQL Server]→[导入和导出数据]命令，打开[DTS导入/导出向导]对话框。
2，单击[下一步]按钮，打开[选择数据源]对话框。
3，由于要将excel工作表数据导入到SQL Server数据库，因此在“数据源”下拉列表中选择“Microsoft Excel 97-2000”,然后在“文件名”文本框中输入excel文件,如D:\分析数据.xls,(也可以通过右边的按钮☐查找并选择excel文件。
4，单击[下一步]按钮，打开[选择目的]对话框，在“目的”下拉列表中选择“用于SQL Server的Microsoft OLE DB提供程序”，在“服务器”下拉列表中选择SQL Server服务器名称，在“数据库”下拉列表中选择要保存数据的数据库。
5，单击[下一步]按钮，打开[指定表复制或查询]对话框，这里选择“从源数据库复制表和视图”单选按钮。
6，单击[下一步]按钮，打开[选择源表和视图]对话框，选择复制数据的工作表。
7，单击[下一步]按钮，打开[保存，调度和复制包]对话框，这里选择“立即运行”复选框和“用复制方法发布目的数据”复选框。
8，单击[下一步]按钮，打开[正在完成DTS导入/导出向导]对话框
9，单击[完成]按钮，系统弹出[正在执行包]对话框，完毕后单击[完成]按钮。关闭是[正在执行包]对话框
10，[开始]→[程序]→[Microsoft SQL Server]→[企业管理器]命令，打开SQL Server企业管理器窗口，展开数据库和表目录，可以见到已经在指定的数据库创建了一个名为工作表名称的表

在excel工作表中制作一个按钮，绑定如下代码或读或写 SQL Server中表的数据
参见实例：

点击下载此示例

Public 多层嵌套查询()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String
'建立与指定SQL Server数据库的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "Password =180****;" _
& "Data Source=***.16.222.1;" _
& "Initial Catalog =学生管理"
cnn.Open
'查询数据
SQL = "select 姓名 from 学生 where 学号 in" _
& "(select 学号 from 成绩 where 成绩=" _
& "(select max(成绩) from 成绩))"
Set rs = cnn.Execute(SQL)
'复制查询出的数据
Cells.Clear
Range("A1") = "姓名"
Range("A1").Font.Bold = True
Range("A2").CopyFromRecordset rs
Columns.AutoFit
'关闭记录集以及与数据库的连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

Public Sub 查询表中所有记录的指定字段()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String, mydata As String, mytable As String
mydata = "学生管理" '指定要修改的数据库
mytable = "班主任" '指定数据表
'清除工作表的所有数据
Cells.Clear
'建立与指定SQL Server数据库的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "Password =180****;" _
& "Data Source=***.16.222.1;" _
& "Initial Catalog =" & mydata
cnn.Open
'查询全表某些字段的记录
SQL = "select 姓名,性别,职称 from " & mytable
Set rs = cnn.Execute(SQL)
'复制字段名
For i = 0 To rs.Fields.Count - 1
Cells(1, i + 1) = rs.Fields(i).Name
Cells(1, i + 1).Font.Bold = True
Next i
'复制全部记录数据
Range("A2").CopyFromRecordset rs
'字段设置列宽至合适的宽度
Columns.AutoFit
'关机记录集以及与数据库的连接
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

Public Sub 根据表达式对查询结果进行排序()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String, mydata As String, mytable As String
mydata = "销售管理" '指定数据库
mytable = "订单资料" '指定数据表
'清除工作表的所有数据
Cells.Clear
'建立与指定SQL Server数据库的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "Password =180****;" _
& "Data Source=***.16.222.1;" _
& "Initial Catalog =" & mydata
cnn.Open
'查询全表某些字段的记录
SQL = "select 数量*单价 as total,* from " & mytable _
& " order by 数量*单价 desc"
Set rs = cnn.Execute(SQL)
'复制字段名
For i = 0 To rs.Fields.Count - 1
Cells(1, i + 1) = rs.Fields(i).Name
Cells(1, i + 1).Font.Bold = True
Next i
'复制全部记录数据
Range("A2").CopyFromRecordset rs
'字段设置列宽至合适的宽度
Columns.AutoFit
'关机记录集以及与数据库的连接
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

Public Sub 将工作表的数据添加到数据库()
Dim cnn As New ADODB.Connection
Dim i As Integer
Dim myStr As String
'建立与指定SQL Server数据库的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "Password =180****;" _
& "Data Source=***.16.222.1;" _
& "Initial Catalog =学生管理"
cnn.Open
'添加新记录
If MsgBox("下面将把工作表的数据添加到数据表<成绩>！是否添加？", _
vbQuestion + vbYesNo) = vbYes Then
myColumn = "(学号, 课程代码, 成绩, 学期) "
For i = 2 To Range("A65536").End(xlUp).Row
myStr = " values("
myStr = myStr & "'" & Cells(i, 1) & "','" & Cells(i, 2) _
& "'," & Cells(i, 3) & ",'" & Cells(i, 4) & "')"
SQL = "insert into 成绩 " & myColumn & " " & myStr
cnn.Execute SQL
Next i
MsgBox "数据添加成功！", vbInformation
Else
MsgBox "添加数据操作被取消！", vbExclamation
End If
'关闭记录集以及与数据库的连接
cnn.Close
Set cnn = Nothing
End Sub

Public 一次修改多条记录()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'建立与指定SQL Server数据库的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "Password =180****;" _
& "Data Source=***.16.222.1;" _
& "Initial Catalog =学生管理"
cnn.Open
'添加新记录
If MsgBox("下面将修改数据表<成绩>的记录！是否修改？", _
vbQuestion + vbYesNo) = vbYes Then
SQL = "update 成绩 set 成绩 = 成绩 + 50 where 课程代码='200601003'"
cnn.Execute SQL
MsgBox "数据修改成功！", vbInformation
Else
MsgBox "修改数据操作被取消！", vbExclamation
End If
'关闭记录集以及与数据库的连接
cnn.Close
Set cnn = Nothing
End Sub

Public 删除数据表的全部记录()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'建立与指定SQL Server数据库的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "Password =180****;" _
& "Data Source=***.16.222.1;" _
& "Initial Catalog =学生管理"
cnn.Open
'删除记录
If MsgBox("下面将删除数据表<研究生>的所有记录！是否删除？", _
vbQuestion + vbYesNo + vbDefaultButton2) = vbYes Then
SQL = "delete from 学生"
cnn.Execute SQL
MsgBox "数据删除成功！", vbInformation
Else
MsgBox "删除数据操作被取消！", vbExclamation
End If
'关闭记录集以及与数据库的连接
cnn.Close
Set cnn = Nothing
End Sub

Public Sub 为某字段插入空值()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'建立与指定SQL Server数据库的连接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "Password =180****;" _
& "Data Source=***.16.222.1;" _
& "Initial Catalog =学生管理"
cnn.Open
'添加新记录
If MsgBox("下面将向数据表<成绩>添加新记录！是否添加？", _
vbQuestion + vbYesNo) = vbYes Then
SQL = "insert into 学生 values('A04200603','祝丽萍','女'," _
& "'2007-9-1','A01200602 ',null,'A03001')"
cnn.Execute SQL
MsgBox "数据添加成功！", vbInformation
Else
MsgBox "添加数据操作被取消！", vbExclamation
End If
'关闭记录集以及与数据库的连接
cnn.Close
Set cnn = Nothing
End Sub

Access软件网QQ交流群 (群号:54525238) 　　　　　　Access源码网店

【打印此文】【关闭窗口】