| |
▼さつき さん：
こんばんは。
あまり上手なコードではありませんが、
参考になれば幸いです。
Sub 集計()
Dim SQLCode As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim dbName As String
Dim Kiten As Range
Dim Cnt As Long
dbName = "C:週報.mdb"
Set Kiten = Range("A1")
SQLCode = "SELECT 累計.店名, 累計.契約者名, " _
& "Count(累計.品名) AS 品名のカウント " _
& "FROM 累計 " _
& "GROUP BY 累計.店名, 累計.契約者名;"
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & dbName
cnn.Open
Set rst = New Recordset
rst.Open SQLCode, cnn
Range("A1").CopyFromRecordset rst
rst.Close
For Cnt = Kiten.Row To Kiten.End(xlDown).Row
SQLCode = "SELECT 累計.品名 " _
& "FROM 累計 " _
& "WHERE 店名 = '" & Cells(Cnt, 1) _
& "' AND 契約者名 = '" & Cells(Cnt, 2) & "'"
rst.Open SQLCode, cnn
Do Until rst.EOF
Cells(Cnt, 4) = Cells(Cnt, 4) & " " & rst![品名]
rst.MoveNext
Loop
rst.Close
Next
Set rst = Nothing
cnn.Close
Set cnn = Nothing
End Sub
|
|
小僧
- 05/4/26(火) 19:42 -
