|
发表于 2011-9-28 11:55
|
显示全部楼层
本楼为最佳答案
回复 80013025 的帖子
- Sub Comm1_Click()
- Call Comm2_Click '先清空
- Dim intRow As Integer, t As Single
- Dim ARow As Integer, SQdate As String
- t = Timer
- Dim cn As New ADODB.Connection, sql As String
- '第一个问题,日期为空时 不加日期条件
- If Len([D1]) > 0 And Len([F1]) > 0 Then SQdate = " WHERE 销售日期 between # " & [D1] & " # AND #" & [F1] & "#"
- intRow = Sheet1.Range("C65536").End(xlUp).Row
- cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
- sql = "select 业务员,客户区域,客户简称,货品名称,产地,类型,单价,单位,sum(0) as 折合数,sum(金额),sum(小单位数量) from [源数据库$B2:Q" & intRow & "]" & _
- SQdate & " GROUP BY 客户简称,业务员,客户区域,货品名称,产地,类型,单价,单位"
- ' where 客户简称 like '%" & Range("E3").Value & "%'"
- Sheet3.Range("D5").CopyFromRecordset cn.Execute(sql) '导出数据
- cn.Close
- Set cn = Nothing
- ARow = Sheet3.Range("D65536").End(xlUp).Row
- ’第二个问题,无数据时,解决 【L4 】被填充公式问题
- If ARow > 4 Then Sheet3.Range("L5:L" & ARow).FormulaR1C1 = "=QtyWithUnit(RC[-5],RC[2])" '输入公式
- MsgBox "共用时:" & (Timer - t) * 1000 & "毫秒"
- End Sub
复制代码
|
|