Private Sub commandbutton1_Click()
Dim i As Integer, sht As Worksheet 'i为整数变量;sht 为excel工作表对象变量,指向某一工作表
Dim cn As New adodb.Connection '定义数据链接对象 ,保存连接数据库信息;请先添加ADO引用
Dim rs As New adodb.Recordset '定义记录集对象,保存数据表
Dim strCn As String, strSQL As String '字符串变量Set sht = ThisWorkbook.Worksheets("sheet1")
strCn = "Provider=sqloledb;Server=ipsundb;Database=ufdata_100_2011" + ";Uid=sa;Pwd=abcdef;"
''strSQL = " select ccode,ccode_name,cexch_name,sbb,sbb1,smd,smc,sld,slc from GL_P_FSEYEB"cn.Open strCn '与数据库建立连接,如果成功,返回连接对象cn
Set g_Cmd = New Command
g_Cmd.ActiveConnection = strCn ' 连接到数据库
g_Cmd.CommandType = adCmdStoredProc ' 表示cmd的类型为存储过程
g_Cmd.CommandText = "GL_P_FSEYEB" ' 调用存储过程g_Cmd.Parameters(1) = "1"
g_Cmd.Parameters(2) = "5"
g_Cmd.Parameters(3) = 0
g_Cmd.Parameters(4) = Null
g_Cmd.Parameters(5) = "我"
g_Cmd.Parameters(6) = 1
g_Cmd.Parameters(7) = 3
g_Cmd.Parameters(8) = 0
g_Cmd.Parameters(9) = Null
g_Cmd.Parameters(10) = Null
g_Cmd.Parameters(11) = Null
g_Cmd.Parameters(12) = Null
g_Cmd.Parameters(13) = "case when cclass ='资产' then 1 else case when cclass ='负债' then 2 else case when cclass ='权益' then 3 else case when cclass ='成本' then 4 else 5 end end end end as lx"
g_Cmd.Parameters(14) = "YEB26753"
g_Cmd.Parameters(15) = NullSet g_Rs = g_Cmd.Execute ' 执行存储过程 把数据库查询结果返回给数据集rs.Open g_Cmd.Execute, cn '执行strSQL所含的SQL命令,结果保存在rs记录集对象中。此处运行时错误,提示:超时已过期。
i = 5Do While Not rs.EOF '当数据指针未移到记录集末尾时,循环下列操作
sht.Cells(i, 1) = rs(1)
sht.Cells(i, 2) = rs(2)
sht.Cells(i, 3) = rs(3)
sht.Cells(i, 4) = rs(4)
sht.Cells(i, 5) = rs(5)
sht.Cells(i, 6) = rs(6)
sht.Cells(i, 7) = rs(7)
sht.Cells(i, 8) = rs(8)
sht.Cells(i, 9) = rs(9)
rs.MoveNext '移向下一条记录
i = i + 1
Loop
rs.Close
cn.Close '关闭数据库链接,释放资源
MsgBox "查询完毕!"
End Sub
Dim i As Integer, sht As Worksheet 'i为整数变量;sht 为excel工作表对象变量,指向某一工作表
Dim cn As New adodb.Connection '定义数据链接对象 ,保存连接数据库信息;请先添加ADO引用
Dim rs As New adodb.Recordset '定义记录集对象,保存数据表
Dim strCn As String, strSQL As String '字符串变量Set sht = ThisWorkbook.Worksheets("sheet1")
strCn = "Provider=sqloledb;Server=ipsundb;Database=ufdata_100_2011" + ";Uid=sa;Pwd=abcdef;"
''strSQL = " select ccode,ccode_name,cexch_name,sbb,sbb1,smd,smc,sld,slc from GL_P_FSEYEB"cn.Open strCn '与数据库建立连接,如果成功,返回连接对象cn
Set g_Cmd = New Command
g_Cmd.ActiveConnection = strCn ' 连接到数据库
g_Cmd.CommandType = adCmdStoredProc ' 表示cmd的类型为存储过程
g_Cmd.CommandText = "GL_P_FSEYEB" ' 调用存储过程g_Cmd.Parameters(1) = "1"
g_Cmd.Parameters(2) = "5"
g_Cmd.Parameters(3) = 0
g_Cmd.Parameters(4) = Null
g_Cmd.Parameters(5) = "我"
g_Cmd.Parameters(6) = 1
g_Cmd.Parameters(7) = 3
g_Cmd.Parameters(8) = 0
g_Cmd.Parameters(9) = Null
g_Cmd.Parameters(10) = Null
g_Cmd.Parameters(11) = Null
g_Cmd.Parameters(12) = Null
g_Cmd.Parameters(13) = "case when cclass ='资产' then 1 else case when cclass ='负债' then 2 else case when cclass ='权益' then 3 else case when cclass ='成本' then 4 else 5 end end end end as lx"
g_Cmd.Parameters(14) = "YEB26753"
g_Cmd.Parameters(15) = NullSet g_Rs = g_Cmd.Execute ' 执行存储过程 把数据库查询结果返回给数据集rs.Open g_Cmd.Execute, cn '执行strSQL所含的SQL命令,结果保存在rs记录集对象中。此处运行时错误,提示:超时已过期。
i = 5Do While Not rs.EOF '当数据指针未移到记录集末尾时,循环下列操作
sht.Cells(i, 1) = rs(1)
sht.Cells(i, 2) = rs(2)
sht.Cells(i, 3) = rs(3)
sht.Cells(i, 4) = rs(4)
sht.Cells(i, 5) = rs(5)
sht.Cells(i, 6) = rs(6)
sht.Cells(i, 7) = rs(7)
sht.Cells(i, 8) = rs(8)
sht.Cells(i, 9) = rs(9)
rs.MoveNext '移向下一条记录
i = i + 1
Loop
rs.Close
cn.Close '关闭数据库链接,释放资源
MsgBox "查询完毕!"
End Sub
存储过程代码是:
DECLARE @RC int
DECLARE @iPer1 nvarchar(10)
DECLARE @iPer2 nvarchar(10)
DECLARE @ibk tinyint
DECLARE @c_code1 nvarchar(60)
DECLARE @c_code2 nvarchar(60)
DECLARE @n_jc1 tinyint
DECLARE @n_jc2 tinyint
DECLARE @isend tinyint
DECLARE @kmlx nvarchar(20)
DECLARE @exch nvarchar(20)
DECLARE @yefw1 nvarchar(20)
DECLARE @yefw2 nvarchar(20)
DECLARE @kmlxs nvarchar(500)
DECLARE @TRnd nvarchar(20)
DECLARE @skmZdyType nvarchar(100)
SELECT @iPer1 = N'1'
SELECT @iPer2 = N'5'
SELECT @ibk = 0
SELECT @c_code1 = NULL
SELECT @c_code2 = N'我'
SELECT @n_jc1 = 1
SELECT @n_jc2 = 3
SELECT @isend = 0
SELECT @kmlx = NULL
SELECT @exch = NULL
SELECT @yefw1 = NULL
SELECT @yefw2 = NULL
SELECT @kmlxs = N'case when cclass =N''资产'' then 1 else case when cclass =N''负债'' then 2 else case when cclass =N''权益'' then 3 else case when cclass =N''成本'' then 4 else 5 end end end end as lx'
SELECT @TRnd = N'YEB26753'
SELECT @skmZdyType = NULL
EXEC @RC = [UFDATA_100_2011].[dbo].[GL_P_FSEYEB] @iPer1, @iPer2, @ibk, @c_code1, @c_code2, @n_jc1, @n_jc2, @isend, @kmlx, @exch, @yefw1, @yefw2, @kmlxs, @TRnd, @skmZdyType
--@1整表导出
EXEC master..xp_cmdshell 'bcp 图书订购系统.dbo.出版社信息 out c:\2.doc -c -q -S(服务器名) -U(用户名) -P(对应密码)'
--@2查询导出
EXEC master..xp_cmdshell 'bcp "select * from AdventureWorks.Person.Address" queryout c:\adventure.txt -c -q -S(服务器名) -U(用户名) -P(对应密码)'
--eg:EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
--参数:S 是SQL服务器名;U是用户;P是密码
--可以导出doc,xls,txt等等--用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件(大小写区分)
-- [-m 最大错误数] [-f 格式化文件] [-e 错误文件]
-- [-F 首行] [-L 末行] [-b 批大小]
-- [-n 本机类型] [-c 字符类型] [-w 宽字符类型]
-- [-N 将非文本保持为本机类型] [-V 文件格式版本] [-q 带引号的标识符]
-- [-C 代码页说明符] [-t 字段终止符] [-r 行终止符]
-- [-i 输入文件] [-o 输出文件] [-a 数据包大小]
-- [-S 服务器名称] [-U 用户名] [-P 密码]
-- [-T 可信连接] [-v 版本] [-R 允许使用区域设置]
-- [-k 保留空值] [-E 保留标识值]
-- [-h"加载提示"] [-x 生成 xml 格式化文件]
--NULL