|
库存单价,库存金额没做,没看懂如何计算的。
Sub a()
Dim cnn, myf$, sql$, SQA, SQB, SQC
myf = ThisWorkbook.FullName
Set cnn = CreateObject("adodb.connection")
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & myf
SQA = "SELECT 商品名称,商品代码 FROM [期初$A1:B] UNION SELECT 商品名称,商品代码 FROM [入库$A1:B] WHERE 商品名称 is not null"
sql = "select A.*,B.期初数量,B.期初金额,B.销售单价,C.入库数量,C.入库金额,D.销售数量,D.销售金额 from (((" & SQA & ") AS A LEFT JOIN [期初$A1:E] AS B ON A.商品名称=B.商品名称 AND A.商品代码=B.商品代码)"
sql = sql & " LEFT JOIN [入库$A1:E] AS C ON A.商品名称=C.商品名称 AND A.商品代码=C.商品代码) " _
& " LEFT JOIN [出库$A1:D] AS D ON A.商品名称=D.商品名称 AND A.商品代码=D.商品代码"
sql = "SELECT 商品名称,商品代码,期初数量,期初金额,销售单价,入库数量,入库金额,销售数量,销售金额, IIF(ISNULL(期初数量),0,期初数量)+入库数量-销售数量 FROM (" & sql & ")"
[A2:L9999] = ""
Range("A2").CopyFromRecordset cnn.Execute(sql)
Set cnn = Nothing
End Sub |
评分
-
1
查看全部评分
-
|