找回密码
 注册账号

QQ登录

只需一步,快速开始

手机号码,快捷登录

手机号码,快捷登录

初学者课程:T3自学|T6自学|U8自学软件下载课件下载工具下载资料:通资料|U8资料|NC|培训|年结积分规则 | 使用常见问题Q&A
知识库:U8 | | NC | U9 | OA | 政务U8|U9|NCC|NC65|NC65客开|NCC客开新手必读 | 任务 | 快速增金币用友QQ群[微信群]
查看: 19481|回复: 59

[数据库知识] [原创]Excel vba操纵数据库

  [复制链接]
发表于 2008-11-8 14:04:21 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?注册账号

×
今天开此贴皆在学习交流Vba操纵数据库,最终目的,利用Excel vba开发用友函数。有兴趣的朋友可以在此交流,请勿在本贴后回谢谢之类感谢的话,希望能分享一下这方面的经验,在学习本贴之前,建议安装sql server,确认服务器名称、密码等信息。

申明(下面的所有代码均在vbe中编写):
1、定义数据库连接对象cn
dim cn as adodb.connection
set cn=new adodb.connection
上面两句,可以用一句 set cn=createobject("adodb.connection"),这两种方法不同的是后者不需要添加ado引用,且在后面输入ado对象时其属性、方法不能显示出来,所以建议使用第一种方法。在使用第一种方法之前,需要添加ado引用。在vbe中,工具-引用-microsoft.activex.data.object.2.8
2、连接数据库字符串
CnStr = "provider=sqloledb;server=renh;user id=sa;pwd=;database=" & Db
其中:Provider为Sql Server数据库的提供者sqloledb,Server为数据库服务器名称或者IP地址(局域网操纵),database为数据库名,user id也可以写成uid为数据库用户名,pwd为数据库密码
3、打开数据库
cn.open cnstr
      上述几句,在连接数据库中经常用到。具体实例如下:
Sub 连接SqlServer数据库()
Dim Cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim SqlStr As String, CnStr As String, MyTable As String, Db As String
MyTable = "spt_provider_types"
Db = "master"
Set Cn = New ADODB.Connection
'连接本地数据库
CnStr = "provider=sqloledb;server=renh;user id=sa;pwd=;database=" & Db
'CnStr = "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.Path & "\" & Db
'Debug.Print CnStr
Cn.Open CnStr
If Cn.State = adStateOpen Then
  MsgBox "数据库连接成功!"
End If
End Sub

有了上述知识,就可也结合sql语言编写一些有用的程序了。
一、新建数据库
   使用Create Database 数据库名---新建一个数据库
Sub CreateDatabase()
'创建数据库
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim Db As String, MyTable As String, CnStr As String, SqlStr As String
Set Cn = New ADODB.Connection
MyTable = "spt_provider_types"
Db = "master"
CnStr = "provider=sqloledb;server=renh;user id=sa;pwd=;database=" & Db
Cn.Open CnStr
SqlStr = "create database Finance_2009 "
  Set Rs = Cn.Execute(SqlStr)
Cn.Close
Set Cn = Nothing

End Sub

[ 本帖最后由 hner 于 2008-11-8 14:46 编辑 ]

操纵Sql Server.rar

18.27 KB, 下载次数: 108, 下载积分: 金币 -5

金币

快速加金币

下载提示积分金币不足,非特定用户等

无法下载,可充值金币

发表于 2017-4-27 17:05:34 | 显示全部楼层
这个要好好学习,谢谢了!
回复 点赞 拍砖

使用道具 举报

发表于 2017-7-28 00:48:58 | 显示全部楼层
Excel vba操纵数据库
回复 点赞 拍砖

使用道具 举报

发表于 2017-6-5 13:15:26 | 显示全部楼层
小手一抖,金币到手
回复 点赞 拍砖

使用道具 举报

发表于 2017-2-5 16:04:21 | 显示全部楼层
小手一抖,金币到手
回复 点赞 拍砖

使用道具 举报

发表于 2017-3-31 08:34:34 | 显示全部楼层
楼主非常专业的
回复 点赞 拍砖

使用道具 举报

 楼主| 发表于 2008-11-8 14:14:51 | 显示全部楼层
二、在数据库中新建一个表
利用create table 数据表名(字段1 数据类型,字段2 数据类型,....),具体实例如下:
Sub CreateDatabase()
'创建数据库
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim Db As String, MyTable As String, CnStr As String, SqlStr As String
Set Cn = New ADODB.Connection
MyTable = "spt_provider_types"
Db = "master"
CnStr = "provider=sqloledb;server=renh;user id=sa;pwd=;database=" & Db
Cn.Open CnStr
SqlStr = "create database Finance_2009 "
  Set Rs = Cn.Execute(SqlStr)
Cn.Close
Set Cn = Nothing
set rs=nothing
End Sub
数据类型参照下面的地址
http://hi.baidu.com/shuwang/blog ... ed2866853524d5.html
 楼主| 发表于 2008-11-8 14:22:56 | 显示全部楼层
