|
本帖最后由 liangmutou01 于 2019-7-26 13:28 编辑
见代码:
Sub hz()
If IsEmpty([q1]) And IsEmpty([r1]) Or Not IsEmpty([q1]) And Not IsEmpty([r1]) Then
MsgBox "q1和r1单元格既不能同时为空,也不能同时有内容。q1为商品;r1为部门,更正后重试。"
Exit Sub
End If
Set conn = CreateObject("ADODB.Connection")
Range("a2:p65536").ClearContents
If IsEmpty([q1]) Then
bm = [r1]: bs = "b": [b2] = [r1]
Else
bm = [q1]: bs = "s": [a2] = [q1]
End If
lj = ThisWorkbook.Path & "\"
wjm = Dir(ThisWorkbook.Path & "\*.xls")
Do While Len(wjm) > 0
If ThisWorkbook.FullName <> lj & wjm Then
conn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0;HDR=yes';data source='" & lj & wjm & "'"
For lie = 4 To Cells(1, Columns.Count).End(xlToRight).Column
If Cells(1, lie) = Left(wjm, Len(wjm) - 4) Then Exit For
Next lie
If bs = "b" Then
Cells(2, lie).CopyFromRecordset conn.Execute("select sum(记帐销售金额) from [Sheet1$] where 部门='" & bm & "' group by 部门;")
Else
Cells(2, lie).CopyFromRecordset conn.Execute("select sum(记帐销售金额) from [Sheet1$] where 商标名称='" & bm & "' group by 商标名称;")
End If
End If
wjm = Dir
If conn.State = 1 Then conn.Close
Loop
Range("c2").Formula = "=sum(d2:p2)"
set conn=nothing
End Sub |
|