singo1232001

¦^´_ 1# willeddie


    Sub TEST()
Set cn = CreateObject("adodb.connection")
C = ".0; Data Source=" & ThisWorkbook.FullName
Select Case Application.Version
Case Is < 12: cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8" & C
Case Else: cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12" & C: End Select

q = "select ¤é´Á,²£«~,sum(¼Æ¶q) as ¼Æ¶q from [¤u§@ªí2$a1:C] "
q = q & "group by ¤é´Á,²£«~  having ¤é´Á is not null "

With Sheets("¤u§@ªí2"): .Range("F:H").ClearContents
Set rs = cn.Execute(q)
For i = 0 To rs.Fields.Count - 1 '¨ú¼ÐÃD¡A¦pªG¤£­n¥i¬Ù²¤
    .Cells(1, i + 6) = rs.Fields(i).Name
Next
.Cells(2, 6).CopyFromRecordset rs : End With
End Sub

singo1232001

¦^´_ 9# singo1232001

Sub TEST()
Select Case Application.Version
Case Is < 12: B = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8"
Case Else: B = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12": End Select
Set cn = CreateObject("adodb.connection")
cn.Open B & ".0; Data Source=" & ThisWorkbook.FullName
q = "select ¤é´Á,²£«~,sum(¼Æ¶q)from[¤u§@ªí2$a1:C]group by ¤é´Á,²£«~ having ¤é´Á is not null"
Set rs = cn.Execute(q)
[F:H].ClearContents: [F1:H1] = [A1:C1].Value
[F2].CopyFromRecordset rs
End Sub

singo1232001

¦^´_ 10# singo1232001

'«Üµw­nªºÁY¨ì·¥­­
Sub TEST()
Set CN = CreateObject("adodb.connection"): V = Application.Version: [F:H].ClearContents
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; "
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; "
CN.Open V & "Data Source=" & ThisWorkbook.FullName
q = "select ¤é´Á,²£«~,sum(¼Æ¶q)from[¤u§@ªí2$a1:C]group by ¤é´Á,²£«~ having ¤é´Á is not null"
Set RS = CN.Execute(q): [F1:H1] = [A1:C1].Value: [F2].CopyFromRecordset RS
End Sub