第三步，从K3的数据库中获取金额

我这里是使用循环，通过任务单号将金额汇总出来，如果使用数组的话，还要按任务单写GROUP，还要去对应，不如循环直接一点

'''''获取材料和外协金额的表格

Sub getje()

Dim rowcount As Long
Dim billno As String, sqlstr As String
Dim objRec
Dim objConn


MsgBox "计算金额时间较久，请耐心待"

Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

Sheets("材料&外协金额表").Select

Columns(3).Clear ' 清除数据
Columns(4).Clear ' 清除数据

Cells(1, 3) = "材料出库金额"
Cells(1, 4) = "外协加工金额"
rowcount = ActiveSheet.Range("a" & Rows.count).End(xlUp).Row


objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=192.168.100.3;Initial Catalog=AIS20150813141843;User ID=sa;Password=Chr_2016"
objConn.Open




For i = 2 To rowcount


billno = Range("B" & i)

''''''''''''获取材料出库金额
sqlstr = sqlstr + " select sum(FAmount) je from  ICStockBillEntry where "
sqlstr = sqlstr + " FICMOBillNo='" & billno & "'"
sqlstr = sqlstr + " group by FInterID,FICMOBillNo "

Set objRec = objConn.Execute(sqlstr)

Range("C" & i).CopyFromRecordset objRec
Set objRec = Nothing
sqlstr = ""


''''''''''''获取外协加工金额
sqlstr = sqlstr + " select sum(FDecimal) je  from t_BOS257800018Entry2 where  "
sqlstr = sqlstr + " FBillNo_SRC='" & billno & "'"
sqlstr = sqlstr + " group by FBillNo_SRC "

Set objRec = objConn.Execute(sqlstr)

Range("D" & i).CopyFromRecordset objRec
Set objRec = Nothing
sqlstr = ""


Next



     
'释放对象
'Set objRec = Nothing
Set objConn = Nothing

moformat.format
    Sheets("目录").Select
End Sub

结果如下