VBA调用U870总账发生额及余额表存储过程

以下是一个VBA查询总账发生额及余额表的例子,掌握了它,我们就可以在EXCEL上直接得到各账套的发生额及余额表了,但是我对它并不太满意,我想要的效果是直接得到全年各月余额(损益类的为发生额)的表,这样我做集团合并报表时会方便很多,正在研究中,若能成功再发布上来:

Dim conn As ADODB.Connection

Dim rst As ADODB.Recordset

"建立公用的数据源连接字符串

Public Function GetConnStr()

GetConnStr = "driver={SQL Server};server=SERVER;uid=sa;pwd=123456"

End Function

Sub chaxun()

Range("a7:ba5000").ClearContents  ""先清空一下以前查的

Set conn = New ADODB.Connection

With conn

.ConnectionString = GetConnStr & ";database=UFDATA_" & ThisWorkbook.Sheets("chaxun").[ZT].Value & "_" & ThisWorkbook.Sheets("chaxun").[ND].Value "打开账套

.Open

End With

csqlstr = "exec GL_P_FSEYEB " & _

"N"" & ThisWorkbook.Sheets("chaxun").[star_mon].Value & ""," & _   (从几月)

"N"" & ThisWorkbook.Sheets("chaxun").[end_mon].Value & ""," & _   (到几月)

"0," & _

"NULL," & _

"N"我"," & _

"0," & _

"0, " & _

"1," & _

"NULL," & _

"NULL," & _

"NULL," & _

"NULL," & _

"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"," & _

"N"YEB12132"," & _

"NULL"

Set rst = New ADODB.Recordset

With rst

.ActiveConnection = conn

.Open csqlstr

End With

Range("a7").CopyFromRecordset rst

Range("a7").Select

rst.Close

conn.Close

End Sub

我对用友的存储过程并不太了解,曾在网上搜过很多次,可惜无人发布过相关的资料.只有自行研究了!我会把所掌握的存储过程全部公开,也希望与志同道合的朋友一起交流!

品略图书馆 http://www.pinlue.com/