三、向数据库中插入数据
利用insert into 数据表名(字段1,字段2,....) values(值1,值2,......)

Sub 向数据库中添加数据()
'向sql server数据库中添加数据
'create table GL_Accvouch(period numeric(2,0),VouDate datetime,Content varchar(100),Code varchar(30),Debit numeric(10,2),credit numeric(10,2))
Dim Cn As ADODB.Connection
Dim Db As String, MyTable As String, CnStr As String, SqlStr As String
Set Cn = New ADODB.Connection
Db = "Finance_2009"
MyTable = "GL_Accvouch"
CnStr = "provider=sqloledb;server=renh;user id=sa;pwd=;database=" & Db
Cn.Open CnStr

For i = 1 To 10
    Code = "1001" & Application.WorksheetFunction.Text(i, "00")
    debit = Round(Rnd * 9000 + 1000, 2)
    credit = Round(Rnd * 8000 + 1000, 2)
    SqlStr = "insert into " & MyTable & " values(11,'2008-11-3','办公费'," & Code & "," & debit & "," & credit & ")"
    Cn.Execute SqlStr
Next i
Cn.Close
Set Cn = Nothing
End Sub
 楼主| 发表于 2008-11-8 14:25:18 | 显示全部楼层
四、导出数据
利用range的copyfromrecordset方法导出查询出来的记录
range("a1").copyfromrecordset rs
具体实例如下:

Sub 导出GL_Accvouch()
'导出Gl_Accvouch表
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim Db As String, MyTable As String, CnStr As String, SqlStr As String
Set Cn = New ADODB.Connection
Db = "Finance_2009"
MyTable = "GL_Accvouch"
[a1:iv65536].ClearContents

CnStr = "provider=sqloledb;server=renh;user id=sa;pwd=;database=" & Db
Cn.Open CnStr

If Cn.State = adStateOpen Then
  'MsgBox "数据库连接成功!"
  SqlStr = "select * from " & MyTable
  Set Rs = Cn.Execute(SqlStr)
  '将字段名称返回在工作表中,Rs.Fields.Count返回记录集字段的个数
  For i = 1 To Rs.Fields.Count
     'Fields集合的序号,即Fields(n) n是一个记录中字段从左到右排列的序号,第一个字段的序号为0,所以这里使用i-1
     Cells(1, i) = Rs.Fields(i - 1).Name
  Next i
  
  Range("a2").CopyFromRecordset Rs
End If
Cn.Close
Set Cn = Nothing

End Sub

[ 本帖最后由 hner 于 2008-11-8 14:38 编辑 ]
 楼主| 发表于 2008-11-8 14:44:13 | 显示全部楼层
五、编写自定义函数
有了上面的数据,我们可以编写一个自定义函数,计算某科目某会计期借方或者贷方的发生额,具体代码如下:
Function Fs(Period, Code, J)
'求某会计科目某会计期借方或者贷方发生额
Dim Cn As ADODB.Connection, rs As ADODB.Recordset
Dim Db As String, MyTable As String, CnStr As String, SqlStr As String
Set Cn = New ADODB.Connection
Db = "Finance_2009"
MyTable = "GL_Accvouch"
CnStr = "provider=sqloledb;server=renh;user id=sa;pwd=;database=" & Db
Cn.Open CnStr
   
If UCase(J) = "J" Then
   p = "Debit"
Else
   If UCase(J) = "D" Then
       p = "Credit"
   Else
      Exit Function
   End If
End If
SqlStr = "select sum( " & p & ") from " & MyTable & " where code=" & Code & " and period=" & Period
Set rs = Cn.Execute(SqlStr)
Fs = rs.Fields(0).Value
Cn.Close
Set Cn = Nothing
Set rs = Nothing
End Function


    大家可以根据上述代码做测试,将数据表导入excel,分别利用sumif函数与自定义函数fs进行校验,在实际开发编写自定义函数时,一定要对数据库有很深的了解,掌握数据库中某些关键数据表中各字段的实际含义,了解数据库中各表之间的关联,有了这些知识才能开发出准确地、有意义的函数。好了,就写到这。大家如果有更好的方法,不妨也在此分享一下。数据库方面的也可以。
 楼主| 发表于 2008-11-9 11:31:46 | 显示全部楼层
2008-11-9  今天主要学习将数据库、数据库中的数据表以及数据表中的所有记录返回在excel中。

Sub 返回SqlServer中所有数据库的名称()
Dim Cn As ADODB.Connection, rs As ADODB.Recordset
Dim Db As String, MyTable As String, CnStr As String, SqlStr As String
Dim Ws As Worksheet, Rng As Range
Set Cn = New ADODB.Connection
Db = "Master"
Set Ws = Sheet5
With Ws
   .[a2:a65536].ClearContents
   Set Rng = .Range("a1")
End With

CnStr = "provider=sqloledb;server=renh;user id=sa;pwd=;database=" & Db
Cn.Open CnStr

'创建数据库名称查询记录集

