[原创]Excel vba操纵数据库

hner · 发表于 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 编辑 ]

wuxiran · 发表于 2017-4-27 17:05:34

这个要好好学习，谢谢了！

上海的 · 发表于 2017-7-28 00:48:58

Excel vba操纵数据库

kenny122 · 发表于 2017-6-5 13:15:26

小手一抖，金币到手

hkahai · 发表于 2017-2-5 16:04:21

小手一抖，金币到手

ahhswiy · 发表于 2017-3-31 08:34:34

楼主非常专业的

hner · 发表于 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

hner · 发表于 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

hner · 发表于 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 编辑 ]

hner · 发表于 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进行校验，在实际开发编写自定义函数时，一定要对数据库有很深的了解，掌握数据库中某些关键数据表中各字段的实际含义，了解数据库中各表之间的关联，有了这些知识才能开发出准确地、有意义的函数。好了，就写到这。大家如果有更好的方法，不妨也在此分享一下。数据库方面的也可以。

hner · 发表于 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 编辑 ]

dafs · 发表于 2008-11-24 15:14:31

真是好东西，学习了

micker1984 · 发表于 2008-12-2 18:33:13

[原创]Excel vba操纵数据库
https://www.oyonyou.com/viewthread.php?tid=33120&fromuid=4270

iwtyqi_guh · 发表于 2008-12-3 16:11:49

fdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfds

ytycf151 · 发表于 2008-12-12 15:05:47

学习学习学习学习学习

zgdasb · 发表于 2009-1-12 20:16:19

非常不错,还有其它操作没???

anderlue · 发表于 2009-1-13 11:43:44

留名留名留名留名留名留名留名留名留名留名留名留名留名留名

anderlue · 发表于 2009-1-13 11:50:02

anderlue · 发表于 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)

anderlue · 发表于 2009-1-13 21:09:39

日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊日，我不会啊，就是下来看一下还不行啊

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

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

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

相关帖子

学习学习学习学习学习

账号		自动登录	找回密码
密码			注册账号