|
本帖最后由 xiangbaoan 于 2019-11-8 19:32 编辑
'附加
Option Explicit
Sub test1()
Dim Sh As Worksheet, Cel As Range, ar(1 To 1888, 1 To 2), br, i%, k%, n#
For Each Sh In Worksheets
If Sh.Name <> "汇总" Then
k = k + 1
n = 0
Set Cel = Sh.Rows(1).Find("营业收入", , , 1)
If Not Cel Is Nothing Then
br = Sh.[a1].CurrentRegion.Offset(, Cel.Column - 1).Resize(, 1)
For i = 2 To UBound(br)
n = n + Val(br(i, 1))
Next
ar(k, 1) = Sh.Name
ar(k, 2) = n
End If
End If
Next
With Sheets("汇总")
.[A:B].ClearContents
.[A2].Resize(k, 2) = ar
End With
End Sub
'Sub test2() '这WPS不中,OFFICE安装不完全也不行
' Dim Cn As Object, sh As Worksheet, ar(), i%
' Set Cn = CreateObject("ADODB.Connection")
' Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
' For Each sh In Worksheets
' If sh.Name <> "汇总" Then
' i = i + 1
' ReDim Preserve ar(1 To i)
' ar(i) = "SELECT '" & sh.Name & "',SUM(营业收入) FROM [" & sh.Name & "$" & sh.[a1].CurrentRegion.Address(0, 0) & "]"
' End If
' Next
' With Sheets("汇总")
' .[A:B].ClearContents
' .[A2].CopyFromRecordset Cn.Execute(Join(ar, " UNION ALL "))
' End With
' Cn.Close
' Set Cn = Nothing
'End Sub |
评分
-
4
查看全部评分
-
|