将魂断蓝桥的稍微改一下也可以,但我不知道为什么会多一空行,请内行指教。
- 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,期初数量)+入库数量-IIF(ISNULL(销售数量),0,销售数量) as 库存数量, " _
- & "IIF(ISNULL(期初金额),0,期初金额)+入库金额-IIF(ISNULL(销售金额),0,销售金额) as 库存金额 " _
- & "FROM (" & sql & ")"
- sql = "SELECT 商品名称,商品代码,期初数量,期初金额,销售单价,入库数量,入库金额,销售数量,销售金额,库存数量,库存金额/库存数量,库存金额 " _
- & "FROM (" & sql & ")"
- Sheet7.Activate
- [A2:L9999].Clear
- Range("A2").CopyFromRecordset cnn.Execute(sql)
- Set cnn = Nothing
- End Sub
复制代码
|