|
发表于 2013-3-2 14:32
|
显示全部楼层
本楼为最佳答案
weixin321 发表于 2013-3-1 11:42
老师请帮忙看看怎么弄。 - Private Sub CommandButton1_Click()
- Range("M5:U65535").ClearContents
- Dim cn As New ADODB.Connection, sql As String
- Dim SQdate As String
- Application.ScreenUpdating = False
- t = Timer
- Dim SQ1$, SQ2$
- Dim MROW&, XROW&
- MROW = Sheets("sheet1").Range("B65536").End(xlUp).Row '取工作表有数据的最大行号,本例以E列
- XROW = Sheets("sheet2").Range("B65536").End(xlUp).Row
- If Len([N1]) > 0 And Len([R1]) > 0 Then SQdate = " WHERE 日期 between #" & [N1] & "# AND #" & [R1] & "# "
- cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
- SQ1 = "select 日期,货品编号,供应商,产地,品名,规格,进货数量 as 量,单位,进货总金额 as 金额,方式 from [sheet1$B4:K" & MROW & "]"
- SQ2 = "select 日期,货品编号,供应商,产地,品名,规格,进货数量 as 量,单位,进货总金额 as 金额,方式 from [sheet2$B4:K" & XROW & "]"
- sql = "select 货品编号,供应商,产地,品名,规格,sum(量),单位,sum(金额) FROM(" & SQ1 & " Union ALL " & SQ2 & _
- ") " & _
- SQdate & "and 方式 IN('采购进货入库','采购退货出库') GROUP BY 货品编号,供应商,产地,品名,规格,单位"
-
- Sheet1.Range("m5").CopyFromRecordset cn.Execute(sql)
- cn.Close
- Set cn = Nothing
- Application.ScreenUpdating = True
- MsgBox "查询完成"
- End Sub
复制代码 |
|