'查看Sql Server联机帮助sysdatabases
'最初安装 SQL Server 时,sysdatabases 包含 master、model、msdb、mssqlweb 和 tempdb 数据库的项。该表只存储在 master 数据库中。

SqlStr = "select name from sysdatabases "
Set rs = Cn.Execute(SqlStr)
i = 0
Do While Not rs.EOF
   i = i + 1
   Rng.Offset(i, 0) = rs.Fields("name")
   rs.MoveNext
Loop
MsgBox "Sql Server数据库中" & Db & "中共有:" & i - 1 & "个数据库!"
Cn.Close
Set Cn = Nothing
Set rs = Nothing

End Sub
Sub 返回数据库中所有数据表()
Dim Cn As ADODB.Connection, rs As ADODB.Recordset
Dim Db As String, MyTable As String, CnStr As String, SqlStr As String
Dim Ws As Worksheet, Rng As Range
Set Cn = New ADODB.Connection


Set Ws = Sheet5
With Ws
   Db = .Range("c1")
   MyTable = .Range("d2")
   .[b3:b65536].ClearContents
   Set Rng = .Range("b1")
End With
CnStr = "provider=sqloledb;server=renh;user id=sa;pwd=;database=" & Db
Cn.Open CnStr

'利用sql语句创建一个数据表记录集

SqlStr = "select name from sysobjects where xtype='u'"
'sysobjects表记录了所有对象,包括“表”,   “存储过程”,“触发器”,等等。
Set rs = Cn.Execute(SqlStr)
i = 1
Do While Not rs.EOF
   i = i + 1
   Rng.Offset(i, 0) = rs.Fields("name")
   rs.MoveNext
Loop
MsgBox "数据库" & Db & "中共有:" & i - 1 & "个用户表!"
Cn.Close
Set Cn = Nothing
Set rs = Nothing

End Sub

Sub 导出数据表中所有记录()
'导出数据表中所有记录

Dim Cn As ADODB.Connection, rs As ADODB.Recordset
Dim Db As String, MyTable As String, CnStr As String, SqlStr As String
Dim Ws As Worksheet, Rng As Range
Set Cn = New ADODB.Connection
Set Ws = Sheet5
With Ws
   Db = .Range("c1")
   MyTable = .Range("d2")
   .[c4:iv65536].ClearContents
   Set Rng = .Range("c4")
End With

CnStr = "provider=sqloledb;server=renh;user id=sa;pwd=;database=" & Db
Cn.Open CnStr

If Cn.State = adStateOpen Then
  'MsgBox "数据库连接成功!"
  SqlStr = "select * from " & MyTable
  Set rs = Cn.Execute(SqlStr)
  '将字段名称返回在工作表中,Rs.Fields.Count返回记录集字段的个数
  For i = 1 To rs.Fields.Count
     'Fields集合的序号,即Fields(n) n是一个记录中字段从左到右排列的序号,第一个字段的序号为0,所以这里使用i-1
     Rng.Offset(0, i - 1) = rs.Fields(i - 1).Name
  Next i
  
  Rng.Offset(1, 0).CopyFromRecordset rs
End If
Cn.Close
Set Cn = Nothing

End Sub

请查看附件,在安装sql server后,修改服务器名称和数据库名称,当然也可以连接公司的数据库,返回公司所有数据库名称以及对应的数据表和数据表记录。

[ 本帖最后由 hner 于 2008-11-9 11:34 编辑 ]

操纵Sql Server.rar

25.45 KB, 下载次数: 52, 下载积分: 金币 -5

金币

快速加金币

下载提示积分金币不足,非特定用户等

无法下载,可充值金币

发表于 2008-11-24 15:14:31 | 显示全部楼层
真是好东西,学习了
发表于 2008-12-2 18:33:13 | 显示全部楼层
发表于 2008-12-3 16:11:49 | 显示全部楼层
fdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfds
发表于 2008-12-12 15:05:47 | 显示全部楼层

学习学习学习学习学习

学习学习学习学习学习
发表于 2009-1-12 20:16:19 | 显示全部楼层
非常不错,还有其它操作没???
发表于 2009-1-13 11:43:44 | 显示全部楼层
留名留名留名留名留名留名留名留名留名留名留名留名留名留名
发表于 2009-1-13 11:50:02 | 显示全部楼层
发表于 2009-1-13 21:07:14 | 显示全部楼层
操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB) 操纵Sql Server.rar (18.27 KB)
发表于 2009-1-13 21:09:39 | 显示全部楼层
日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊日,我不会啊,就是下来看一下还不行啊
您需要登录后才可以回帖 登录 | 注册账号

本版积分规则

QQ|站长微信|Archiver|手机版|小黑屋|用友之家 ( 蜀ICP备07505338号|51072502110008 )

GMT+8, 2024-3-29 15:13 , Processed in 0.075501 second(s), 12 queries , Gzip On, Redis On.

Powered by Discuz! X3.5

© 2001-2024 Discuz! Team.

快速回复 返回顶部 返回列表