excel vba中如何一次性把sql查询的结果填到excel对应位置- 『 VBA交流 ...企业库|免费b2b网站

excel vba中如何一次性把sql查询的结果填到excel对应位置- 『 VBA交流 ...

Rank: Rank:

UID: 202891
帖子: 3
精华: 0
积分: 41
水晶: 25 枚
威望: 0 点
阅读权限: 10
注册时间: 2008-7-4

因为现在rs中的值有2列，一列是sDeptNum，一列是折旧
我的要求是：F6取rs中sDeptNum字段是601的折旧值，F7取rs中sDeptNum字段是602的折旧值
也就是需要删选rs后，放到指定位置，那位高手能帮我改改。
以下是目前的代码：
Private Sub 折旧取数_Click()
Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim sql As String
  Set sht = ThisWorkbook.Worksheets("表一") '把sht指向当前工作簿的sheet1工作表
  cnn.ConnectionString = "Provider=SQLOLEDB;" _
  & "User ID=sa;" _
  & "Password =xxxx;" _
  & "Data Source=xxxx;" _
  & "Initial Catalog =ufdata_002_2010"
  cnn.Open
sql = "select sDeptNum,SUM(dbldepr) as 折旧 from fa_total where iperiod=" + Trim(sht.Range("N5")) + " group by sDeptNum"
rs.Open sql, cnn, adOpenStatic, adLockBatchOptimistic
Set rs = cnn.Execute(sql)
sht.Range("F6") = rs("折旧") '将得到的值放到Excel的单元格中。

rs.Close
cnn.Close
  Set rs = Nothing
  Set cnn = Nothing
End Sub

Rank: Rank:

UID: 202891
帖子: 3
精华: 0
积分: 41
水晶: 25 枚
威望: 0 点
阅读权限: 10
注册时间: 2008-7-4

本帖{zh1}由 shingofish 于 2010-5-28 21:36 编辑

晕啊，没人回答吗。。自己顶下
哎~自己从sql上处理吧，竟然没人回复！现放出通过sql处理的结果，希望有达人能够帮忙解释下VBA的做法。只懂SQL，VBA小菜鸟的在此谢过了~

Private Sub 折旧取数_Click()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Set sht = ThisWorkbook.Worksheets("表一") '把sht指向当前工作簿的sheet1工作表
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "Password =XXXX;" _
& "Data Source=XXXX;" _
& "Initial Catalog =ufdata_002_2010"
cnn.Open
sql = "select (select SUM(dbldepr) from fa_total where sdeptnum='601' and iperiod=4 ) as 火腿腌制车间," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='602' and iperiod=4 ) as 火腿脱水车间," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='603' and iperiod=4 ) as 火腿发酵车间," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='604' and iperiod=4 ) as 火腿成熟车间," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='6051' and iperiod=4 ) as 包装车间火腿原材料组," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='651' and iperiod=4 ) as 低盐火腿腌酵车间," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='652' and iperiod=4 ) as 低盐火腿成熟车间," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='6530' and iperiod=4 ) as 低盐火腿包装车间," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='606' and iperiod=4 ) as 熟食车间," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='607' and iperiod=4 ) as 罐头车间," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='608' and iperiod=4 ) as 腌腊车间," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='609' and iperiod=4 ) as 调料车间," _
& "(select SUM(dbldepr) from fa_total where sdeptnum='654' and iperiod=4 ) as 萨拉米车间"
rs.Open sql, cnn, adOpenStatic, adLockBatchOptimistic
sht.Range("F6") = rs("火腿腌制车间") '将得到的值放到Excel的单元格中。
sht.Range("F7") = rs("火腿脱水车间")
sht.Range("F8") = rs("火腿发酵车间")
sht.Range("F9") = rs("火腿成熟车间")
sht.Range("F10") = rs("包装车间火腿原材料组")
sht.Range("F13") = rs("低盐火腿腌酵车间")
sht.Range("F14") = rs("低盐火腿成熟车间")
sht.Range("F15") = rs("低盐火腿包装车间")
sht.Range("F18") = rs("熟食车间")
sht.Range("F19") = rs("罐头车间")
sht.Range("F20") = rs("腌腊车间")
sht.Range("F21") = rs("调料车间")
sht.Range("F22") = rs("萨拉米车间")
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

复制代码

UID 7753 帖子 1236 精华 0 积分 4859 水晶 1611 枚威望 7 点阅读权限 80 性别男注册时间 2003-7-22

郑重声明：资讯【excel vba中如何一次性把sql查询的结果填到excel对应位置- 『 VBA交流 ...】由发布，版权归原作者及其所在单位，其原创性以及文中陈述文字和内容未经(企业库qiyeku.com)证实，请读者仅作参考，并请自行核实相关内容。若本文有侵犯到您的版权，请你提供相关证明及申请并与我们联系（qiyeku # qq.com）或【在线投诉】，我们审核后将会尽快处理。

—— 相关资讯 ——